Cash Flow Excel Formulas Added

Reviews
Shared by: Yvonne Wang
Categories
Tags
Stats
views:
138
rating:
not rated
reviews:
0
posted:
5/23/2008
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

Shared by: Yvonne Wang
Other docs by Yvonne Wang
Planning Analyst Job Description
Views: 198  |  Downloads: 2
Six Sigma Template
Views: 564  |  Downloads: 64
Related docs
Excel
Views: 331  |  Downloads: 25
Excel Formulas Help
Views: 28299  |  Downloads: 3158
Formulas Interest
Views: 96  |  Downloads: 6
Cash Flow Tracker Spreadheet
Views: 369  |  Downloads: 0
Cash Flow Statement Excel Template
Views: 3188  |  Downloads: 315
Discounted Cash Flow Model with Guide
Views: 54  |  Downloads: 0
Cash Flow Notes
Views: 0  |  Downloads: 0
cash flow[1]
Views: 174  |  Downloads: 13
Cash Flow Matrix
Views: 176  |  Downloads: 12
Cash Flow Sensitivity Analysis
Views: 663  |  Downloads: 77
Cash Flow Manager
Views: 188  |  Downloads: 0