The CashFlow Challenge #2

Rate of Return

Question

As CFO of a small manufacturing company, it is important that you monitor carefully the new project opportunities presented to the firm. Your hurdle rate is now 22%.
A new 5-year venture has been proposed which is projected to return the following contributions (end of year) to Earnings and Overhead:

The initial cost of the project is $325,000, but an additional cost in the amount of $180,000 is expected to be  incurred at the end of year 2.

If your safe rate of investment is 5%, will this project meet your new investment criteria? What will be the raw (unadjusted)  Internal Rate of Return?

What will be the Modified Internal Rate of Return if you can use the cashflows from previous years to fund deficits?

Here's the Answer

Next Problem

Answer

The combination of a positive cashflow of $157,500 in year 2, together with an additional investment at the end of year 2 of -$180,000 results in a net cashflow at the end of year 2 of  -$22,500.

Using this net cashflow series,  the raw Internal Rate of Return is found to be 30.15%.

In calculating the raw IRR, the negative cashflow of -$22,500 is theoretically discounted back at the IRR to Present Value, which  implies that this relatively small amount can be invested at the IRR (30.15%) to yield $22,500 in 2 years. Few such investments exist which provide the certainty required to cover the future negative cashflow at this rate of return.
Therefore, we must adjust this cashflow to provide for the future negative flow by investing sufficient sums today at the safe rate.

Calculating the MIRR:

In order to offset the second year's  negative cash flow, invest only sufficient funds from the first year at the safe rate for 1 period so that $22,500 will be available at the end of year 2.   

We find that $21,428.57, invested for one year @ 5% will yield the $22,500 needed to cover the negative cashflow at the end of the second period.

This re-investment will neutralize the negative cashflow in year 2. But this amount taken from the cashflow in Year 1 will leave  a remaining cash flow of  $82,571.43 ($104,000 - 21,428.57) in the first period. The net cashflow in the second period will now be zero.                                       

Now recalculate the IRR (now the MIRR)  using the adjusted cashflow series.

The Modified Internal Rate of Return is 29.77%

This method of offsetting negative cashflows is much to be preferred over the method which appears in the HP-12C handbook, and which is copied by Excel:

"discount all negative cashflows back to present value, and  compound all  
   positive cashflows forward at the re-investment rate to Future Value."  

At  what re-investment rate should the positive cashflows be compounded forward?
And how would we determine that rate?

The requirement for additional up-front cash, as the result of discounting all negative cashflows to Present Value,  has a pronounced negative effect on the IRR, lowering yield returns significantly.
The re-investment rate is never given, but the suggested re-investment rate is mentioned as the the investor's opportunity cost of funds.

The method of determining the MIRR, which requires the discounting of all negative cashflows back to a Present Value, and the compounding forward of all positive cashflow to Future Value at an unspecified re-investment rate, is the source of the often-repeated fallacy that "the IRR depends upon the reinvestment rate of the cashflows flowing from the investment."
Nothing is farther from the truth.

The method described here will always result in a higher MIRR, and will always obviate the need to choose a "re-investment rate."

Another Problem?