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) |