Difference in NPV calculation between Excel function and manual calculation

[deacs]

Hello there,

I've noticed a difference in the Excel function calculation for NPV from a manual calculation. Here's the example.

Series of cash flows:
Year 1: -200,000
Year 2: 500,000
Year 3, 700,000

Discount rate = 15%

Excel NPV function returns 664,420

If I discount each of the yearly cash flows starting from Year 2 as follows, I get 638,333 for the NPV:
Year 1: -200,000 (no discounting)
Year 2: 378,072 <-- calculated as = 500,000/(1+.15)^2
Year 3: 460,261 <-- calculated as = 700,000/(1+.15)^3
NPV = -200,000 + 378,072 + 460,261 = 638,333

Does anyone know why this is the case? Which method is more accurate?

Thanks!

[Richard Buttrey]

Hi,

You need to deduct the initial investment from general npv formula. i.e

Please Login or Register  to view this content.

[shg]

Year 2: 434,783 <-- calculated as = 500,000/(1+.15)^1
Year 3: 529,301 <-- calculated as = 700,000/(1+.15)^2

[MoneyMaker]

NPV Calculation using tadNPV function

NPV of annuity due with start of period payments using periodic compounding

=NPV(15%,-200000,500000,700000)*(1+15%)

=tadNPV(15%, {-200000,500000,700000}, 1, 0)

Year ## Payment ########## PVIF @ 15% ## Present Value
0 ##### -200,000.00 ##### 1.000000 ##### -200,000.00
1 ##### 500,000.00 ##### 0.869565 ##### 434,782.61
2 ##### 700,000.00 ##### 0.756144 ##### 529,300.57
Net Present Value (Annuity Due) T= 0 to N-1 ##### 764,083

NPV of an ordinary annuity with end of period payments using periodic compounding

=NPV(15%,-200000,500000,700000)
=tadNPV(15%, {-200000,500000,700000}, 0, 0)


Year ## Payment ########## PVIF @ 15% ## Present Value
1 ##### -200,000.00 ##### 0.869565 ##### -173,913.04
2 ##### 500,000.00 ##### 0.756144 ##### 378,071.83
3 ##### 700,000.00 ##### 0.657516 ##### 460,261.36
Net Present Value (Ordinary Annuity) T= 1 to N ##### 664,420

NPV of annuity due with start of period payments using continuous compounding

=tadNPV(15%, {-200000,500000,700000}, 1, 1)

Year ## Payment ########## PVIF @ 15% ## Present Value
0 ##### -200,000.00 ##### 1.000000 ##### -200,000.00
1 ##### 500,000.00 ##### 0.860708 ##### 430,353.99
2 ##### 700,000.00 ##### 0.740818 ##### 518,572.75
Net Present Value (Annuity Due) T= 0 to N-1 ##### 748,927

NPV of an ordinary annuity with end of period payments using continuous compounding

=tadNPV(15%, {-200000,500000,700000}, 0, 1)

Year ## Payment ########## PVIF @ 15% ## Present Value
1 ##### -200,000.00 ##### 0.860708 ##### -172,141.60
2 ##### 500,000.00 ##### 0.740818 ##### 370,409.11
3 ##### 700,000.00 ##### 0.637628 ##### 446,339.71
Net Present Value (Ordinary Annuity) T= 1 to N ##### 644,607

[Simon Livings]

Hello

Just to be contentious I would suggest that neither approach is accurate !

The NPV calculation assumes that the cash flows occur at the end of each year (ie T1), and your initial outlay is intended to occur at T0 (which is why Richard suggests that this is deducted from the amended NPV calculation.

The next issue is that your cash flows suggest you do not receive any income for two years (ie from T0 to T2) whereas I would assume that there are cash flows at the end of T1.

If the timing of the cash flows is erratic then XNPV is a better alternative, which allows you to specify dates, and so you don't have to remember the theory quite so much.

Hope this helps,
Simon