Using Spreadsheets for Inflation Calculations 463SOlUT:ION'The data for labor costs can be stated so that no inflation needs to be applied in Year 1: the cost is
$350,000. In contrast, the transportation costs for Year 1 are determined by multiplying $40,000
by 1.05 (= 1 +f).
Also in later years the laborcostt=laborcoStt-l(1 + f), while each transportation cost
must be computed as the time-Ovalue times (1 + f)t. In Figure 14-6, the numbers in the Year 0
(or real) dollar column equal the values in the actual dollars column divided by (I,03.Y.FIGURE 14-6 Spreadsheetforinflation.The equivalent annual cost equals $168,791.
For the data of Example 14-9, calculate the IRR with and without inflation with MACRS depre-
ciation. How are the results affected by inflation by comparison with the earlier results.SOlUTlQN
.=
Most of the formulas for this spreadsheet are given in rows 11 and 12 for the data in Year 6. The
benefits received are computed from the base value in cell B5. The depreciation is the MACRS
percentage times the $120,000 spent in Year O.This value is not influenced by inflation, so the
depreciation deduction is less valuable as inflation increases. The tax paid equals the tax rate
times the taxable income, which equals dollars received minus the depreciation charge. Then
ATCF (after-tax cash flow) equals the before-tax cash flow.minusthe tax paid.
In~Fig~ 1<t.-7~ifbfi'Ce="thllfmYeaf2"thcde-prd!iatidniicharge is~nu.ge~nobgfito cause'-='thfs
project to pe.y"negative" tax. for a firm, this meaI)sthat the deduction on this project will be used
to offset inc.omefrom other projects.
L.... --
t
I
.1.II!
~r
\
~:.. .-..-...
A B C D E F G H
1
- ., ,- .--.- -.
--7%InflationFreeInt&esr
2 Inflation Rate 6% 5% 3%
3 '. ,
. ., ."'--........-.-.... ..:.. "...;- -;..,.'. .-'J-'. - ,_. ", ''''.''
Total...
- ., ,- .--.- -.
Transportation Costs Total
4 Year ..Labor Costs Year 0 $s Actual $8 Actual $8 Real $s
5 1
..
120',000-40,000-. "42:000---162,000."'157,282--= Es/(l+$F$2)"A5
(^62) 127,200 60,000 66,150 193,350 182,251
(^73) 134,832 50;000 57,881 192,713 176,360
(^84) ,142,922 30,000 36,465 179,387 159,383
(^9) / / I , $571,732= NPV(Fl,F5:F8)
(^10) / / =B8+D8 $168,791 ==.",.PMT(Fl,4,F9)
(^11) =B7(1 +$B$2) =C8(1 +$D$2)"A8