IRR Calculation
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. 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.
In manual calculation of internal rate of return, 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 exact IRR. In the hit-and-trial method, we start by arbitrarily selecting a discount rate and working out the net present value of the project by discounting the cash flows using that rate. If the net present value is positive, we call it NPVa. Since there is an inverse relationship between present value and discount rate, select a higher discount rate and rework the NPV. Keep increasing the rate until we get close to zero. If the initial net present value is negative, let’s label it NPVb, decrease the discount rate and recalculate NPV to get close to zero. The discount rate at which NPV = 0 is the IRR.
Instead of going through all these iterations, we can use the two NPVs, one higher and one lower than zero, to approximate IRR using the following formula:
| IRR = ra + | NPVa × (rb − ra) |
| NPVa − NPVb |
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 NPV at 10% and 15%:
| Period | Cash Flow | PV Factor @ 10% | PV @ 10% | PV Factor @15% | PV @ 15% |
|---|---|---|---|---|---|
| 0 | (588,000) | 1.0000 | (588,000) | 1.0000 | (588,000) |
| 1 | 130,000 | 0.9091 | 118,182 | 0.8696 | 113,043 |
| 2 | 145,000 | 0.8264 | 119,835 | 0.7561 | 109,641 |
| 3 | 151,000 | 0.7513 | 113,449 | 0.6575 | 99,285 |
| 4 | 419,000 | 0.6830 | 286,183 | 0.5718 | 239,565 |
| NPV | 49,648 | (26,466) |
NPV at 10% is positive and NPV at 15% is negative so we know IRR must be between 10% and 15%. IRR can be estimated using the following formula:
| IRR = 10% + | $49,648 × (15% − 10%) |
| $49,648 − (−$26,466) |
| IRR = 10% + | $49,648 × (15% − 10%) |
| $49,648 + $26,466) |
IRR = 10% + 3.26% = 13.26%
If we work out IRR using Excel IRR function, we get a value of 13.15% which is pretty close to our approximation.
by Obaidullah Jan, ACA, CFA and last modified on