Strange NPV Results

Hi All,

I am running some NPV calculations that are not making sense to me. If I have a stream of both positive and negative cash flows I would expect the trailing NPV to adjust in value each period by a similar amount to the current period cash flow. In the attached example when there is a negative 5 I would have expected by NPV to go up by something a little less then 5, but instead it goes up by ~20! Why is this?

Originally Posted by windpowerfinance

I am running some NPV calculations that are not making sense to me.

Frankly, your expectations do not make sense to me. More to the point: generally, I would be hard-pressed to have any expectation.

First, note that when we write NPV(5%,L9:L19), Excel discounts the first term. That is, the PV of L9 is L9/(1+5%). In effect, NPV calculates to the time before L9, not to the time associated with L9. If that is your intent, fine. But most people want to calculate the NPV to the time associated with L9, to wit: L9+NPV(5%,L10:L19).

In any case, returning to your question....

Note that NPV can be calculated as follows:

Please Login or Register  to view this content.

Note the difference in the exponents of similar terms. That will be make a significant difference in the NPV.

IMHO, it would be difficult to generally predict the difference without doing the calculation.

But algebraically, we see that for row 13, the difference would be M12*5% + L12. Similarly for any pair of rows.

Derivation....

M12 - M13 = M12 - M12*(1+5%) + L12 = M12*(1 - (1+5%)) + L12 = M12*5% + L12