How to Calculate Free Cash Flow Valuation With Excelby David Ingram
Free cash flow (FCF) is a measure of the cash available to make interest payments to potential creditors and equity investors, and is used in valuating a company before making investment or lending decisions. Knowing how to calculate free cash flow valuation with Excel can give investors and lenders a competitive edge when making decisions. Much of the information required for a free cash flow valuation can be found on a company's income statement and statement of cash flows.
Free Cash Flow
Type "New Debt Financing" in cell A3 and enter the corresponding figure for the period in cell B3. Type "Debt Repayments" in cell A4 and enter the total debt repayments for the period in cell B4.
Enter "Net Borrowing" in cell A5. Enter the formula "=B3-B4" in cell B5 to calculate your net borrowing figure.
Type "Cash Flow From Operations" in cell A6 then enter the corresponding figure from your statement of cash flows in cell B6. Type "Capital Expenditures" in cell A7 and enter the corresponding figure from your income statement in cell B7.
Enter "Free Cash Flow to Equity" in cell A8. Enter the formula "=B6-B7+B5" in cell B8 to arrive at your first free cash flow figure. Free cash flow to equity is a measure of FCF from the standpoint of creditors and shareholders.
Skip a row. Type "Interest Expense" in cell A10 and enter the appropriate figure in cell B10. Type "Tax Rate" in cell A11 and enter the income tax rate for the period in cell B11. Consult your prior year tax return to locate your income tax rate.
Type "Free Cash Flow to the Firm" in cell A12. Enter the formula "=B6+(B10*(1-B11))-B7" in cell B12 to calculate your second free cash flow figure. Free cash flow to the firm is a measure of FCF from the standpoint of the company being valued.
Skip a row. Type "Cost of Capital" in cell A14, and enter the average interest rate of all company debt, or the expected interest rate of future debt, in cell B14.
Type "Estimated Growth Rate" in cell A15 and enter the appropriate figure for the period, in terms of gross revenue, in cell B15.
Skip a row. Type "FCF Valuation" in cell A17 and enter the formula "=B8/(B14-B15)" in cell B17 to arrive at your free cash flow valuation figure.
- cash image by Alexey Klementiev from Fotolia.com