Quickly calculate the IRR on a project or investment
In short:
If you work for a company looking at a capital budgeting project or are an investor looking at a potential investment, odds are you’ll want to know the internal rate of return. But what exactly is the internal rate of return, how do you calculate it, and why is it important?
The internal rate of return (IRR) is the compounded annual rate of return of an investment or project which can be used as a standard measurement for comparison. The advantage of IRR is it provides an estimate for the annual return on an investment that an investor or company makes.
To calculate IRR, companies or investors will find the discount rate that makes the present value of all future cash flows and the investment’s future value equal to the original investment. This process would be time-consuming and labor-intensive to do by hand so we can use excel to automate the process for us.
Key Points
- The internal rate of return is the annual percentage return of an investment or project.
- IRR can be used to calculate the annual growth if the initial investment, expected annual cash flows, and final value of the investment are known.
- Assumptions of IRR are that all cash flows are reinvested at the same rate of return.
In-depth:
IRR Formula and Calculation
When looking at calculating the internal rate of return on a project or investment, there are a few variables you will either know or have to estimate. These variables include the number of expected years of the investment, what your cash flows will be, the timing of those cash flows, the final value of your investment, and the initial investment amount.
With those known variables you can calculate the IRR by inserting different values for the “Rate” variable in the formula above. You will know you have the correct rate for IRR when the sum of all values on the right is equal to the “Initial Investment”.
As you can imagine, this process of guessing, checking, and recalculating would be very time-consuming and labor-intensive. This is why you should not calculate IRR by hand unless you have to as when you calculate it by hand it increases the likelihood you make an error.
Calculating IRR In Excel – Hard & Easy Way
The best way to go about calculating IRR is to use excel. If you need to experiment with the discount rate to see how the present values change you can apply the formula above which will give you the flexibility to do so. Otherwise, excel has a built-in “=IRR” function that will automatically calculate IRR for you.
We’ll demonstrate how to use each method in the scenario below.
Say we are looking at an investment which would require us to initially invest $100,000, we would hold the investment for 5 years, at the end of the 5th year we believe we could sell the investment for $155,000, and while we are holding the investment we would make $12,000 a year in free cash flow. What is the IRR for this Investment?
From this point, we have multiple ways we can find the IRR in excel. Here is both the approach of applying the formula from above where we guess and excel recalculates for us and using the more simple “=IRR” function built into excel.
Step 1 – Using the Formula and Manually Discounting the Cash Flows
Final Result – When you Know you have the Correct IRR
Using the prebuilt IRR Function
How IRR is Used in Decisions
When looking at a project there are at least three things to be considered. First, does the project earn more than the cost of funding for the company, that is, is the return greater than the weighted average cost of capital WACC or hurdle rate? Second, If there is another project option, which project has a better return? The third, is the absolute dollar value gain of the project? IRR allows companies to look at two of these three factors.
Let’s look at a simple example that may illustrate this point better. Say we work for a real estate company and are currently looking at a single-family home which would initially cost us $100,000 to buy and we would hold for 5 years. We believe we could net $12,000 of income off this property per year, that is $12,000 after we pay all maintenance, taxes, insurance, and account for vacancy. Finally, we estimate that we could sell the property for $135,000 at the end of the 5th year.
The company’s hurdle rate or cost of investment capital is 10%.
To answer the first question, we would need to find out if the single-family home as an investment would have a greater return than our hurdle rate. When we compute the IRR of the single-family deal we find it is 17% which is greater than our hurdle rate meaning it meets the first consideration.
Now say we have $3 million we need to put to work and while we’ve run the numbers on the single-family deal we have another option to invest in a $2.91 million multifamily deal that generates $220,000 in cash flow a year and we reasonably expect we could sell it for $3.5 million at the end of the 5th year.
Calculating the IRR on the second option we find it at 10.83% which is only slightly higher than our hurdle rate and over 6% less than the single-family deal. To answer the second and third questions we would favor the single-family deal if the decision was only on comparing IRRs. However, we would actually choose the multifamily option over the single-family option since the total absolute dollar value of the multifamily deal is significantly larger.
IRR Summary
The internal rate of return is an important figure in investing and capital budgeting decisions. The calculation of IRR, while possibly confusing at first, is actually simple and its application is even more straightforward. IRR is used as a comparison metric for annual rates of returns of projects or investments and while IRR is an important factor it is not the only factor that needs to be considered when making a decision.