Chapter 12 – Strategy and the Analysis of Capital Investments
12-48 Basic Capital-Budgeting Techniques; Uneven Net Cash Inflows with Taxes and MACRS; Spreadsheet
Application (60-75 minutes)
1. Unadjusted Payback Period: as shown by the above schedule, the payback period is between 5 and 6 years. Under
the assumption that the cash inflows occur evenly throughout the year, and using a linear interpolation, we
estimate the payback period as:
12-53
Chapter 12 – Strategy and the Analysis of Capital Investments
12-48 (Continued-1)
12-54
Education.
2. Accounting (book) rate of return (ARR):
As indicated above, the average increase in after-tax operating income over the
3. NPV: using Excel-generated PV factors (As shown in Table 1, the present value
(PV) factor for N periods and rate r per period = 1 ÷ (1 + r)N. NPV = $201,784, as
shown below:
Chapter 12 – Strategy and the Analysis of Capital Investments
Note: The above NPV amount is the same as yielded by the application of the
built-in NPV function (see above screen shot).
12-48 (Continued-2)
4. Present value payback period: as indicated in the above schedule, the present
value payback period is “6-plus” (i.e., 6 + $76,479 ÷ $112,253 = 6.68) years;
5. Internal rate of return (IRR): as indicated in the above screen shot, we can use
the built-in function in Excel to estimate the IRR for this proposed investment;
thus, IRR = 15.91%
Alternatively, we can estimate the IRR as follows. We are looking for an
Chapter 12 – Strategy and the Analysis of Capital Investments
PV of net cash inflows at 14% (i.e., an interest rate too low) = $707,133
PV of net cash inflows at 16% (i.e., an interest rate too high) = $647,530
Difference in PV with 2% difference in discount rate = $ 42,419
Therefore,
12-48 (Continued-3)
Interpolation:
IRR = 14% + [($707,133 – $650,000) ÷ $59,603] × (16% – 14%)
= 14% + (0.9586 × 2%) = 15.92%
Alternatively, we could use the built-in function in Excel to estimate the IRR, as
follows:
6. Modified internal rate of return (MIRR) = 13.01%:
12-57
Chapter 12 – Strategy and the Analysis of Capital Investments
12-49 Basic Capital-Budgeting Techniques, Uneven Net Cash Inflows, with Taxes and
MACRS; Spreadsheet Application (45-60 minutes)
1. Payback period: as shown by the schedule below, and under the assumption that
2. Accounting (book) rate of return (ARR):
Average after-tax operating income/year: $798,750 ÷ 10 = $79,875
Accounting (book) rate of return (ARR):
a. On initial investment: $79,875 ÷ $650,000 = 12.29%
Chapter 12 – Strategy and the Analysis of Capital Investments
12-59
Education.
Chapter 12 – Strategy and the Analysis of Capital Investments
12-49 (Continued-1)
3. Net Present Value (NPV): the NPV of the proposed investment is $227,590 (based
on exact PV factors found using the following formula: factori = 1 ÷ (1+0.10)i, i =
1,10:
Chapter 12 – Strategy and the Analysis of Capital Investments
12-49 (Continued-2)
The estimated NPV for the proposed investment can also be found using the built-in
NPV function in Excel, as follows:
Assuming that the original investment outlay ($650,000) is contained in cell M15 and
that the discount rate is contained in cell M17, we have:
4. Internal Rate of Return (IRR): given the schedule below, we can use the built-in IRR
function to estimate the IRR for the proposed investment: 17.08%.
Formula (in the cell of your choice): =IRR(O33:O43,0.12)
12-61
Chapter 12 – Strategy and the Analysis of Capital Investments
12-49 (Continued-3)
Alternatively, we can estimate the IRR as follows. We are looking for an
interest/discount rate, r, that produces a NPV = $0 (i.e., a present value of cash
inflows equal in amount to the original investment outlay, $650,000). We choose, by
trial and error, two sample interest rates and calculate the NPV under each. Below,
we chose 15% and 20%. Thus,
Note that had we chosen rates closer to 17% (e.g., 16% and 17%) the interpolation
would have yielded a more accurate estimate of the true IRR of 17.08%.
12-62
Education.