Free Subscription to CFO Magazine

Comment on this Article

You are here: Home : Article : Comment on this Article

Spreadsheets and IRR: It's All in the Timing Overoptimistic or uneven payments can hurt calculations. In the second part of our internal-rate-of-return review, we show how XIRR can help.

Richard Block, CFO.com | US
February 27, 2009


Yes, Martha, there is a reinvestment assumption in IRR

Joe,

In response to your November 20, 2009 post, I have to disagree again. Reinvestment of savings or interest is a fundamental (albeit subtle) precept of IRR and the purpose of the two articles. Once again, I?ll use two investments to demonstrate.

Investment 1 requires $100 today, returning $10 every year for 10 years. At the end of the 10th year, the final interest payment of $10 is returned along with the principal investment of $100. Total amount earned is $100 over ten years; including the principal, the total amount returned is $200.

Investment 2 requires $100 today, returning nothing until the end of the tenth year, when $259.37 will be returned. The total amount earned is $159.37 over the 10 years; and the total returned amount, including the principal is, of course $259.37

The question is: Which is the better investment? This is the typical question we in finance get all the time. And IRR is a great tool to help us evaluate multiple investments by quantifying investments having different streams of savings and different time horizons.

It may be surprising to realize the IRR for both of these investments is 10%! But how can that be if Investment 1 only returns $100, while Investment 2 returns $159.37? There must be that ?time value of money thing? going on here, and the IRR function helps quantify that time value of money so we can evaluate multiple investments appropriately.

Even though Investment 1 only returns $100, it returns the interest in earlier time periods than Investment 2. And the reason that the IRR for this investment is 10%, the same as Investment 2, is because it ASSUMES or PRESUMES that the $10 of interest earned in each year is being reinvested! If we calculate the amount these $10 of yearly interest are worth at the end of year ten year assuming THEY ARE REINVESTED AT 10% compounded annually, (the IRR), the $100 in interest is worth?.(drum roll please)?.$159.37; The same amount as Investment #2!

If we didn?t reinvest the $10 of interest from Investment 1 at all; instead we stuffed the money into a mattress for 10 years, the IRR for Investment 1 would be 7.18%, not 10%. IT DOES MATTER what we do with the savings or returns from our investments.

It is not apparent when using IRR, our finance evaluation friend, that there is a reinvestment assumption; unfortunately there is. We often fail to realize it. This was the purpose of our February 2009 multiple part article on the subject. I hope this explanation helps to clarify and expose this hidden demon.

Richard Block
Babson College
Tatum LLC 

Posted by Richard Block | Nov 23, 2009 5:34 PM ET

No reinvestment assumption, Part 2

One last analogy with the same numbers. Suppose you live on a planet we?ll call Bob. Life is pretty boring on Bob. All investments are risk-free and have a 5 percent return. A new company is forming to undertake a project that will cost $100 today and return $10 in one year and $110 in two years. The company is all-equity financed and has a 100 percent dividend payout policy. You are given the opportunity to buy the one share of stock in the company at a price of $100. This project is more risky than all other projects on Bob and it is determined that the required return on stock in the company is 9.9 percent. Should you buy the share of stock? Of course. The return on the stock is 10 percent, which is greater than the 9.9 percent required return.

Now, suppose that instead of paying the dividend in one year, the company decides to keep the dividend and reinvest at the 5 percent return in the rest of the economy. The company will pay a terminating dividend in year 2. Ignoring the fact that this will alter the risk and return of the company, should you buy the stock now? The answer in this case is no since the return on the stock will fall to 9.77 percent. However, this decrease in return is due to the dividend policy adopted by the company, a decision that is external to the project. If you could invest in the project on its own, you would still accept it since the return of the project itself is still 10 percent. This is the same as the MIRR assumption of reinvestment: The decision to reinvest is a decision external to the project and violates the principle of incremental cash flows.

As a note, I agree that shareholders will not generally receive the IRR of a project. The reason is that company management reinvests intermediate cash flows. If management paid out all project incremental cash flows as dividends and bond repurchases, the shareholders would receive the IRR.

Posted by Joe Smolira | Nov 20, 2009 2:46 PM ET

No reinvestment assumption, Part 1

While I agree that the mathematics that any intermediate cash flows must be invested at the IRR to the end of the project for the MIRR to equal the IRR, I still must disagree that the MIRR yields any meaningful interpretation for the decision to accept a project. The decision to reinvest simply isn?t relevant in an IRR calculation.

