NPV vs. XNPV

[Mr930]

I have set up a table to compare a set of cashflows using XNPV and NPV. The cashflow is the same for both functions, it has a time zero investment followed by 600 monthly inflows.

I created a graph to show the differences of the calculated values vs. discount rate on the X axis. I was shocked at how far the curves diverge, at the end (600%) XNPV yields -14,353 and NPV yields -86,138.

I understand the formulas but I did not expect the difference to be so large. Is this to be expected? In my XNPV formula I included the initial investment in the range I passed and in NPV I did not, but subtracted it from the calculated NPV value (per the help file).

I am not sure if I could load a screen shot of my graph on this board or not.

thanks
Fred

 

[dcardno]

That does seem like a large difference, but...
> How does it compare to the magnitude of the cash flows you are using in the calculations?
> How close are the values in the range of -say- -15% to +25%? An annual (I assume) rate of 600% is high to the point where it is almost meaningless - cash flows more than a few months out (say a year, two at most) are worth virtually nothing and the result is dominated by the early cash flows.
> How are you determining the discount rate for the NPV formula? XNPV implicitly reflects daily compounding, while NPV compounds on each cash-flow date - at the sort of rates where you are noting differences the monthly v. daily rates may be part of the cause.
> One other thing I forgot - how are you representing months that have no cash flow? NPV ignores a blank in counting the number of periods that have elapsed between cash flows, so that subsequent values are discounted by one less period (or fewer periods, for multiple blanks) than they should be. XNPV doesn't "count" periods - it discounts based on the date values, so the blank cell doesn't affect the calculation of subsequent (or prior - XNPV is only sensitive to date order on the initial cash flow).

I am going to muck around with my own example - I'll let you know what I find...

 

[Mr930]

AT 15% DR I get XPV=248,188 and NPV of 243,771. The undiscounted value is 330,062

I built my table with discount rates to do a check on my IRR calculations. So the numbers were created by me.

 

[dcardno]

Fred:
> my last comment was wrong: XNPV fails if there is a blank in the cash flow series; NPV miscalculates - if you are getting results for XNPV then the issues is not a blank in the cash flow series.

When asking about the magnitude, I was more wondering about the absolute cash flows: is this a difference of ~5K on gross cash flows of +/- $1M or on gross cash flows of 100M?

 

[Mr930]

The largest monthly cashflow is the initial investment at 147,278, the next month is 38,109. It declines to zero in month 108 with zeroes thereafter for a total range size of 1200 months.

Not sure if that address your magnitude question.

 

[dcardno]

Fred - I can't come close to those sorts of differences between NPV and XNPV. With an initial investment of $150K and a return of $40K in the first month, then an evenly declining monthly return (an arithmetic series) for 108 months to come to a total non-discounted cash flow of ~$330K the difference between XNPV and NPV at a 15% discount rate is ~$12. Assuming that the 'month one' cash flow is an additional investment instead of a return, and adjusting the monthly cash flows to get the same non-discounted value, the difference at 15% discount rate is ~$6. Even at the very high discount rates (~600%) the difference in NPV only grows to ~$100 to ~$340, depending on whether that 'month one' cash flow is a return or a further investment.

 

[Mr930]

I figured out why I got the big difference. In the NPV formula I was using the monthly discount rate as the annual/12. When I changed it to be:

( (1+i)^(1/12) ) -1

The curves laid on top of each other. I had been beating around all day with the best way to convert from an annual number to a monthly number.

thanks for checking it out
Fred

 

[dcardno]

In the NPV formula I was using the monthly discount rate as the annual/12

Yeah - that'll do it! Glad you got it figured out...