Internal Rate of Return

The internal rate of return is an iterative computational method for calculating the interest rate on a series of cash flows. Unlike ordinary PV and FV calculations the cash flows don't have to be even, in fact they can be any series of cash flows that you want, as long as there is at least one cash flow of the opposite sign.

There is no formula to memorise, you'll need either a financial calculator or a spreadsheet. Excel does it perfectly well, you use the =IRR() function. The reason why there is no formula is that the solution is found by the computer guessing the answer, then making more guesses and refining the answer each time until the computer finds the right answer to within a specified precision. It is pretty heavy going, fortunately you don't have to worry about that stuff.

It is useful as I said for calculating uneven cash flows. Here is how one would find the internal rate of return of a house.

I'll assume the purchase price is $100,000.
You'll sell it in 5 years for $150,000.
In the first year you get rent of $6,240, and pay costs of $1,500.
In the second year you get rent of $6,500, paying $2,000 in costs.
In the third year you get rent of $7,800, paying $15,000 in costs.
In the fourth year you get rent of $7,000, paying $1,000 in costs.
In the fifth year, you get rent of $7,200, paying $1,300 in costs.

This is one way to set out the table of cash flows:

Now, if you enter the third column into Excel, you can calculate the IRR.

In some other cell, type =IRR( and then select the cells in the cash flows column and press enter. If you have done it right you'll get 10.80% (though you might have to alter the decimal places of the cell).

Net Present Value

While you have that in Excel, you can calculate another number, the Net Present Value.

As an investor, you should expect a certain rate of return, but you would never consider investing in a risky asset unless it provided a suitable return.

The net present value is the future value of the cash flows, minus the present value.

In Excel you would type =NPV(8%, and then select the cash flow cells again and press enter. The answer you get would be $12,078.17. Which is saying that this investment provides an extra $12,078.17 worth of value to an investor that expects an 8% return, ie it is underpriced by that much.

If you wanted to get 16% from the investment, type =NPV(16%, and select the cells and press enter. You'll get -$16,933.22. In other words, if you were wanting 16% out of this investment, the asking price is $16,933.22 too high.

If you enter the 10.80% internal rate of return, NPV will equal zero. In other words, if you were expecting 10.80%, the price is just right.

The internal rate of return is defined as the discount rate at which the net present value of the income stream equals the price paid. Alternatively, it is the discount rate that makes the net present value equal to zero.