# tax_shield_model

Document Sample

```					                                                      091fbe5c-19e2-4fe3-8a0e-91db1c79c675.xls

TAX SHIELD CALCULATION
a. Uses circular references to calculate PV of after-tax cash flows
b. Note that after-tax discount rate is not exactly pre-tax rate times (1 - tax rate)
c. Model needs a starting value (270 initially "seeded" into cell B19)
d. Set calculation to manual, enable iteration, and press F9 approx. 10 times until model converges
(in Excel, need to copy B19, then Paste Special Values to itself, then F9)

FORMULAS

Year                              1           2          3           4           5

Pre-tax Income                    60.00       90.00     110.00      135.00      150.00
Tax shield                        54.00       54.00      54.00       54.00       54.00 <== straight-line depreciation
Pre-tax Income (adj'd)             6.00       36.00      56.00       81.00       96.00
Tax rate                           46%
Net Income                         3.24       19.44      30.24       43.74       51.84
Net Cash Flow                     57.24       73.44      84.24       97.74      105.84
Discount rate                      15%
PV of Net Income               \$270.00    \$269.20     <== used only to make copying formulas & values to B19 easy

Total NPV of Income (Pre-tax)
20%                \$301.54
24%                \$272.88      <== Therefore discount rate of 15% after-tax is equivalent to tax rate
25%                \$266.37          of between 24% and 25% pre-tax.

Note:
(1-0.46) * .24 =               12.96%     <== But, simpler calculation has has 24% - 25% pre-tax
(1-.046) * .25 =               13.50%         equivalent to 13% after-tax.

Yale M. Braunstein                                                      Page 1                                                          8/18/2012

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 8 posted: 8/18/2012 language: pages: 1