• Technology
  • CFO.com | US

Spreadsheets at Work: Rating Your Own IRR

Some tips for doing these key calculations; and introducing "modified" internal rate of return.

In Exhibit 1 (below), we portray a simple multiyear investment and return, in which a single cash outflow (investment) of $100,000 occurs in period 0, and anticipated annual cash inflows (savings) of $31,547 are planned during each of the next four years. After four years of savings, the expected total savings is $126,188. Replacing less-efficient incandescent or older fluorescent lighting in an office building with more efficient compact fluorescent lighting is one example of this type of investment.

Calculating internal rate of return on a spreadsheet

In the examples portrayed, cash outflows or investments are presented as negative numbers; cash inflows or savings are presented as positive numbers. This “sign convention” is dictated by specific functionality requirements within Microsoft Excel. Further, when the present value of these period-specific cash flows is computed, Microsoft Excel reverses the sign.

In Exhibit 2 (below), using the IRR function, the IRR for this stream of cash outflows and inflows is determined to be an even 10 percent. To further prove that this IRR of 10 percent produces a Net Present Value of $0 for these cash flows, each specific annual cash flow has been discounted using 10 percent, producing a net sum of $0.

Calculating internal rate of return on a spreadsheet

Also noted is the number of years (3.17) it takes the savings to pay back the initial investment. The payback method is another mechanism for evaluating an investment. More simplistic perhaps, but the longer it takes for the savings to pay back the investment, the greater the inherent investment risk.

So, how “good” is this IRR of 10 percent? It is only accurate if both the finance rate funding the investment and the reinvestment rate from future savings are both 10 percent. If one wants to change the reinvestment rate and assess the impact, one should use a lesser-known function in the electronic spreadsheet tool suite: the Modified Internal Rate of Return (or MIRR) function.

As Exhibit 3a demonstrates, the MIRR function permits both a finance and reinvestment rate to be associated with the stream of cash outflows and inflows in our investment evaluation example. When both rates are 10 percent, the MIRR is 10 percent, the same as with the IRR function.

However, as can be seen in Exhibit 3b, the reinvestment rate mutes the overall rate of return (the MIRR) when it is lower than the finance rate, although it improves the overall rate when that rate is higher than the 10-percent finance rate previously calculated. This impact might imply that a terrifying increase in the number of additional investment scenarios must now be modeled. Not true. The rate of MIRR change for any series of re-investment rates is fairly constant as can be seen below and in our subsequent investment and savings examples.


Your email address will not be published. Required fields are marked *