Cannot verify XNPV with PV or HP-12C

[ncw]

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

 

[JoeU2004]

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

 

[JoeU2004]

PS....

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.

Alternatively, the discounted cash flow could be computed
by -PV(rate,days/365,0,CFx). The sum of those terms has the same result as
NPV, within a reason margin of error (due to numerical abberations).

Is one formulation right and the other wrong? TBD.


----- original message -----

 

[ncw]

JoeU2004, thank you for pointing out difference between how XNPV rates and PV
rates are calculated.

I believe this difference constitutes a nontrivial error in the XNPV formula.

The XNPV value can be significantly different from the value computed using
the sum of the PVs; in the example I gave, XNPV/PV is greater than 46%.

I am confident that the vast majority of financial professionals and
academics would agree that XNPV function should use the expression
(1+rate/365)^days instead of (1+rate)^(days/365) in the function.

 

[JoeU2004]

I believe this difference constitutes a nontrivial error in the XNPV
formula.

Does it? (Rhetorical.)

The XNPV value can be significantly different from the value computed
using the sum of the PVs

As I noted, that depends on how you compute the discounted cash flows
("PVs").

I am confident that the vast majority of financial professionals and
academics would agree that XNPV function should use the expression
(1+rate/365)^days instead of (1+rate)^(days/365) in the function.

You're free to have an opinion. It's not uncommon for people to take strong
stands on matters they know little about.

I am not so confident that there is a consensus, much less a "vast
majority", given that financial professionals and academics cannot agree on
how to annualize a period IRR (multiply v. compound). And I think that
would be much easier to agree upon than your issue.

My first impression was similar to yours -- not what a "vast majority" might
say, but simply which I would choose to do.

However, after a millisecond of thought, several factors lead me to think
that XNPV might be doing it correctly -- or at least, one correct way ;-).

First, note that the formula used by XNPV is similar to that used by XIRR.
That formula seems to make some sense for XIRR since it derives an annual
rate. Of course, both could be wrong. But....

Second, the XIRR formula seems somewhat consistent with my interpretation of
the (US) Truth in Lending and Truth in Savings regulations -- especially the
latter.

TIS computes APY by taking the simple rate of return (interest / principal)
and amortizing by years and fractions thereof (365 / days). That is
analogous to what XNPV does, namely discounting by years and fractions
thereof (days / 365).

TIL is more ambiguous to apply by analogy. Arguably, it allows for
fractional unit rates, for example rate/365 for daily compounding. But for
a unit period of a year -- arguably, the unit period for XIRR and XNPV --
fractional periods are computed by days/365.

Of course, TIS and TIL have nothing to with NPV and IRR, which are the
purview of financial analysis. And since I have neither time nor enthusiasm
to research financial analysis texts to see if any demonstrate how to do
such exact computations -- and I wouldn't be surprised if none does -- I
cannot offer nor dispositively dispute an opinion on what the "vast
majority" might conclude, if anything.