"ncw" wrote:
Could someone please help me understand the discrepancy between
the first two calculations and the second two calculations?
Looks like it's the difference between (1+rate/365)^days and
(1+rate)^(days/365).
Using PV for the purposes of NPV, each discounted cash flow
is -PV(rate/365,days,0,CFx), which is computed by CFx / (1+rate/365)^days.
For NPV, each discounted cash flow is computed by CFx / (1+rate)^(days/365).
If you sum the discounted cash flows in each case, you will see the
discrepancy.
it seems to me that either
my logic is wrong or there is an error in XNPV.
Or perhaps neither. (
TBD)
----- original message -----
"ncw" wrote in message
...
Using both Excel 2003 and Excel 2007, I calculated the net present value
of
the stream of payments shown below four different ways:
1) Using the XNPV function for the full stream of payments: Result =
$25.20
2) Adding the individual XNPVs: Result = $25.20
3) Adding the individual Excel PVs: Result = $17.19
4) Using the HP-12C financial calculator to compute and add the individual
PVs: Result = $17.19
The purpose of calculations 3 and 4 was to manually verify the correctness
of calculations 1 and 2. Since they don't match, it seems to me that
either
my logic is wrong or there is an error in XNPV.
Could someone please help me understand the discrepancy between the first
two calculations and the second two calculations?
Pmt Amount Date Days Cum XNPV PV
Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00)
Cf1 $100 04/01/09 90 90 $94.65 $94.02
Cf2 $100 06/30/09 90 180 $89.58 $88.40
Cf3 $100 09/28/09 90 270 $84.78 $83.12
Cf4 $100 12/27/09 90 360 $80.24 $78.15
Cf5 $100 03/27/10 90 450 $75.95 $73.48
Total= $25.20 $17.19
=XNPV $25.20
Hurdle Rate= 25.0%
Hurdle Rate/365= 0.000684932
Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00)
Start $0 01/01/09 0 0 $0.00 $0.00
Cf1 $100 04/01/09 90 90 $94.65 $94.02
Start $0 01/01/09 0 0 $0.00 $0.00
Cf2 $100 06/30/09 180 180 $89.58 $88.40
Start $0 01/01/09 0 0 $0.00 $0.00
Cf3 $100 09/28/09 270 270 $84.78 $83.12
Start $0 01/01/09 0 0 $0.00 $0.00
Cf4 $100 12/27/09 360 360 $80.24 $78.15
Start $0 01/01/09 0 0 $0.00 $0.00
Cf5 $100 03/27/10 450 450 $75.95 $73.48
Total $25.20 $17.19