Financial functions: sln, syd, vdb, xirr, xnpv

Excel Function SLN

The SLN() function also deals with the calculation of depreciation of an asset.

The thing is, this function uses the Straight Line Depreciation' method.

In this method, a fixed amount is deduced from the asset value at each period of calculation.

This is the simplest method among all the depreciation calculation methods.

The Syntax is:

SLN(cost of asset,salvage,life of depreciation)

Cost Salvage life SLN() Formula
10000 750 4 2312.5 =SLN(L4,M4,N4)
24000 1000 5 4600 =SLN(L5,M5,N5)
12000 900 7 1585.71428571429 =SLN(L6,M6,N6)
100000 1000 6 16500 =SLN(L7,M7,N7)
200000 3000 3 65666.6666666667 =SLN(L8,M8,N8)

 

 

Excel Function SYD

The SYD() function also deals with the calculation of depreciation of an asset.

The thing is, this function uses the 'Sum of years' digits depreciation method.

The Syntax is:

SYD(cost of asset,salvage,life of depreciation,period)

Cost Salvage life Period(years) SYD() Formula
10000 750 4 1 3700 =SYD(L19,M19,N19,O19)
24000 1000 5 2 6133.33333333333 =SYD(L20,M20,N20,O20)
12000 900 7 3 1982.14285714286 =SYD(L21,M21,N21,O21)
100000 1000 6 4 14142.8571428571 =SYD(L22,M22,N22,O22)
200000 3000 3 2 65666.6666666667 =SYD(L23,M23,N23,O23)

 

 

Excel Function VDB

The VDB() function also deals with the calculation of depreciation of an asset.

The thing is, this function uses the 'Declining Balance' depreciation method.

The Syntax is:

VDB(cost of asset,salvage,life of depreciation,start period,end period)

Cost Salvage life Start End VDB() Formula
10000 750 4 1 4 4250 =VDB(L32,M32,N32,O32,P32)
24000 1000 5 2 5 7640 =VDB(L33,M33,N33,O33,P33)
12000 900 7 3 6 2807.57125007437 =VDB(L34,M34,N34,O34,P34)
100000 1000 10 4 5 8192 =VDB(L35,M35,N35,O35,P35)
200000 3000 11 2 5 60554.7311100211 =VDB(L36,M36,N36,O36,P36)

 

 

Excel Function XIRR

The XIRR() function returns the internal rate of return for a supplied series of cash flows.

This cash flow includes initial investment as well as income accrued.

The IRR indicates the profitability of the investment plan.

The Syntax is:

XIRR(Values, Dates, [guess])

Values Dates XIRR() Formula
Initial investment -100 01/02/2017
Income for period1 20 01/03/2017 -0.99999999922656 =XIRR(M46:M47,N46:N47)
Income for period2 35 20/07/2017 -0.818856082111597 =XIRR(M46:M48,N46:N48)
Income for period3 42 01/03/2018 -4.55874532461166E-02 =XIRR(M46:M49,N46:N49)
Income for period4 25 02/05/2018 0.305188077688217 =XIRR(M46:M50,N46:N50)

 

 

Excel Function XNPV

The XNPV() function is used to calculate the net present value of a series of cash flows.

The important thing with this function is that, the cash flow is not necessarily periodic".

The syntax is as follows:

XNPV(discount rate,values array,dates)

discount rate 5% Values Dates XNPV() Formula
Initial investment -1000 01/02/2017
Income from year1 100 01/03/2017 -900.373581154223 =XNPV(5%,M61:M62,N61:N62)
Income from year2 150 20/07/2017 -753.724170054869 =XNPV(5%,M61:M63,N61:N63)
Income from year3 170 01/03/2018 -592.424253828373 =XNPV(5%,M61:M64,N61:N64)
Income from year4 220 02/05/2018 -385.406008566285 =XNPV(5%,M61:M65,N61:N65)

 

You can find similar Excel Questions and Answer hereunder

1) How to read a value from a cell in vba in Excel

2) financial functions cumipmt cumprinc db ddb dollarde dollarfr

3) String split in vba in Excel

4) financial functions accrint accrintm amordegrc amorlinc

5) Import txt file in Excel

6) Generate a list of unique values in Excel

7) How to do date and time zone conversion in excel in Excel

8) Userform initialize vs userform show in Excel

9) financial functions effect irr mirr nominal nper ppmt

10) Highlight row of selected cell in Excel

 

Here the previous and next chapter