Internal Rate of Return

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 six 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, 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 four 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 to exactly 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.

B

Let’s go 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:

Screenshot of Excel showing an IRR calculation.

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.

Practice Question