professional documents
home
Upload
docsters
Upload
Excel Spreadsheet

Financial Excel Spreadsheets center doc

 


FREE CASH FLOW VS. CASH FLOWS TO SHAREHOLDERS DATA Cost of debt 10% EBIT 6.00 Tax rate 40% Investment 12.00 Cost of equity 12% Amount borrowed 4.00 CASH FLOWS TO SHAREHOLDERS Year 0 1 2 3 Goes on forever EBIT 6.00 6.00 6.00 Less interest (0.40) (0.40) (0.40) Earnings before taxes 5.60 5.60 5.60 Taxes (2.24) (2.24) (2.24) Net income 3.36 3.36 3.36 Investment by shareholders (8.00) ---Net cash flow to shareholders (8.00) 3.36 3.36 3.36 NPV at cost of equity 20.00 Market value of equity 28.00 WEIGHTED AVERAGE COST OF CAPITAL Proportion Weighted Cost of of Market Cost of Capital Value Capital Debt (after-tax) 6.0% 12.5% 0.8% Equity 12.0% 87.5% 10.5% Weighted average cost of capital 11.3% FREE CASH FLOW Year 0 1 2 3 Goes on forever EBIT 6.00 6.00 6.00 Taxes (2.40) (2.40) (2.40) EBIAT 3.60 3.60 3.60 Investment (12.00) ---Free cash flow (12.00) 3.60 3.60 3.60 NPV at WACC 20.00 AFTER-TAX CASH FLOWS FROM LENDERS Year 0 1 2 3 Goes on forever Interest (0.40) (0.40) (0.40) Interest tax shield 0.16 0.16 0.16 After-tax interest (0.24) (0.24) (0.24) Principal receipt (repayment) 4.00 ---Principal plus after-tax interest 4.00 (0.24) (0.24) (0.24) NPV at after-tax cost of debt -CONCLUSION Cash flows to shareholders (8.00) 3.36 3.36 discount at cost of equity After-tax cash flows to lenders (4.00) 0.24 0.24 discount at after-tax cost of debt Free cash flow (12.00) 3.60 3.60 discount at WACC NPV of cash flows to shareholders = NPV of free cash flows + NPV of after-tax cash flows from lenders Page 1FREE CASH FLOW VS. CASH FLOWS TO SHAREHOLDERS DATA Cost of debt 10% EBIT 6.00 Tax rate 40% Investment 12.00 After-tax cost of debt 6% Amount borrowed 4.00 Cost of equity 12% Subsidized interest rate 5% Market value of debt 2.00 CASH FLOWS TO SHAREHOLDERS Year 0 1 2 3 Goes on forever EBIT 6.00 6.00 6.00 Less interest (0.20) (0.20) (0.20) Earnings before taxes 5.80 5.80 5.80 Taxes (2.32) (2.32) (2.32) Net income 3.48 3.48 3.48 Investment by shareholders (8.00) ---Net cash flow to shareholders (8.00) 3.48 3.48 3.48 NPV at cost of equity 21.00 Market value of equity 29.00 WEIGHTED AVERAGE COST OF CAPITAL Proportion Weighted Cost of of Market Cost of Capital Value Capital Debt (after-tax) 6.0% 6.5% 0.4% Equity 12.0% 93.5% 11.2% Weighted average cost of capital 11.6% FREE CASH FLOW Year 0 1 2 3 Goes on forever EBIT 6.00 6.00 6.00 Taxes (2.40) (2.40) (2.40) EBIAT 3.60 3.60 3.60 Investment (12.00) ---Free cash flow (12.00) 3.60 3.60 3.60 NPV at WACC 19.00 AFTER-TAX CASH FLOWS FROM LENDERS Year 0 1 2 3 Goes on forever Interest (0.20) (0.20) (0.20) Interest tax shield 0.08 0.08 0.08 After-tax interest (0.12) (0.12) (0.12) Principal receipt (repayment) 4.00 ---Principal plus after-tax interest 4.00 (0.12) (0.12) (0.12) NPV at after-tax cost of debt 2.00 CONCLUSION Free cash flow (12.00) 3.60 3.60 discount at WACC After-tax cash flows from lenders 4.00 (0.12) (0.12) discount at after-tax ordinary cost of debt Cash flows to shareholders (8.00) 3.48 3.48 discount at cost of equity NPV of cash flows to shareholders = NPV of free cash flows + NPV of after-tax cash flows from lenders Page 2FREE CASH FLOW VS. CASH FLOWS TO SHAREHOLDERS We seldom encounter perpetuities. This shows how to calculate the PV of after-tax cash flows from bonds when the subsidized loan is a bullet loan (principal repaid only at maturity). DATA Cost of debt 10% Amount borrowed 4.00 Tax rate 40% Subsidized rate 5% After-tax cost of debt 6% AFTER-TAX CASH FLOWS FROM BONDS Year 0 1 2 3 4 Interest (0.20) (0.20) (0.20) (0.20) Interest tax shield 0.08 0.08 0.08 0.08 After-tax interest (0.12) (0.12) (0.12) (0.12) Principal receipt (repayment) 4.00 ---(4.00) Principal plus after-tax interest 4.00 (0.12) (0.12) (0.12) (4.12) PV factor at after-tax cost of debt 100% 94% 89% 84% 79% PV of cash flow 4.00 (0.11) (0.11) (0.10) (3.26) Cumulative PV 4.00 3.89 3.78 3.68 0.42 NPV 0.42 Page 3
flag this doc
823
50
not rated
0
12/27/2007
English
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 887 | 107 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 495 | 40 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 358 | 29 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 517 | 31 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 333 | 27 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 287 | 19 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 287 | 14 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 459 | 47 | 0 |
Preview

Financial Excel Spreadsheets

shanti12 12/27/2007 | 710 | 80 | 0 |
Preview

Introduction to Excel Spreadsheets

EPADocs 5/13/2008 | 231 | 14 | 0 | legal
Preview

Financial_model_1[3]

GrayLadyIce 2/5/2008 | 169 | 18 | 0 | financial
Preview

Bill_Snow_Financial_Model_2004_03_0 9[3]

GrayLadyIce 2/5/2008 | 86 | 0 | 0 | financial
Preview

Financial Projections Model v6.8.4[3]

GrayLadyIce 2/5/2008 | 301 | 59 | 0 | financial
Preview

Financial History and Ratios1[9]

balazon 1/8/2008 | 64 | 3 | 0 | legal
Preview

Download all the tables in Excel spreadsheets

RMA 6/18/2008 | 60 | 6 | 0 | legal
Preview

A Junior Software engineer

shanti12 3/10/2008 | 457 | 52 | 2 | legal
Preview

1st Sem-Operating systems_1

shanti12 1/19/2008 | 466 | 34 | 1 | educational
Preview

Technological Trends

shanti12 1/18/2008 | 460 | 35 | 0 |
Preview

Useful Material

shanti12 1/18/2008 | 832 | 122 | 0 |
Preview

Professional Ajax

shanti12 1/18/2008 | 3752 | 211 | 7 | business
Preview

Pragmatic AJAX

shanti12 1/18/2008 | 853 | 102 | 2 |
Preview

hoomanLibrary

shanti12 1/18/2008 | 287 | 3 | 0 |
Preview

msnet article

shanti12 1/18/2008 | 267 | 1 | 0 |
Preview

AJAX

shanti12 1/18/2008 | 434 | 39 | 0 |
Preview

ajax-tutorial-08

shanti12 1/18/2008 | 960 | 57 | 0 |
 
review this doc