IRR down, NPV goes up?

[Jack.Excel]

Hi guys,

I have been playing around with the IRR/NPV relationship and discovered that when the cash flow is heavily weighted towards the exit valuation the NPV and IRR seem to display an inverse relationship over time.

However, mathematically this doesn't seem to make sense as the IRR is simply the discount rate to solve for NPV=0.

I have attached an example to demonstrate this issue and would be very grateful if anyone has any insights on the matter.

Many thanks,
Jack

[MrShorty]

I am not in financials, so I'm not sure I understand all of the nuances of financial functions. That said, according to the help file (https://support.office.com/en-us/art...b-ac28acf2a568 ), the equation for NPV (and IRR) is

NPV=sum(for i=1 to n)[x(i)/(1+r)^i]

Assuming that the r in this equation is the same as the IRR function, then it seems pretty clear to me that there should be an inverse relationship between NPV and r (r=IRR), since r is in the denominator.

[Jack.Excel]

Yes, when the formula is used to solve for the NPV, the higher the discount rate (r) the lower the NPV.

However, when the formula is used to solve for the IRR (r) to return NPV=0, the higher the PV of the cash flows the higher the solution to r. However, my second example shows the inverse.

Thanks.

[MrShorty]

Disclaimer again -- I am not in financials, so I'm not sure what all of these things mean. As a mathematician, here's what I see:

NPV=sum(for i=1 to n)[x(i)/(1+r)^i] --> Rewrite to highlight nth term
NPV=sum(for i=1 to n-1)[x(i)/(1+r)^i] + x(n)/(1+r)^n

Looking at your examples, I see 4 variables: NPV, n, x(n), r
In column B, where you calculate IRR NPV is constant (at 0 as evidenced by column D), and you are varying n and x(n) at the same time and calculating r. Looking at the relationship between these three variables:
As x(n) increases - r must also increase (NPV and n held constant).
As n increases - r must decrease (NPV and x(n) held constant).
When you change both x(n) and n, then the overall effect on r will depend on which changes more (in your example, it appears that the effect of n is greater than the effect of x(n), so IRR decreases overall).

In column C, you are holding r constant (at 4%), and varying x(n) and n to see the effect on NPV.
As x(n) increases (r and n constant), NPV will also increase
As n increases (r and x(n) constant), NPV will decrease.
As before, when you change both x(n) and n, the overall effect on NPV will depend on which effect is larger. In your example, the effect of x(n) is larger, so the overall effect is for NPV to get larger.

I would venture to say that your comparison between column B and column C is not an exact comparison. If we could visualize this function in four dimensional space, B and C are looking at different directional derivatives on that surface. Not understanding financial theory, I'm not sure what the relationship should be between those two derivatives, or what that relationship means.

[Jack.Excel]

Great explanation, I think that is exactly the issue.

I'm using a constant 4% for the NPV, which is sufficiently small to give an increasing NPV as n increases (Col. C)

However, the IRRs are proportionally larger than the increases in x(n), therefore, the IRR is decreasing with each increase in n (Second example Col. B)

Thanks very much for your insights, much appreciated!