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 |
