Docstoc

tax_shield_model

Document Sample
tax_shield_model Powered By Docstoc
					                                                      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