Exercise 12-35: After-Tax Net Present Value and IRR (non-MACRS rules)
Background
Data
Purchase price and installation cost of new computer system
$
60,000
Cost to operate each year
$
30,000
Estimated useful life in years
4
Expected cost reduction per year
$
62,000
Cost of capital (discount rate)
10%
Income tax bracket
30%
Required
Solution
Part 1
a)
$
32,000
$
101,440
NPV
$
41,440
b)
Net cash inflow before depreciation
$
32,000
15,000
Increase in net income before tax
$
17,000
Income tax rate
30%
Income tax
$
5,100
$
26,900
$
85,273
$
25,273
(Note: PV annuity factor obtained from Appendix C, Table 2)
c)
Straight-line depreciation rate
25%
Double-declining-balance rate
50%
Beginning
Book
Depreciation
Accumulated
Ending
Year
Value
Expense
Depreciation
Book Value
0
$60,000
1
$
60,000
$
30,000
$
30,000
$30,000
2
$
30,000
$
15,000
$
45,000
$15,000
3
$
15,000
$
7,500
$
52,500
$7,500
4
$
7,500
$
7,500
$
60,000
$0
Net
30%
After-tax
10%
Cash
Depreciation
Taxable
Income
Net Cash
Discount
Present
Year
Inflow
Expense
Income
Taxes
Inflow
Factor*
Value
0
$
(60,000)
$
(60,000)
1.000
($60,000)
1
32,000
$
30,000
$
2,000
$
600.00
31,400
0.909
$28,543
2
32,000
15,000
17,000
5,100
26,900
0.826
$22,219
3
32,000
7,500
24,500
7,350
24,650
0.751
$18,512
4
32,000
7,500
24,500
7,350
24,650
0.683
$16,836
$26,110
Part 2
Net cash inflow each year ($62,000
−
$30,000)
Present value of net cash inflows ($32,000
×
3.170*)
(
Note
: PV annuity factor obtained from Appendix C, Table 2)
Depreciation expense ($6,000
÷
4 years)
Net after-tax cash inflow per year ($32,000
−
$5,100)
Present value of net cash inflows ($26,900
×
3.170*)
NPV ($85,273
−
$60,000)
(
Note:
PV factors obtained from Appendix C, Table 1)
a) Net cash inflow each year = $32,000 ($62,000
−
$30,000)
$60,000 = $32,000 x A
?, 4

** Subscribe** to view the full document.

Using the built-in IRR function in Excel:
Formula
Estimated IRR =
39.08%
=IRR(F62:F66,0.1)
b) After-tax cash flows:
Year
Amount
0
$
(60,000)
1
$
26,900
2
$
26,900
3
$
26,900
4
$
26,900
Using the Built-in Function in Excel:
Estimated IRR =
28.27%
=IRR(F80:F84,0.1)
We can also use the annuity tables in the text (Appendix C), and interpolation, to estimate the project's IRR,
as follows:
Discount
Discount
Rate
Factor
25%
25%
2.362
2.362
?
2.230
30%
2.166
Difference
5%
?
0.196
0.132
Internal Rate of Return
28.37%
A
?, 4
= 1.875, which has a rate of return greater than 30%.
By inspection of the annuity factors in Appendix C, Table 2, we see that:
$60,000 = $26,900 x A
?, 4
A
?, 4
= 2.230, which corresponds to adiscount rate somewhere between 25% and 30%

Exercise 12-36: Using Arrays in Excel to Calculate Net Present V
Background
Please refer to footnote #12 in Chapter 12 and the data referenced in this footn
Data--Part 1
WACC
10.00%
After-tax cash flows:
Year
CF
0
($680,000)
1
$178,000
Alternative Formulas (hit:
2
$208,000
$29,240 =SUM((CF)/((1+
3
$208,000
$29,240 =SUM((CF)/((1+
4
$321,000
Formula (remember: hit ctl+shift+enter)
NPV =
$29,240 =SUM((B12:B16)/((1+$B$9)^Y))
Data--Part 2
WACC
10.00%
After-tax cash flows:
Yr
CashFlows
0
($1,000)
1
$100
2
$200
3
$300
4
$400
5
$500
6
$600
Alternative Formulas (hit:
7
$700
$1,904
=SUM((CashFlo
8
$800
$1,904
=SUM((CashFlo
9
$900
10
$1,000
Formula (hit: ctl+shift+enter)
NPV =
$1,904
=SUM((B25:B35)/((1+$B$22)^Yr))

** Subscribe** to view the full document.

Required
1. Follow the directions given in footnote #12 to calculate the NPV of the refere
specified help file (Microsoft website) if need be.
First, define variable names (go to "Formulas," then "Define Names"). For e
through A16 as "Year," and cells B12 through B16 as "CF."
Next, enter into an open cell (e.g., B18) the following formula to calculate the
=SUM(CF/(1+WACC)^Year)
Finally, rather than hitting "enter," you now hit the following (to enter the arra
now display the correct amount, $29,240 (rounded).

- Spring '18