Internal Rate of Return

Internal rate of return (IRR) is the annual compound interest rate at which an investment’s net present value is zero. Projects whose IRR is higher than the company’s cost of capital are good candidates for investment and projects with highest IRR must be selected. Let’s learn how to calculate IRR manually or using Excel.

Companies invest in different projects to generate value and increase their shareholders wealth, which is possible only if the projects they invest in generate a return higher than the minimum rate of return required by the providers of capital (i.e. shareholders and debt-holders). The minimum required rate of return is called the hurdle rate. Projects must generate a return at least as much as the hurdle rate.

IRR is based on the concept of time value of money. The initial investment in any project must be compensated by net cash flows which far exceed the initial investment. The higher those cashflows when compared to the initial outlay, the higher will be the IRR and the project is a promising investment. IRR is the rate of return that fits the following equation:

$$ \frac{CF_1}{{(1+IRR)}^1}+\frac{CF_2}{{(1+IRR)}^2}\\+\frac{CF3}{{(1+IRR)}^3}+\ldots+\ \frac{CF_n}{\left(1+IRR\right)^n}\\−Initial\ Investment \\=0 $$

CF refers to periodic cash flows and initial investment means total amount invested in the project at t=0

Calculation

There are multiple ways in which we can calculate IRR: using Microsoft Excel IRR or XIRR functions, using any financial calculator or through a manual hit-and-trial method.

You can follow these links above for thorough illustration of Excel IRR and XIRR.

In manual calculation of IRR, we must use a hit-and-trial method because there is no algebraic formula in which we plug the cash flows and initial investment and get IRR. The equation above is the equation for net present value. We must guess an interest rate and find the net present value by discounting the cash flows using that rate. If the net present value is higher than 0, let’s call it NPV1, we know we must increase the guessed rate because higher interest rate means lower present value. We should keep increasing the rate until we get close to zero. If the initial net present value is lower than 0, let’s label it NPV2, we must decrease the rate and recalculate net present value to get close to zero.

If we find two NPVs, one higher and one lower than zero, we can use the following formula for a quick calculation of IRR:

$$ IRR=r_a+\frac{{\rm NPV}_a\times(r_b-r_a)}{{\rm NPV}_a-{\rm NPV}_b} $$

Where ra is the lower interest rate, rb is the higher interest rate and NPVa and NPVb are the net present values calculated using the interest rate ra and rb respectively.

Example

Your company is interested in a project that will generate cash inflows of $300,000, $350,000, $370,000, $330,000 at the end of each year for 4 years. Cash outflows before taxes amount to 50% of the cash inflows. The project will require purchase of fixed assets of $550,000 which is to be depreciated using straight-line method with a salvage value of $150,000. After the initiation of project, existing equipment with book value of $80,000 can be sold at $100,000. The hurdle rate applicable to the project is 12% and the company’s tax rate is 40%. Working capital of $130,000 is required at time 0 which shall be released at the end of the project.

Let’s calculate the IRR of the project.

The first thing we need is to get a complete picture of the project’s cash flows. The initial investment is calculated below:

Equipment A 550,000
Working capital B 130,000
Sales proceeds of equipment disposed off C 100,000
Carrying value of equipment disposed off D 80,000
Taxable gain E=C-D 20,000
Tax on gain @ 40% F=E×40% 8,000
After-tax sales proceeds G=C-F 92,000
Total initial investment outlay H=A+B-G 588,000

We also need to find the net project cash flows which are worked out as follows:

Year 0 1 2 3 4
Cash inflows 300,000 350,000 370,000 330,000
Less: operating costs @ 50% 150,000 175,000 185,000 165,000
Operating cash flows before tax 150,000 175,000 185,000 165,000
Less: depreciation (straight-line) 100,000 100,000 100,000 100,000
Income before tax 50,000 75,000 85,000 65,000
Less: taxes @ 40% 20,000 30,000 34,000 26,000
Net income 30,000 45,000 51,000 39,000
Add: depreciation 100,000 100,000 100,000 100,000
Net operating cash flows 130,000 145,000 151,000 139,000
Terminal cash flow 280,000
Initial investment outlay -588,000
Net cash flows -588,000 130,000 145,000 151,000 419,000

The terminal cash flow above equals the sum of salvage value ($150,000) and recoupment of working capital ($130,000).

Let’s calculate IRR using the hit-and-trial method. We can form the following equation to work out IRR manually:

$$ 0=\ \frac{$130,000}{{(1+IRR)}^1}+\frac{$145,000}{{(1+IRR)}^2}+\frac{$151,000}{{(1+IRR)}^3}+\frac{$419,000}{\left(1+IRR\right)^4}-\ $588,000 $$

Let’s calculate NPV at 10%:

$$ NPV\ at\ 10\%\\=\frac{$130,000}{{(1+10\%)}^1}+\frac{$145,000}{{(1+10\%)}^2}+\frac{$151,000}{{(1+10\%)}^3}+\frac{$419,000}{\left(1+10\%\right)^4}-\ $588,000\\=$49,648 $$

Let’s calculate NPV at 15%

$$ NPV\ at\ 15\%\\=\frac{$130,000}{{(1+15\%)}^1}+\frac{$145,000}{{(1+15\%)}^2}+\frac{$151,000}{{(1+15\%)}^3}+\frac{$419,000}{\left(1+15\%\right)^4}-\ $588,000\\=-$26,466 $$

NPV at 10% is higher than 0 and NPV at 15% is lower than zero so we know IRR must be between 10% and 15%. An estimated IRR can be worked out as follows:

$$ IRR=r_a+\frac{{\rm NPV}_a\times(r_b-r_a)}{{\rm NPV}_a-{\rm NPV}_b}\\=10\%+\frac{$49,648\times(15\%-10\%)}{$49,648-(-$26,466)}=13.26\% $$

Alternatively, we can work out exact IRR using Excel IRR function.

IRR Excel Calculation Example

Our approximation of 13.26% is quite close to the exact calculation of 13.15%.

Limitations

Studies indicate that internal rate of return is one of the most popular capital budgeting tool, but theoretically net present value, a measure of absolute value added by a project, is a better indicator of a project’s feasibility. This is because sometimes where the cash flows are unconventional i.e. there are net cash outflows other than the initial investment outlay, we may get multiple results for internal rate of return. This phenomenon is called multiple IRR problem. Further, internal rate of return technique assumes that all project cash flows are reinvested at the internal rate of return, which is rarely the case because new investment opportunities are seldom readily available. A variant of internal rate of return called the modified internal rate of return, attempts to mitigate this problem by calculating the internal rate of return where the net cash flows are reinvested at a rate lower than the internal rate of return itself.

Written by Obaidullah Jan