MIRR results increase then decrease with constant cash flows the longer the life

[langlang98]

I have been preparing business plans recently using the MIRR formula in Excel 2007. The results of the MIRR function will increase initially then begin to decrease as time continues. We use the assumption of an initial investment(no other investments going forward) and while the cash flows are not equal, there is very little change over the life. An example would be on a 6 year plan, the cash flow goes from $8,500 in year 1 to $8,400 in year 6(even drop in cash flows year to year, it would eventually plateau at $8,300 years later) with an initial investment of $13,500. Finance rate of 3.72% and reinvestment rate of 10%. We assumed that the result of the MIRR function would continually increase as there are no other investments beyond the initial investment but the results increase for the first 5 years and then start declining. I am hoping someone has an explaination for this whether it is a financial one or excel based. I am wanting to verify that it is not an anomaly in the formula/algorithm used. I have actually run an example out for years and years(50 years or so) and notice that the initial years have big increases in return(first few years) and then declines much slower than the initial increases. I did notice that the delince always continutes but slows down each additional year. Any help or explaination would be greatly appreciated.

Thanks,
Jim

 

[pplstuff]

Time value of money...

Why not DCF it and see how the cash flows of 8.3k diminish as time goes on. Once your IRR dips below that 10% you should get out of that project since you could obviously get 10% elsewhere.

 

[langlang98]

By saying time value of money, you mean that since the cash flows are not increasing that the $8.3k hitting it twice(once for not increasing and that $8.3k 5 years from now is worth less than $8.3k today).

Kind of funny that you mention DCF as that is in the file but we have been using the formula off of the regular cash flow. I did take a look at it and the same holds true on the results with an increase for a few years then continued decreases. The movements in the results are just less pronounced.