Sign in or start a free trial to avail of this feature.
1. Internal Rate of Return
Our first lesson on financial functions focuses on the popular but error-prone IRR function. Learn how to use it effectively and avoid the various traps that catch many users.
Internal Rate of Return
- Very popular in business for evaluating investment proposals
- Tells you the annual compound interest rate equivalent for an investment
=IRR(range): Calculates IRR for a range of consecutive cashflows (assumes re-investment rate is same as initial investment)
=MIRR: Calculates IRR but allows you to specify a separate finance and re-investment rate
- MIRR is more likely to offer you an accurate value than IRR
- One weakness of MIRR is that its re-investment rate is subjective
=XIRR(range, dates): Calculates IRR for non-periodic cashflows
Weaknesses of IRR
- Assumes re-investment rate of return is the same as the initial rate (rarely so)
- Can give multiple answers if you have positive and negative cashflows during investment
- Click here and here to learn more about the weaknesses of IRR
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F2: Jump inside formula
In the last few lessons, we've covered most of the basic logical and arithmetic functions in Excel.
Now let's move on to some finance functions.
Excel has many finance functions, most of which you'll never need to use in business.
But there are a few which come in regular use, and today we're going to cover one of the most popular, which is internal rate of return, or IRR.
IRR is primarily used when evaluating investment proposals.
Unfortunately, the function has a number of weaknesses, which I'll explain later.
But given its popularity in business, it's important for you to know how to calculate IRR and to understand its strengths and limitations.
IRR can be difficult to describe in plain words.
I would probably define it as the annual compound interest rate equivalent for an investment.
And that may not make a huge amount of sense.
So let's look at a simple investment example of buying a house to understand IRR.
Let's assume that we buy the house today for $100,000.
We then live in the house for five years, and at the end of year five, we sell the house for $200,000.
To calculate the IRR from this investment, we have to use a built in formula, because the maths behind IRR can get quite complex.
Each of the investment cash flows must be arranged in the order of years, as shown on screen, and we then write “=IRR”, and then select the array of cash flows.
Close the bracket, and press ENTER.
This calculation gives us an output of just under 15%.
So for this investment of $100,000, we earn a compounding annual return of almost 15%.
IRR’s big advantage is that it allows us to quickly compare different investment options.
If I had two other investment options, one that offered an IRR of 30%, and one that offered a return of 6%, the IRR calculation helps me to quickly decide which investment is the best use of my money.
To increase IRR, our house could be sold for a greater amount, or it could be sold at the same price, sooner.
For example, if we sell the house for $300,000, this increases the rate of return to 24.6%.
Alternatively, if I sell the house in year four, instead of year five, I end up with a return of 18.9%.
In these scenarios, with one investment cash flow and one sale cash flow, IRR works very well.
Unfortunately, things get a little more complex when we add in cash flows during the life of the investment.
Let's undo the changes we've made, and add in a rental cash flow for the house in years one to four.
I'll set this annual rent to $15,000.
Let's also assume that we put this annual rental income into the bank in a deposit account.
The new rental income increases our return dramatically to 25%.
However, this calculation is totally wrong, because the IRR function in Excel assumes that we can reinvest the rental cash flows at the same rate of return as our initial investment.
But we're not reinvesting these cash flows at the same rate, we're putting them in the back.
And so a different interest rate must be applied to the rental cash flows.
To fix this problem, we can use a much better function in Excel called Modified IRR or MIRR.
MIRR allows us to specify a separate interest rate for negative and positive cash flows, during the life of an investment.
So I'll write “=MIRR”, open the bracket, and then select the values.
I'll then select the finance rate, which is the rate at which we would borrow money to finance negative cash flows.
I don't have any negative cash flows, but I'll still enter 3% in this case.
I'll then enter the reinvestment rate, which will be 2%, which is the typical bank deposit rate.
I'll then close the bracket, and press ENTER.
This gives me a new return of 21.3%, almost 4% lower than the initial calculation.
This is a huge error in Excel, and could easily make or break an investment decision.
Unfortunately, many business people and even finance professionals are not aware of this flaw in the IRR calculation.
But now that you do, be sure to always use a different interest rate for cash flows with the modified IRR function.
The IRR function has another limitation, because it assumes that all cash flows happen at the end of each year.
But often this isn't the case.
For example, we might often receive rent once a month.
And we may end up selling the house in the middle of the year.
The classic IRR function would not know how to calculate the rate of return in this case.
Thankfully, Excel has created a function called XIRR, which takes two arguments, the array of cash flows and the array of corresponding dates, which allow you to calculate the IRR for investments with non periodic cash flows.
Let's do this in the example below, where I have an investment on the 31st of December 2012 and I sell on the 1st of July 2015.
I'll type “=XIRR”, open the bracket, and first select the array of values.
I'll then select the array of dates, close the bracket, and press ENTER.
And this provides me with an IRR of just under 32%.
Unfortunately, Excel doesn't easily combine XIRR and the modified IRR I showed earlier.
Instead, this calculation must be done manually.
If you're interested in learning how to do this, you can check out the relevant lesson in my financial modeling course, which I'll link to in the show notes.
I'll also link to a couple of articles that highlight IRR's deficiencies and instead recommend an alternative called Net Present Value, which I'll show you in the next lesson.