Question 12.8: A capital expenditure of $246,000 is made for equipment (CCA...
A capital expenditure of \$246,000 is made for equipment (CCA Class 8, 20\% rate). The investment is expected to generate the following before-tax cash flows over the next 10 years.
|
Year |
Before-Tax Cash
Flow Amount |
| 1 | \$30,000 |
| 2 | 40,000 |
| 3 | 50,000 |
| 4 | 60,000 |
| 5 | 70,000 |
| 6 | 80,000 |
| 7 | 90,000 |
| 8 | 100,000 |
| 9 | 110,000 |
| 10 | 120,000 |
At the end of 10 years the equipment is sold for \$20,000. The marginal tax rate is 35\%, and the MARR = 12\%. Find the present worth of the investment.
This problem can be solved in two ways: first, by using the capital tax factors and assuming that the account books stay open; second, by using a spreadsheet program. The advantages and shortcomings of each method will be discussed after the example.
Solution Using CTFs
The before-tax cash flow diagram (values in thousands) is
To convert from a before-tax situation to an after-tax situation, Formula 12-2 and the capital tax factor derivation show us that it is necessary only to
• multiply the cost and revenues (before-tax cash flows) by (1 – t).
• multiply the depreciable capital investment amounts by CTF.
• multiply the proceeds from disposal of capital assets (cash salvage values) by CSF.
Therefore, the corresponding after-tax cash flow diagram is
And the calculations are as follows:
t = 35\%
d = 20\%
i= 12\%
=\left[ 1-\left( \frac{0.3×0.20}{0.12+0.20} \right)\left( \frac{1+0.12/2 }{1+0.12} \right) \right]
=0.7930
GSF=\left[ 1-\left( \frac{td}{i+d} \right)\right]
=\left[ 1-\left( \frac{0.35×0.20}{0.12+0.20} \right)\right]
= 0.7813
PW = -\$246K (CTF) + \$35K (1 – t)(P/A, i , n) + \$10K (1 – t)
(P/G, i, n) + \$20K (CSF) (P/F, i, n)
= -246 × 0.7930 + 35 × (1 – 0.35) × (P/A, 12\%, 10) + 10 × (10.35)
× (P/G, 12\%, 10) + 20 × 0.7813 × (P/F, 12\%, 10)
= -195,070 + 128,538 + 131,651 + 5,032
= +\$70,151 \cong \$70,000
Solution in a Spreadsheet
To analyze this in a spreadsheet it is convenient to assume that the account books are closed and that a net (after-tax) salvage value can be used. This can be calculated explicitly, as follows:
| Data | |
| n= | 10 |
| MARR=i= | 12\% |
| A= | \$ 35,000 |
| DTE = | \$ 10,000 |
| Equipment P = | \$ 246,000 |
| S= | \$20,000 |
| d= | 20\% |
| t= | 35\% |
| Calculation of Net Salvage | |
| UCC at Year 10 = | \$29,716 |
| Proceeds S = | 20,000 |
| Loss on disposal = | 9,716 |
| Tax effect DTE = | 3,401 |
| Net salvage = DTE + S = | \$23,401 |
The spreadsheet tabular format takes the relationships from the corporate cash flow pipeline of Figure 12-2.
| End of Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| BTCF | \$35,000 | \$45,000 | \$55,000 | \$65,000 | \$75,000 | \$85,000 | \$95,000 | \$105,000 | \$115,000 | \$125,000 | |
| – CCA | 24,600 | 44,280 | 35,424 | 28,339 | 22,671 | 18,137 | 14,510 | 11,608 | 9,286 | 7,429 | |
| = Taxable income | 10,400 | 720 | 19,576 | 36,661 | 52,329 | 66,863 | 80,490 | 93,392 | 105,714 | 117,571 | |
| – Income tax | 3,640 | 252 | 6,852 | 12,831 | 18,315 | 23,402 | 28,172 | 32,687 | 37,000 | 41,150 | |
| = Net profit | 6,760 | 468 | 12,724 | 23,830 | 34,014 | 43,461 | 52,319 | 60,705 | 68,714 | 76,421 | |
| + CCA | 24,600 | 44,280 | 35,424 | 28,339 | 22,671 | 18,137 | 14,510 | 11,608 | 9,286 | 7,429 | |
| = ATCF from | |||||||||||
| operations | 31,360 | 44,748 | 48,148 | 52,169 | 56,685 | 61,598 | 66,828 | 72,313 | 78,000 | 83,850 | |
| Cap investment | \$246,000 | ||||||||||
| + Net salvage | 23,401 | ||||||||||
| = Net ATCF | (246,000) | 31,360 | 44,748 | 48,148 | 52,169 | 56,685 | 61,598 | 66,828 | 72,313 | 78,000 | 107,251 |
| Using the Excel NPV function:
Net ATCF present worth = \$70,565 |
The difference between the two answers (\$70,565 – 70,151 = \$414) is due to the different assumptions books open or books closed. For most engineering economy studies that involve long time periods and small salvage values, the difference is not significant.
Both the spreadsheet tabular calculation and the tax factor method have advantages. The tax factors are useful when a quick feasibility check is desired and the estimates are based on either arithmetic or geometric series. But if there are discontinuous cash flows, such as a major revenue or cost item in a particular year, or a situation when it is necessary to monitor cash and working capital requirements carefully throughout the project, then the spreadsheet provides a more complete picture. The spreadsheet method is also extremely useful when one is doing a “What-if’ analysis or experimenting with different methods of financing.
