7. Calculating Internal Rate of Return
Internal rate of return or IRR is an alternative income-based valuation method to NPV. It's more intuitive than NPV but does suffer from a few limitations, as you'll see in this lesson.
Internal Rate of Return
- Very popular in business for evaluating investment proposals
- Tells you the annual compound interest rate equivalent for an investment
- IRR is equal to the discount rate when NPV is equal to zero
=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 will offer you a more accurate rate of return estimate 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
- 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
Although NPV has been gaining popularity in recent years, most companies use a different income based valuation method for evaluating projects.
This is called internal rate of return or IRR.
IRR has some strengths and weaknesses when compared to NPV and we will explore this in detail in the next two lessons.
IRR approaches investment valuation from a different perspective to NPV.
As you'll recall, NPV assumes a discount rate at the beginning that helps you calculate the net present value of future cash flows.
IRR on the other hand, sets the NPV to zero at the start and then calculates the discount rate based on this NPV value.
The best way to understand it is simply to see it in action.
In this example in Excel, I have an investment proposal whereby we buy a house today for $100,000 dollars and we sell it after five years for $200,000.
To calculate the IRR on this investment I'll use an internal Excel function, because the maths behind IRR are quite difficult.
The cash flows must be arranged in consecutive years, as shown on screen, and then we simply pass this array of cells into the IRR function.
So I'll write “=IRR”, open the bracket and select my cells.
I'll then close the bracket and press Enter.
And this tells me that the investment provides a compounding annual return of 14.87%.
IRR's big advantage over NPV is that it doesn't require a discount rate assumption.
It's also more intuitive to compare investments on rates of return than on net present values.
However, IRR does have quite a few limitations which I’m about to show you.
Let's now assume that we rent out the property for the intervening four years at $15,000 per year.
And with this rental income, we open a savings account in the bank and lodge $15,000 each year.
As you can see, the IRR will automatically update and calculate a new rate of return of 25.3%.
However, this new IRR figure is incorrect.
IRR assumes that the rate of return for cash flows during the project are equal to the rate of return on the initial investment.
But here, we’re not investing in a new property with our rental income, we're putting it in the bank.
To fix this limitation of IRR, we can use a related function called modified IRR or MIRR.
This allows us to specify a financing rate and a reinvestment rate for our project.
The financing rate is for negative cash flows during the project and the reinvestment rate is for positive cash flows.
So let's return to our function, and I'll write “=MIRR” and open the bracket.
I'll first select the values, I'll then write a comma and add a finance rate, which here is irrelevant because we don't have any negative cash flows during the project.
But I'll need to enter a value, so I'll write 3%.
And for the reinvestmentrate, which is the savings account interest rate, I'll add 3% as well.
And when I close the bracket and press Enter, you can see that the internal rate of return has updated accordingly.
The IRR is now lower, because my reinvestment rate of 3% is lower than the rate of return on my initial $100,000.
Many analysts and even finance professionals are unaware of this inbuilt IRR assumption, and often overstate the rate of return on projects as a result.
But now that you know about MIRR, you'll be able to avoid this pitfall.
Unfortunately, MIRR does introduce a new variable, the reinvestment rate, which is likely to vary for different investors.
For ease of use, I'm going to use IRR in most of the remaining lessons in this course.
But feel free to replace it with MIRR when calculating returns for your own investments.
Similar to NPV, IRR has a function that exists for non annual cash flows.
On sheet two, I have a separate example where I have annual cash flows for the same project, but at different times of the year.
Here I'll use the function XIRR.
So I'll write “=XIRR”, first enter the values, then enter the dates.
Close the bracket and press Enter.
And this tells me that my internal rate of return is 31.05%.
Unfortunately, there’s no internal function that combines XIRR and MIRR.
To use these functions together, it needs to be done manually, and I'll show you this in a later course on advanced finance.
In the next lesson, I'll describe some further limitations of IRR and explain why you might still want to use it anyway.