Going back to a bond, suppose you have a bond with two years to maturity and a 10 percent annual coupon priced at par. Obviously, the YTM (IRR) on this bond is 10 percent. The YTM on this bond is 10 percent regardless of whether or not the coupon payment in one year is reinvested. Even if the coupon payment is reinvested at a different interest rate, say 5 percent, the YTM on the bond when purchased is still 10 percent. If we do reinvest the coupon payment received in one year at 5 percent, we have $1,205 in two years, which is a return on our original investment of 9.77 percent. This 9.77 percent is not the YTM (IRR) of the bond when purchased, but rather the holding period return. We do have to reinvest the intermediate coupon payment at the YTM of the bond to have a holding period return equal to the original YTM, but YTM and the holding period return are different concepts as IRR is a different concept from MIRR. With bonds, the holding period return is relevant only for a dedicated portfolio, not on the investment return in any particular bond. In the same vein, MIRR is not relevant for the decision to accept a project. In short, the YTM (IRR) of a bond does not depend on the reinvestment of intermediate coupon payments. This applies to the IRR of a project as well.

Consider the pizza example. Suppose I give you the opportunity to give me $100 today and I will pay you $10 in one year and $110 in two years. What is your rate of return on this investment? Obviously it is 10 percent. Whether or not you reinvest the cash flow in one year, the rate of return on this investment is 10 percent. You are free to spend the $10 in one year on pizza or reinvest it somewhere else, but in reinvesting it somewhere else you are removing it from the original investment. Either way, the $10 is a cash inflow for you and it is not possible to reinvest it with me since I will not allow it. In other words, you cannot expand your original investment.

This brings me to a major point why MIRR is incorrect. A fundamental tenant of capital budgeting is that only the cash flows incremental to the project are included in the analysis. For example, sunk costs or allocated costs are excluded since they are irrelevant to the project. Similarly, what we do with any intermediate cash flows created by a project is irrelevant to the analysis of a project. By including any reinvestment assumption, we are now saying that cash flows created outside the project are relevant to the project, a major violation of the incremental cash flow condition. The definition of IRR is the return internal to the project. Reinvestment of any intermediate cash flows is external to the project and therefore irrelevant to the project. In essence, MIRR is calculating the holding period return for investing a sum of money, but reinvestment has nothing to do with the rate of return created by only the cash flows created by a project.


(to be continued)

Posted by Joe Smolira | Nov 20, 2009 2:45 PM ET

Reinvestment is key to IRR

Joe,

But there is a reinvestment assumption when using IRR. Your pizza analogy provides a good example to highlight why reinvestment is the key to IRR.

Say you put $100 in the bank, earning 10% interest per year. (Wouldn't we all like to have an account at that bank?) After year 1, you earn $10. As you state, you remove the $10 to buy a pizza or just put the cash in your mattress. At the end of year 2, your original $100 earns another $10 as the interest rate is still 10%. So, in two years your original $100 has produced $20 in interest and you have either $120 in cash or $110 and an empty pizza box.

Let's change one assumption in the above scenario. Instead of removing the $10 after year 1, you keep it in the bank. Now you have $110 at the beginning of year two and it earns 10% during the year, giving you $11 in interest. At the end of year two you have earned $21 in interest and your bank balance is $121 or $1 more than if you removed the $10 earned year 1 to buy the pizza.

While the interest rate each year did not change, your annual rate of return in these examples did. In the reinvestment or keep-the-cash-in-the-bank scenario, the compounded annual rate of return is indeed 10%. In the pizza or cash-in-mattress scenario, the compounded annual rate of return is only 9.55% even though the interest rate each year was 10%. (Try calculating the future value of $100 growing to $120 and $121 over 2 years using the FV function in Excel).

The key to IRR or interest rates used in PV or FV finance formulas assume reinvestment of savings or interest. The only change in the scenarios above was the decision to reinvest the year 1 interest earned. And that decision raised your annual rate of return from 9.55% to 10%.

The decision to reinvest is very relevant.

I hope this helps.

Richard Block
Babson College

Posted by Richard Block | Nov 20, 2009 2:39 PM ET

No Reinvestment Assumption in IRR!!!

There is no reinvestment rate assumption in IRR, it is simply the interest rate that makes the NPV equal to zero. In fact, the MIRR makes matters worse since the rate of return is now not internal to the project. Here are 2 questions:
1) Does the YTM calculation of a bond assume reinvestment of the intermediate coupons? No. But the YTM calculation of a bond is nothing more than the IRR of the bond.
2) I have a project that costs $100 today and returns $10 in one year and $110 in 2 years. What is the IRR of the project? It is easy to confirm that the IRR is 10%. Now suppose I spend the $10 on pizza in one year. Does this change the IRR? No. Now suppose I take the $10 and reinvest it in another project that returns 2%. Does that change the IRR of the original project? No. The fact that the $10 was taken out of the original project and invested in another project is irrelevant. The return on the 2nd project has nothing to do with the return on the first project since the IRR only deals with the "return interal to the project" (rearrange to IRR if you wish), not what is done with the intermediate cash flows.

Posted by Joe Smolira | Nov 16, 2009 5:13 PM ET