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.
To explore more Kubicle data literacy subjects, please refer to our full library.
The IRR Function (00:11)
The Internal Rate of Return, or IRR, is used to evaluate investments. IRR can be defined as the annual compound interest rate equivalent for an investment. In this lesson, we use IRR to evaluate an investment in a house. We spend money purchasing the house, then several years later we receive money from selling the house.
In Excel, the IRR function can be used to calculate the Internal Rate of Return. The function takes a single argument, the array of cash flows we expect to receive from the investment. The output is a percentage, which we can compare with other potential investments. The IRR will increase if we sell the house for a higher amount, or if we sell the house sooner.
Modified IRR (02:33)
The IRR function works well when there is a single investment cash flow and a single sale cash flow. However, it falls down when there are additional cash flows in the life of the investment. For example, we might earn income by renting out the house between buying it and selling it. In this case, the IRR function assumes we can reinvest the rental cash flows at the same rate of return as the original investment. This is generally not a realistic assumption, and it can lead to serious errors when using the IRR function.
Instead, you should use the MIRR function, or Modified IRR. This takes three arguments. First is the array of cash flows. Second is the finance rate, which is the rate we would borrow at to finance negative cash flows. Third is the reinvestment rate, which is the rate of return on rental cash flows. If we put rental cash flows in a bank account, this rate would be the bank deposit rate.
Non-Periodic Cash Flows (04:32)
Another limitation of the IRR function is that it assumes that cash flows happen regularly at the end of each year. This often isn’t realistic, and cash flows will often occur at irregular, or non-periodic cash flows.
If you have non-periodic cash flows, you should use the XIRR function. This takes two arguments. First is the array of cash flows, and second is a corresponding array of dates. The function then calculates the IRR correctly for non-periodic cash flows.
Unfortunately, there is no built-in formula that combines MIRR and XIRR; instead you must calculate this manually.
Articles illustrating the inefficiency of IRR:
In this course we'll introduce some of the most important finance functions available in Excel. Excel has many finance functions, most of which you'll never need to use in business, but there are a few that come in regular use. In this lesson we'll learn how to calculate the internal rate of return, or the IRR.
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 learn 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.00, we then live in the house for five years, and at the end of year five we sell the house for $200,000.00. To calculate the IRR from this investment, we have to use a built in formula because the math 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 equals IRR, and 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.00, 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.00, this increases the rate of return to 24.6%. Alternatively, if I sell the house in year 4, instead of year 5, 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 undue 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.00.
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 bank, 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 equals MIRR, open the bracket and then select the values.
I'll then select the finance rate, which is the rate at which we'll 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 equals 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 efficiencies, and instead recommend an alternative called net present value, which I'll show you in the next lesson.