The calculation for internal rate of return or IRR has many uses. For individuals, the most common use is to find out the rate of return our investments produce. For corporations, internal rate of return can be used to determine which project should be implemented or whether or not a stock buyback plan should be initiated. For the bulk of those reading this, you are most interested in calculating IRR for your investments, so this post will talk about this use of calculating internal rate of return.

What is Internal Rate of Return?

I will do my best to not lose anyone here, but I want to let everyone know that there is math involved in calculating IRR. Before we get to the math however, we need to have a clear understanding of what IRR is. Internal rate of return is the interest rate that will bring a series of cash flows to a net present value of zero. For example, let’s look at someone’s mortgage payment. If I took out a mortgage loan for $150,300 for 30 years, with payments of $937 each month, the internal rate of return is 6.375%.

This sounds simple enough, but internal rate of return actually gets more complicated. In order to fully understand IRR, we need to address and understand net present value as well.

Net Present Value

Net Present Value, or NPV assumes that one dollar today is worth more than one dollar tomorrow. This is because if you invest your one dollar today, you will have more than one dollar tomorrow. When looking at NPV for investments, you want the net present value to be a positive number which means you are getting a bargain. If NPV is negative, then you aren’t getting a deal.

For example, let’s say you own a restaurant and are looking to sell it for $1,000,000. I just happen to be looking to buy a restaurant so I would begin by estimating how much future cash flows your restaurant will produce into one lump-sum present value amount. If my calculations for these future cash flows total less than $1,000,000 I am not going to buy your restaurant because I will be paying more for it than I will earn back. But, if my future cash flows net present value amount is greater than $1,000,000 I would buy your restaurant.

Going back to the example above regarding a mortgage, in order to get to a new present value of zero, we see that paying the monthly cash flows of $937 for 30 years will allow us to pay off the $150,300 balance at an interest rate of 6.375%.

How to Calculate Internal Rate of Return

Now that we have a better understanding of NPV, we can go ahead and calculate IRR. Here is the basic formula for IRR:

Where:

• C = cash flows
• n = time period(s) – or years
• r = rate of return

Let’s go ahead and work on a problem now. Let’s say I buy rental property for $200,000. I plan on renting the house for $1,000 a month ($12,000 annually), and holding the house for 5 years. At that point I plan on selling the house and estimate I will get $225,000 from the sale. Here is what the formula looks like:

In order to solve for “r” we use the trial and error method (don’t worry, I’ll show you an easier way next). In order to solve for “r” we have to guess a rate of return. Let’s go with 10%. We set up the problem like this:

If we take the $200,000 as a negative since it was an outflow of cash and add back in the cash flows we would have this:

($200,000) + $10,909.09 + $9,917.36 + $9,015.78 + $8,196.16 + $147,158.40 = $14,803.26

Since the goal of NPV is to equal zero, we can see that we guessed too high since our NPV equals $14,803. If we estimate an interest rate of 8%, we have the following cash flows:

($200,000) + $11,111.11 + $10,288.07 + $9,525.98 + $8,820.36 + $161,298.20 = ($1,043.74)

We are almost at zero! If we plug in an interest rate of 8.1% our cash flows total ($210.75). For all intents and purposes, this is close enough. We won’t go our past the first decimal place, but you can if you want to.

Note that in the final year’s cash flow I took into account the sale price of the house. Be sure that you include it as well.

How to Calculate Internal Rate of Return Using Excel

The much easier and faster way for calculating internal rate of return is to use Excel. Here is how it looks all set up:

Once set up, you click on an empty cell below your numbers and click on the “fx” symbol to insert a function and search for IRR. When you find it, highlight it and click OK. Then, you click in the “Values” box and highlight your cells as seen below:

From there you can click OK – there is no need to guess. You’ll see that our answer is 8.13%, which is what we arrived at when we used the trial and error method.

Using Internal Rate of Return For Investments

When looking at other investments, you will have to pay close attention to get your true IRR. For example, let’s say you bought a mutual fund for $5,000. In year three you bought an additional $250 worth and in year six, you sold everything for $6,000. You might set up the Excel equation like this and see that you earned a 7% return.

Unfortunately, this is incorrect. Each cell is looked at as a period of time. Therefore, the correct equation looks like this:

You actually earned a 2.3% return. However, if the mutual fund paid dividends. then this too would be wrong. Let’s assume the fund paid a $50 dividend in years 1 and 2 and a $75 each year thereafter: