| TVM: NPV and IRR |
| Practice Problem Worksheet Using Excel Formulas |
|
| A good understanding of the principles of capital budgeting theory and of capital budgeting measurement techniques is necessary to understand how to correctly calculate the net present alue and internal rate of return statistics. Accordingly, it is recommended you thoroughly read and understand Chapter 13, "Weighing Net Present Value and Other Capital Budgeting Criteria," from your M: Finance text before reviewing the following problems and solutions. |
|
|
|
|
|
| Net Present Value (NPV) Calculation |
| Solving for the Net Present Value (NPV): |
| Problem 1: Here is a simple net present value (NPV) calculation problem: |
|
| What is the NPV statistic for the following indicated cash flows assuming |
| the appropriate cost of capital is: | | | 14% |
| Time: | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash Flow: | -100,000 | 25,000 | 35,000 | 45,000 | 35,000 | 25,000 |
|
| Solution: Using the Excel, Formulas, Financial, NPV formula that calculates the present values of cash flows from year 1 and beyond, and first entering the cash flow at time point 0 (outside of the NPV formula function) because cash at time point 0 does not require a present value calculation because the present value of an amount at time point 0 (or now) equals its stated value: |
|
|
|
|
| NPV = Minus Cash Outflow at time point 0 + [NPV (Int rate or cost of capital, CF Yr 1, CF Yr 2, CF Yr 3 … ) |
|
| Solution inputting the numbers into the Excel NPV formula in the exact order prescribed by the formula (click on Cell B32 and/or B34 to see the contents of the cell as inputted): |
|
|
| NPV = | $12,941.93 | This approach uses the problem's numbers inputted into the formula. |
|
| NPV = | $12,941.93 | This approach uses the appropriate cells inputted into the formula. |
| | Either approach is acceptable. |
|
| Is the above project desirable? Yes, since the project's NPV is greater than $0; the project is earning $12,941.93 more than its cost of capital. |
|
|
|
| Problem 2 |
|
| What is the NPV statistic for the following indicated cash flows assuming the appropriate cost of capital is 11%? |
|
| Time: | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 |
| Cash Flow: | -12,000 | 2,000 | 3,000 | 4,000 | 5,000 |
|
| NPV = | ($1,544.91) | This approach uses the problem's numbers inputted into the formula. |
|
| NPV = | ($1,544.91) | This approach uses the appropriate cells inputted into the formula. |
| | Either approach is acceptable. |
|
| Is the above project desirable? No, since the project's NPV is less than $0; the project is earning $1,544.91 less than the cost of capital. |
|
|
|
|
| Internal Rate of Return (IRR) Calculation |
|
| Solving for Internal Rate of Return (IRR) Problem 1: |
|
| What is the internal rate of return for the following indicated cash flows? |
|
| Time: | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| Cash Flow: | -100,000 | 25,000 | 35,000 | 45,000 | 35,000 | 25,000 |
|
| Solution: Using the Excel, Formulas, Financial, IRR formula that reads as follows: |
|
| IRR = (Range of initial cash outflow and subsequent cash inflow values by year beginning at CF0) |
|
| Solution inputting the numbers into the Excel IRR formula in the exact order prescribed by the formula (click on Cell B75 and to see the contents of the cell as inputted): |
|
|
| IRR = | 19% | You need to adjust the B75 cell to allow showing additional decimals (at least 4 decimal places) as reflected in cell B76. |
| IRR = | 19.16% |
|
| If we are now advised the firm's cost of capital is 14%, is the above project desirable? Yes, the project is desirable since the project's IRR of 19.16% is greater than the project's cost of capital of 14%. Notice you do not use the cost of capital to calculate IRR, however, you do compare the calculated IRR to the cost of capital that serves as the benchmark. |
|
|
|
|
|
| Problem 2 |
|
| What is the internal rate of return (IRR) for the following cash flows? |
|
| Time: | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 |
| Cash Flow: | -12,000 | 2,000 | 3,000 | 4,000 | 5,000 |
|
| IRR = | 6% | You need to adjust the B91 cell to allow showing additional decimals (at least 4 decimal places) as reflected in cell B92. |
| IRR = | 5.61% |
|
| If we are now advised that the firm's cost of capital is 11%, is the above project desirable? No, the project is not desirable since the project's IRR of 5.61% does not exceed the project's cost of capital of 11%. Notice you do not use the cost of capital to calculate IRR, however, you do compare the calculated IRR to the cost of capital that serves as the benchmark. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
