NPV IRR MIRR

[Santa Claus]

This is more of a mathmatical query than an excel query, but I thought I'd
see if anyone could shed of light on this for me.....

I have the following cashflow series. It is unusual because the income is
recieved upfront (over two years) and the then there is a negative income
stream over the next 6 years.

Using Excel, I get an IRR of 3.55% and a NPV of $2,079.86 using 3% int.

Yr 0 Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8
50,000 35,000 -15,000 -15,000 -15,000 -10,000 -10,000 -10,000 -25,000


I'm wondering if should be discounting future year cashflow projections by
say 3% (for inflation) and then calculating an IRR. This would give a
smaller IRR but is this cheating or would it be acceptable?

Its an interesting scenario!! As I said I thought I'd just see if anyone
else has thought this through?

Maybe there is another function or formulea to consider?

Thanks
Santa

 

[Fred Smith]

You are calculating what economists call nominal rate of return. It's also what
regular people like to see, ie, I'm making 3.55% on my money.

However, as you point out, their purchasing power is being eroded by inflation
each year, in that they can't buy as much with their future dollars as they
could today.

If you want to factor in inflation, you are calculating the real rate of return
(ie, how much is my purchasing power going up).

To calculate real rate of return, it's simple subtraction: Nominal - Inflation =
Real. So you can just quote a 3.55% nominal IRR, or a 0.55% real IRR.

 

[vandenberg p]

Hello:

While it is popular to suggest that the real rate can be computed by subtracting
inflation from the nominal rate, the truth is it does not work. The actual
computation is:

(1+Nominal Rate) = (1+ Real Rate)*(1+inflation rate)

Which means to get the real rate you should use the following:

Real Rate = (1+Nominal Rate)/(1+inflation rate) -1

When inflation is low the error introduced by substracting is small, but
as inflation picks up the error get larger. The degree of the error is
equal to Real Rate(R)*Inflation Rate(I).

The logic:

(1+R)*(1+I)= (1+N)

1+R+I+R*I = 1+N

As long as R*I is small in this expansion the error is small and it
can be dropped and you end up with N = R + I. Of course the problem
is that inflation is really important when it is large and would
make error large.

Pieter Vandenberg


: You are calculating what economists call nominal rate of return. It's also what
: regular people like to see, ie, I'm making 3.55% on my money.

: However, as you point out, their purchasing power is being eroded by inflation
: each year, in that they can't buy as much with their future dollars as they
: could today.

: If you want to factor in inflation, you are calculating the real rate of return
: (ie, how much is my purchasing power going up).

: To calculate real rate of return, it's simple subtraction: Nominal - Inflation =
: Real. So you can just quote a 3.55% nominal IRR, or a 0.55% real IRR.

: --
: Regards,
: Fred


: :> This is more of a mathmatical query than an excel query, but I thought I'd see
:> if anyone could shed of light on this for me.....
:>
:> I have the following cashflow series. It is unusual because the income is
:> recieved upfront (over two years) and the then there is a negative income
:> stream over the next 6 years.
:>
:> Using Excel, I get an IRR of 3.55% and a NPV of $2,079.86 using 3% int.
:>
:> Yr 0 Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8
:> 50,000 35,000 -15,000 -15,000 -15,000 -10,000 -10,000 -10,000 -25,000
:>
:>
:> I'm wondering if should be discounting future year cashflow projections by
:> say 3% (for inflation) and then calculating an IRR. This would give a smaller
:> IRR but is this cheating or would it be acceptable?
:>
:> Its an interesting scenario!! As I said I thought I'd just see if anyone else
:> has thought this through?
:>
:> Maybe there is another function or formulea to consider?
:>
:> Thanks
:> Santa
:>

 

[Fred Smith]

Good point. In dealing with real future rates, the estimate of inflation is
always going to be more inaccurate than the error introduced by simply
subtracting the inflation rate. Regardless, your formula is more accurate.