In: Economics
An asset in the five-years MACRS property class costs $150,000 and has a zero estimated salvage value after six years of use. The asset will generate annual revenues of $320,000 and will require $80,000 in annual labor costs and $50,000 in annual material expenses. Assume a tax rate of 21%. Compute the after-tax cash flows over the project life and the NPW for a MARR = 12%. Is the investment acceptable?
MARR = 12%
Tax rate = 21%
Depreciation = Purchase value * Depreciation rate
Taxable income = Net cash flow - Depreciation
Tax = Tax rate * Taxable income
ATCF = Taxable income - Tax + Depreciation
Using Excel
Year | Initial cost | Revenue | O&M cost | Depreciation | Depreciation recapture | TI | Tax | ATCF |
0 | -150000.00 | -150000 | ||||||
1 | 320000.00 | -130000.00 | 30000.00 | 160000.00 | 33600.00 | 156400 | ||
2 | 320000.00 | -130000.00 | 48000.00 | 142000.00 | 29820.00 | 160180 | ||
3 | 320000.00 | -130000.00 | 28800.00 | 161200.00 | 33852.00 | 156148 | ||
4 | 320000.00 | -130000.00 | 17280.00 | 172720.00 | 36271.20 | 153729 | ||
5 | 320000.00 | -130000.00 | 17280.00 | 172720.00 | 36271.20 | 153729 | ||
6 | 320000.00 | -130000.00 | 8640.00 | 0.00 | 181360.00 | 38085.60 | 151914 | |
NPW | 490372 |
NPW = 490372
As NPW is positive, project should be selected
Showing formula in excel
Year | Initial cost | Revenue | O&M cost | Depreciation | Depreciation recapture | TI | Tax | ATCF |
0 | -150000 | =B10 | ||||||
1 | 320000 | =-80000-50000 | =150000*0.2 | =C11+D11-E11+F11 | =G11*0.21 | =C11+D11-H11+F11 | ||
2 | 320000 | =-80000-50000 | =150000*0.32 | =C12+D12-E12+F12 | =G12*0.21 | =C12+D12-H12+F12 | ||
3 | 320000 | =-80000-50000 | =150000*0.192 | =C13+D13-E13+F13 | =G13*0.21 | =C13+D13-H13+F13 | ||
4 | 320000 | =-80000-50000 | =150000*0.1152 | =C14+D14-E14+F14 | =G14*0.21 | =C14+D14-H14+F14 | ||
5 | 320000 | =-80000-50000 | =150000*0.1152 | =C15+D15-E15+F15 | =G15*0.21 | =C15+D15-H15+F15 | ||
6 | 320000 | =-80000-50000 | =150000*0.0576 | 0 | =C16+D16-E16+F16 | =G16*0.21 | =C16+D16-H16+F16 | |
NPW | =NPV(12%,I11:I16)+I10 |