Excel Spreadsheet

Financial Excel Spreadsheets

You must be logged in to download this document
Reviews
Shared by: shanti12
Categories
Tags
Stats
views:
1972
rating:
not rated
reviews:
0
posted:
12/27/2007
language:
English
pages:
0
FREE CASH FLOW VS. CASH FLOWS TO SHAREHOLDERS DATA Cost of debt Tax rate Cost of equity 10% 40% 12% EBIT Investment Amount borrowed 6.00 12.00 4.00 CASH FLOWS TO SHAREHOLDERS Year EBIT Less interest Earnings before taxes Taxes Net income Investment by shareholders Net cash flow to shareholders NPV at cost of equity Market value of equity 0 1 6.00 (0.40) 5.60 (2.24) 3.36 3.36 2 6.00 (0.40) 5.60 (2.24) 3.36 3.36 3 6.00 (0.40) 5.60 (2.24) 3.36 3.36 Goes on forever (8.00) (8.00) 20.00 28.00 WEIGHTED AVERAGE COST OF CAPITAL Proportion of Market Value 12.5% 87.5% Weighted Cost of Capital 0.8% 10.5% 11.3% Cost of Capital Debt (after-tax) Equity Weighted average cost of capital 6.0% 12.0% FREE CASH FLOW Year EBIT Taxes EBIAT Investment Free cash flow NPV at WACC 0 1 6.00 (2.40) 3.60 3.60 2 6.00 (2.40) 3.60 3.60 3 6.00 (2.40) 3.60 3.60 Goes on forever (12.00) (12.00) 20.00 AFTER-TAX CASH FLOWS FROM LENDERS Year Interest Interest tax shield After-tax interest Principal receipt (repayment) Principal plus after-tax interest NPV at after-tax cost of debt 0 1 (0.40) 0.16 (0.24) (0.24) 2 (0.40) 0.16 (0.24) (0.24) 3 (0.40) 0.16 (0.24) (0.24) Goes on forever 4.00 4.00 - CONCLUSION Cash flows to shareholders After-tax cash flows to lenders Free cash flow (8.00) (4.00) (12.00) 3.36 0.24 3.60 3.36 0.24 3.60 discount at cost of equity discount at after-tax cost of debt discount at WACC NPV of cash flows to shareholders = NPV of free cash flows + NPV of after-tax cash flows from lenders Page 1 FREE CASH FLOW VS. CASH FLOWS TO SHAREHOLDERS DATA Cost of debt Tax rate After-tax cost of debt Cost of equity 10% 40% 6% 12% EBIT Investment Amount borrowed Subsidized interest rate Market value of debt 6.00 12.00 4.00 5% 2.00 CASH FLOWS TO SHAREHOLDERS Year EBIT Less interest Earnings before taxes Taxes Net income Investment by shareholders Net cash flow to shareholders NPV at cost of equity Market value of equity 0 1 6.00 (0.20) 5.80 (2.32) 3.48 3.48 2 6.00 (0.20) 5.80 (2.32) 3.48 3.48 3 6.00 (0.20) 5.80 (2.32) 3.48 3.48 Goes on forever (8.00) (8.00) 21.00 29.00 WEIGHTED AVERAGE COST OF CAPITAL Proportion of Market Value 6.5% 93.5% Weighted Cost of Capital 0.4% 11.2% 11.6% Cost of Capital Debt (after-tax) Equity Weighted average cost of capital 6.0% 12.0% FREE CASH FLOW Year EBIT Taxes EBIAT Investment Free cash flow NPV at WACC 0 1 6.00 (2.40) 3.60 3.60 2 6.00 (2.40) 3.60 3.60 3 6.00 (2.40) 3.60 3.60 Goes on forever (12.00) (12.00) 19.00 AFTER-TAX CASH FLOWS FROM LENDERS Year Interest Interest tax shield After-tax interest Principal receipt (repayment) Principal plus after-tax interest NPV at after-tax cost of debt 0 1 (0.20) 0.08 (0.12) (0.12) 2 (0.20) 0.08 (0.12) (0.12) 3 (0.20) 0.08 (0.12) (0.12) Goes on forever 4.00 4.00 2.00 CONCLUSION Free cash flow After-tax cash flows from lenders Cash flows to shareholders (12.00) 4.00 (8.00) 3.60 (0.12) 3.48 3.60 (0.12) 3.48 discount at WACC discount at after-tax ordinary cost of debt 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 2 FREE 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 Tax rate After-tax cost of debt 10% 40% 6% Amount borrowed Subsidized rate 4.00 5% AFTER-TAX CASH FLOWS FROM BONDS Year Interest Interest tax shield After-tax interest Principal receipt (repayment) Principal plus after-tax interest PV factor at after-tax cost of debt PV of cash flow Cumulative PV NPV 0 1 (0.20) 0.08 (0.12) (0.12) 94% (0.11) 3.89 2 (0.20) 0.08 (0.12) (0.12) 89% (0.11) 3.78 3 (0.20) 0.08 (0.12) (0.12) 84% (0.10) 3.68 4 (0.20) 0.08 (0.12) (4.00) (4.12) 79% (3.26) 0.42 4.00 4.00 100% 4.00 4.00 0.42 Page 3

Related docs
Free Financial Spreadsheets
Views: 110  |  Downloads: 4
investment spreadsheets
Views: 189  |  Downloads: 9
Free Spreadsheets On
Views: 112  |  Downloads: 0
Excell Spreadsheets
Views: 141  |  Downloads: 13
Spreadsheets
Views: 33  |  Downloads: 5
Financial Excel Spreadsheets
Views: 546  |  Downloads: 60
Financial Excel Spreadsheets
Views: 659  |  Downloads: 66
Financial Excel Spreadsheets
Views: 461  |  Downloads: 42
Financial Excel Spreadsheets
Views: 673  |  Downloads: 46
Financial Excel Spreadsheets
Views: 1196  |  Downloads: 157
Free Spreadsheets Templates
Views: 97  |  Downloads: 0
Introduction to Excel Spreadsheets
Views: 311  |  Downloads: 22
premium docs
Other docs by shanti12
Globalisation Of Education
Views: 540  |  Downloads: 25
multinational financial management
Views: 410  |  Downloads: 24
Financial Ratios
Views: 2710  |  Downloads: 351
Financial model
Views: 2401  |  Downloads: 677
financial management
Views: 751  |  Downloads: 154
financial Analysis
Views: 1320  |  Downloads: 137
BUSINESS PLANNING AND FINANCIAL FORECASTING
Views: 1524  |  Downloads: 329
A Junior Software engineer
Views: 708  |  Downloads: 60
1st Sem-Operating systems_1
Views: 769  |  Downloads: 41
Useful Material
Views: 1157  |  Downloads: 135
Technological Trends
Views: 662  |  Downloads: 36
Professional Ajax
Views: 7493  |  Downloads: 293
msnet article
Views: 428  |  Downloads: 1
hoomanLibrary
Views: 765  |  Downloads: 3
AJAX
Views: 575  |  Downloads: 46