Learning Outcomes
- Calculate the internal rate of return
Continuing to use the JuxtaPos scenario where management is considering adding a line of puzzles that necessitates a new machine that will cost $230,000 with an estimated useful life of 6 years and a residual value of $40,000, let’s see if we can provide a number to management that represents the internal rate of return (IRR) on this project.
Again, net annual cash flows are as follows:
| Description | Amount |
|---|---|
| Year 1 | $ 60,000 |
| Year 2 | 60,000 |
| Year 3 | 55,000 |
| Year 4 | 55,000 |
| Year 5 | 50,000 |
| Year 6 (includes the $40,000 proceeds from sale) | 65,000 |
If these amounts were even, we could look for an annuity table and find a factor that represents the annuity and then backtrack that number to an approximate interest rate.
Let’s take a simpler example of an investment of $45,560 that results in an annual cash inflow of $15,000 for 4 years with no residual value. Is this a good investment? We are trading $45,560 for $60,000 over the next four years.
If we look at how the factors in the Present Value of an annuity (a steady stream of future cash flows) are created, we take $45,560 and divide it by the cash flow of $15,000 to get a factor of 3.037 (rounded to the nearest thousandth). On the table, if we follow the row for n=4 across until we find something close to 3.037, we can then extrapolate that the rate of return on this investment is 12%.
| Present Value of Ordinary Annuity of $1 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Periods | 1% | 2% | 3% | 4% | 5% | 6% | 7% | 8% | 9% | 10% | 12% | 14% | 15% | 16% | 18% | 20% |
| Period 1 | 0.990 | 0.980 | 0.971 | 0.962 | 0.952 | 0.943 | 0.935 | 0.926 | 0.917 | 0.909 | 0.893 | 0.877 | 0.870 | 0.862 | 0.847 | 0.833 |
| Period 2 | 1.970 | 1.942 | 1.913 | 1.886 | 1.859 | 1.833 | 1.808 | 1.783 | 1.759 | 1.736 | 1.690 | 1.647 | 1.626 | 1.605 | 1.566 | 1.528 |
| Period 3 | 2.941 | 2.884 | 2.829 | 2.775 | 2.723 | 2.673 | 2.624 | 2.577 | 2.531 | 2.487 | 2.402 | 2.322 | 2.283 | 2.246 | 2.174 | 2.106 |
| Period 4 | 3.902 | 3.808 | 3.717 | 3.630 | 3.546 | 3.465 | 3.387 | 3.312 | 3.240 | 3.170 | 3.037 | 2.914 | 2.855 | 2.798 | 2.690 | 2.589 |
| Period 5 | 4.853 | 4.713 | 4.580 | 4.452 | 4.329 | 4.212 | 4.100 | 3.993 | 3.890 | 3.791 | 3.605 | 3.433 | 3.352 | 3.274 | 3.127 | 2.991 |
| Period 6 | 5.795 | 5.601 | 5.417 | 5.242 | 5.076 | 4.917 | 4.767 | 4.623 | 4.486 | 4.355 | 4.111 | 3.889 | 3.784 | 3.685 | 3.489 | 3.326 |
| Period 7 | 6.728 | 6.472 | 6.230 | 6.002 | 5.786 | 5.582 | 5.389 | 5.206 | 5.033 | 4.868 | 4.564 | 4.288 | 4.160 | 4.039 | 3.812 | 3.605 |
| Period 8 | 7.652 | 7.325 | 7.020 | 6.733 | 6.463 | 6.210 | 5.971 | 5.747 | 5.535 | 5.335 | 4.968 | 4.639 | 4.487 | 4.344 | 4.078 | 3.837 |
| Period 9 | 8.566 | 8.162 | 7.786 | 7.435 | 7.108 | 6.802 | 6.515 | 6.247 | 5.995 | 5.759 | 5.328 | 4.946 | 4.772 | 4.607 | 4.303 | 4.031 |
| Period 10 | 9.471 | 8.983 | 8.530 | 8.111 | 7.722 | 7.360 | 7.024 | 6.710 | 6.418 | 6.145 | 5.650 | 5.216 | 5.019 | 4.833 | 4.494 | 4.192 |
| Period 11 | 10.368 | 9.787 | 9.253 | 8.760 | 8.306 | 7.887 | 7.499 | 7.139 | 6.805 | 6.495 | 5.938 | 5.453 | 5.234 | 5.029 | 4.656 | 4.327 |
| Period 12 | 11.255 | 10.575 | 9.954 | 9.385 | 8.863 | 8.384 | 7.943 | 7.536 | 7.161 | 6.814 | 6.194 | 5.660 | 5.421 | 5.197 | 4.793 | 4.439 |
| Period 13 | 12.134 | 11.348 | 10.635 | 9.986 | 9.394 | 8.853 | 8.358 | 7.904 | 7.487 | 7.103 | 6.424 | 5.842 | 5.583 | 5.342 | 4.910 | 4.533 |
| Period 14 | 13.004 | 12.106 | 11.296 | 10.563 | 9.899 | 9.295 | 8.745 | 8.244 | 7.786 | 7.367 | 6.628 | 6.002 | 5.724 | 5.468 | 5.008 | 4.611 |
We could prove this by looking at it from a slightly different vantage point. What if we invested $45,560 with a promised return of $15,000 over the next four years (basically, an annuity) and we knew it was a 12% return on investment (ROI)? We could discount each of the future cash flows according to the PV of $1 tables and compare it to our initial investment like this:
| Present Value of $1 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Periods | 1% | 2% | 3% | 4% | 5% | 6% | 7% | 8% | 9% | 10% | 12% | 14% | 15% | 16% | 18% | 20% |
| Period 1 | 0.990 | 0.980 | 0.971 | 0.962 | 0.952 | 0.943 | 0.935 | 0.926 | 0.917 | 0.909 | 0.893 | 0.877 | 0.870 | 0.862 | 0.847 | 0.833 |
| Period 2 | 0.980 | 0.961 | 0.943 | 0.925 | 0.907 | 0.890 | 0.873 | 0.857 | 0.842 | 0.826 | 0.797 | 0.769 | 0.756 | 0.743 | 0.718 | 0.694 |
| Period 3 | 0.971 | 0.942 | 0.915 | 0.889 | 0.864 | 0.840 | 0.816 | 0.794 | 0.772 | 0.751 | 0.712 | 0.675 | 0.658 | 0.641 | 0.609 | 0.579 |
| Period 4 | 0.961 | 0.924 | 0.888 | 0.855 | 0.823 | 0.792 | 0.763 | 0.735 | 0.708 | 0.683 | 0.636 | 0.592 | 0.572 | 0.552 | 0.516 | 0.482 |
| Period 5 | 0.951 | 0.906 | 0.863 | 0.822 | 0.784 | 0.747 | 0.713 | 0.681 | 0.650 | 0.621 | 0.567 | 0.519 | 0.497 | 0.476 | 0.437 | 0.402 |
| Period 6 | 0.942 | 0.888 | 0.837 | 0.790 | 0.746 | 0.705 | 0.666 | 0.630 | 0.596 | 0.564 | 0.507 | 0.456 | 0.432 | 0.410 | 0.370 | 0.335 |
| Period 7 | 0.933 | 0.871 | 0.813 | 0.760 | 0.711 | 0.665 | 0.623 | 0.583 | 0.547 | 0.513 | 0.452 | 0.400 | 0.376 | 0.354 | 0.314 | 0.279 |
| Period 8 | 0.923 | 0.853 | 0.789 | 0.731 | 0.677 | 0.627 | 0.582 | 0.540 | 0.502 | 0.467 | 0.404 | 0.351 | 0.327 | 0.305 | 0.266 | 0.233 |
| Period 9 | 0.914 | 0.837 | 0.766 | 0.703 | 0.645 | 0.592 | 0.544 | 0.500 | 0.460 | 0.424 | 0.361 | 0.308 | 0.284 | 0.263 | 0.225 | 0.194 |
| Period 10 | 0.905 | 0.820 | 0.744 | 0.676 | 0.614 | 0.558 | 0.508 | 0.463 | 0.422 | 0.386 | 0.322 | 0.270 | 0.247 | 0.227 | 0.191 | 0.162 |
| Period 11 | 0.896 | 0.804 | 0.722 | 0.650 | 0.585 | 0.527 | 0.475 | 0.429 | 0.388 | 0.350 | 0.287 | 0.237 | 0.215 | 0.195 | 0.162 | 0.135 |
| Period 12 | 0.887 | 0.788 | 0.701 | 0.625 | 0.557 | 0.497 | 0.444 | 0.397 | 0.356 | 0.319 | 0.257 | 0.208 | 0.187 | 0.168 | 0.137 | 0.112 |
| Period 13 | 0.879 | 0.773 | 0.681 | 0.601 | 0.530 | 0.469 | 0.415 | 0.368 | 0.326 | 0.290 | 0.229 | 0.182 | 0.163 | 0.145 | 0.116 | 0.093 |
| Period 14 | 0.870 | 0.758 | 0.661 | 0.577 | 0.505 | 0.442 | 0.388 | 0.340 | 0.299 | 0.263 | 0.205 | 0.160 | 0.141 | 0.125 | 0.099 | 0.078 |
| Year | Amount | Factor | Total |
|---|---|---|---|
| Year 1 | $ 15,000.00 | 0.893 | $13,395 |
| Year 2 | $ 15,000.00 | 0.797 | $11,955 |
| Year 3 | $ 15,000.00 | 0.712 | $10,680 |
| Year 4 | $ 15,000.00 | 0.636 | $9,540 |
| Total present value of cash inflows | $45,570 | ||
| Initial investment | ($45,560) | ||
| Net present value of project | Single Line$10Double line |
Notice that the NPV is very close to zero (rounding prevents it from coming out exactly to zero). This means that the present value of each of these future cash flows is equal to our initial investment if our alternative is a 12% ROI.
Back to JuxtaPos, because the cash flows are not uniform, we can’t use the PV of an annuity table to back our way into the IRR. We might be able to come up with a reasonable estimate though. The average annual net cash flow is $57,500 (total of $345,000 divided by six years). Dividing the initial investment of $230,000 by the average annual net cash flow of $57,500, we get a factor of 4.0. On the table for the row n=6, we see that the factor of 4.0 would fall somewhere between 12% and 14%.
| Present Value of Ordinary Annuity of $1 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Periods | 1% | 2% | 3% | 4% | 5% | 6% | 7% | 8% | 9% | 10% | 12% | 14% | 15% | 16% | 18% | 20% |
| Period 1 | 0.990 | 0.980 | 0.971 | 0.962 | 0.952 | 0.943 | 0.935 | 0.926 | 0.917 | 0.909 | 0.893 | 0.877 | 0.870 | 0.862 | 0.847 | 0.833 |
| Period 2 | 1.970 | 1.942 | 1.913 | 1.886 | 1.859 | 1.833 | 1.808 | 1.783 | 1.759 | 1.736 | 1.690 | 1.647 | 1.626 | 1.605 | 1.566 | 1.528 |
| Period 3 | 2.941 | 2.884 | 2.829 | 2.775 | 2.723 | 2.673 | 2.624 | 2.577 | 2.531 | 2.487 | 2.402 | 2.322 | 2.283 | 2.246 | 2.174 | 2.106 |
| Period 4 | 3.902 | 3.808 | 3.717 | 3.630 | 3.546 | 3.465 | 3.387 | 3.312 | 3.240 | 3.170 | 3.037 | 2.914 | 2.855 | 2.798 | 2.690 | 2.589 |
| Period 5 | 4.853 | 4.713 | 4.580 | 4.452 | 4.329 | 4.212 | 4.100 | 3.993 | 3.890 | 3.791 | 3.605 | 3.433 | 3.352 | 3.274 | 3.127 | 2.991 |
| Period 6 | 5.795 | 5.601 | 5.417 | 5.242 | 5.076 | 4.917 | 4.767 | 4.623 | 4.486 | 4.355 | 4.111 | 3.889 | 3.784 | 3.685 | 3.489 | 3.326 |
| Period 7 | 6.728 | 6.472 | 6.230 | 6.002 | 5.786 | 5.582 | 5.389 | 5.206 | 5.033 | 4.868 | 4.564 | 4.288 | 4.160 | 4.039 | 3.812 | 3.605 |
| Period 8 | 7.652 | 7.325 | 7.020 | 6.733 | 6.463 | 6.210 | 5.971 | 5.747 | 5.535 | 5.335 | 4.968 | 4.639 | 4.487 | 4.344 | 4.078 | 3.837 |
| Period 9 | 8.566 | 8.162 | 7.786 | 7.435 | 7.108 | 6.802 | 6.515 | 6.247 | 5.995 | 5.759 | 5.328 | 4.946 | 4.772 | 4.607 | 4.303 | 4.031 |
| Period 10 | 9.471 | 8.983 | 8.530 | 8.111 | 7.722 | 7.360 | 7.024 | 6.710 | 6.418 | 6.145 | 5.650 | 5.216 | 5.019 | 4.833 | 4.494 | 4.192 |
| Period 11 | 10.368 | 9.787 | 9.253 | 8.760 | 8.306 | 7.887 | 7.499 | 7.139 | 6.805 | 6.495 | 5.938 | 5.453 | 5.234 | 5.029 | 4.656 | 4.327 |
| Period 12 | 11.255 | 10.575 | 9.954 | 9.385 | 8.863 | 8.384 | 7.943 | 7.536 | 7.161 | 6.814 | 6.194 | 5.660 | 5.421 | 5.197 | 4.793 | 4.439 |
| Period 13 | 12.134 | 11.348 | 10.635 | 9.986 | 9.394 | 8.853 | 8.358 | 7.904 | 7.487 | 7.103 | 6.424 | 5.842 | 5.583 | 5.342 | 4.910 | 4.533 |
| Period 14 | 13.004 | 12.106 | 11.296 | 10.563 | 9.899 | 9.295 | 8.745 | 8.244 | 7.786 | 7.367 | 6.628 | 6.002 | 5.724 | 5.468 | 5.008 | 4.611 |
We could estimate the IRR then at 13%.
We could also use a simple Excel formula to calculate IRR:
And by recalculating our NPV analysis using 13% (the factors are not in the table above) we find that the NPV of the project at 13% is more or less equal to the initial investment, proving that the IRR is right around 13%.
Net cash inflows (additional cash revenues – additional cash expenses) 13%
| Year | Description | Factor | Total |
|---|---|---|---|
| Year 1 | $ 60,000 | 0.8850 | $ 53,100 |
| Year 2 | $ 60,000 | 0.7830 | $ 46,980 |
| Year 3 | $ 55,000 | 0.6930 | $ 38,115 |
| Year 4 | $ 55,000 | 0.6130 | $ 33,715 |
| Year 5 | $ 50,000 | 0.5430 | $ 27,150 |
| Year 6 | $ 65,000 | 0.4800 | $ 31,200 |
| Total present value of cash inflows | Single Line$ 230,260 | ||
| Initial investment | $ (230,000) | ||
| Net present value of project | Single Line$ 260Double line |
The IRR is the actual rate of return or Return on Investment (ROI) of the project. If our hurdle rate is 15%, then this project at 13% does not rise to the level of an acceptable endeavor.
Before we calculate the profitability index on this project, check your understanding of the IRR.
