The statement of cash flows on a financial statement sheds a lot of light on the movement of cash in and out of a company and shows how well a company manages its cash position. Free cash flow (FCF) is one of the more important measures derived from a cash flow statement.
Free cash flow is used in fundamental analysis to measure the amount of cash a company generates, after accounting for its capital expenditures. To calculate a company's FCF, one would refer to its balance sheet and subtract its capital expenditures from its total cash flow from operating activities.
Microsoft Excel is a comprehensive and easy-to-use tool for calculating different formulas and any other computational work in general. Below, see how to calculate free cash flow in Excel as well as how to run comparisons with other companies.
Key Takeaways
- Free cash flow (FCF) is a measure of how much cash a company has generated, minus its capital expenditures.
- The metric is an important one in fundamental analysis, demonstrating how well or poorly a company is managing its cash position.
- To calculate FCF, read the company's balance sheet and pull out the numbers for capital expenditures and total cash flow from operating activities, then subtract the first data point from the second.
- This can be calculated by hand or by using Microsoft Excel, as in the example included in the story.
Calculating Free Cash Flow in Excel
For example, according to its cash flow statement for fiscal year-end Sept. 26, 2020, Apple Incorporated reported a total cash flow from operating activities of $80.67 billion. Apple reported capital expenditures of $7.31 billion for the same period.
Apple's competitor, Alphabet Incorporated, reported total cash flow from operating activities of $65.12 billion and capital expenditures of $22.28 billion for the period ending Dec. 31, 2020.
To compare the FCFs between Apple and Alphabet in Excel, enter the words "Apple Incorporated" in cell B1 and "Alphabet Incorporated" in cell C1.
Next, enter the date "Sept. 26, 2020" into cell B2. Enter "Total Cash Flow From Operating Activities" into cell A3, "Capital Expenditures" into cell A4, and "Free Cash Flow" into cell A5. Then, enter "=80670000000" into cell B3 and "=7310000000" into cell B4.
To calculate Apple's FCF, enter the formula "=B3-B4" into cell B5. The resulting FCF of Apple is $73.36 billion.
Now, enter the date "Dec. 31, 2020" into cell C2. Enter "=65120000000" into cell C3 and "=22280000000" into cell C4. Next, enter the formula "=C3-C4" into cell C5. The result demonstrates that Alphabet's FCF is $42.84 billion.
In this instance, you would now be provided with both companies' free cash flow numbers, Apple's in cell B5, of $73.36 billion, and Alphabet's in cell C5, of $42.84 billion. One can also enter the numbers into each cell differently. For example, in cell B3, one wouldn't have to enter all the numerals to get a billion-dollar value. Instead, one could simply enter "=80.67," and in a separate cell, clarify that the numbers are in billions. For example, one can note "numbers in billions."
The Bottom Line
Microsoft Excel is an essential tool when working with numbers, as it provides automatic calculations and a simple layout for mathematical work. The formula for free cash flow is a basic one, as it requires only two numbers, both of which can be found easily on a company's financial statement. Once free cash flow is obtained, it helps understand how much available cash a company has for various uses, such as paying dividends or making investments that contribute to the health and growth of the company.
FAQs
Open Excel. Enter "Total Cash Flow From Operating Activities" into cell A3, "Capital Expenditures" into cell A4, and "Free Cash Flow" into cell A5. Then, enter "=80670000000" into cell B3 and "=7310000000" into cell B4. To calculate FCF, enter the formula "=B3-B4" into cell B5.
What is the formula for calculating free cash flow? ›
What is the Free Cash Flow (FCF) Formula? The generic Free Cash Flow (FCF) Formula is equal to Cash from Operations minus Capital Expenditures. FCF represents the amount of cash generated by a business, after accounting for reinvestment in non-current capital assets by the company.
What is the NPV formula in Excel? ›
The NPV Function[1] is an Excel Financial function that will calculate the Net Present Value (NPV) for a series of cash flows and a given discount rate. It is important to understand the Time Value of Money, which is a foundational building block of various Financial Valuation methods.
What is the formula for calculating cash flow? ›
Important cash flow formulas to know about:
Free Cash Flow = Net income + Depreciation/Amortization – Change in Working Capital – Capital Expenditure. Operating Cash Flow = Operating Income + Depreciation – Taxes + Change in Working Capital.
How to calculate FCF in Excel? ›
Calculating Free Cash Flow in Excel
Enter "Total Cash Flow From Operating Activities" into cell A3, "Capital Expenditures" into cell A4, and "Free Cash Flow" into cell A5. Then, enter "=80670000000" into cell B3 and "=7310000000" into cell B4. To calculate Apple's FCF, enter the formula "=B3-B4" into cell B5.
How do you calculate free cash flow from NPV? ›
What is the formula for net present value?
- NPV = Cash flow / (1 + i)^t – initial investment.
- NPV = Today's value of the expected cash flows − Today's value of invested cash.
- ROI = (Total benefits – total costs) / total costs.
There are five: NPV function, XNPV function, IRR function, XIRR function, and MIRR function. Which one you choose depends on the financial method that you prefer, whether cash flows occur at regular intervals, and whether the cash flows are periodic. Note: Cash flows are specified as negative, positive, or zero values.
How to calculate present value of free cash flow in Excel? ›
Present value (PV) is the current value of a stream of cash flows. PV analysis is used to value a range of assets from stocks and bonds to real estate and annuities. PV can be calculated in Excel with the formula =PV(rate, nper, pmt, [fv], [type]).
Does Excel have a cash flow template? ›
Free Excel Cash Flow Template
Download Xlteq's free Cash Flow Template to assist with managing and reporting for your business. This free cash flow template shows you how to calculate cash flow using a simple cash flow statement. Our cash flow template helps measure your company's financial performance.
How to calculate present value of cash flows? ›
PV = C / (1 + r) n
- C = Future cash flow.
- r = Discount rate.
- n = Number of periods.
To calculate the DCF in Excel, follow these steps:
- Step 1: Organize Your Data. ...
- Step 2: Calculate Present Value for Each Cash Flow. ...
- =CashFlow / (1 + DiscountRate)^Year. ...
- =B2 / (1 + $F$2)^A2. ...
- Step 3: Calculate the Present Value of Terminal Value. ...
- =TerminalValue / (1 + DiscountRate)^LastYear. ...
- Step 4: Sum the Present Values.
Net present value (NPV) is used to calculate the current value of a future stream of payments from a company, project, or investment. To calculate NPV, you need to estimate the timing and amount of future cash flows and pick a discount rate equal to the minimum acceptable rate of return.
How do I calculate free cash flow? ›
The simplest way to calculate free cash flow is by finding capital expenditures on the cash flow statement and subtracting it from the operating cash flow found in the cash flow statement.
What is the formula for monthly cash flow? ›
All types of cash flow formulas explained
| Monthly cash flow balance | = Monthly inflows - Monthly outflows |
|---|
| Operating cash flow | = Net income + depreciation and amortisation + accounts receivables + inventory + accounts payables |
| Investing cash flow | = Incoming investment cash flows - outgoing investment cash flows |
4 more rowsOct 4, 2022
The purpose of a cash flow statement is to provide a detailed picture of what happened to a business's cash during a specified period, known as the accounting period. It demonstrates an organization's ability to operate in the short and long term, based on how much cash is flowing into and out of the business.
What is the formula for free cash flow using EBIT? ›
FCFF = EBIT(1 – Tax rate) + Dep – FCInv – WCInv. FCFF = EBITDA(1 – Tax rate) + Dep(Tax rate) – FCInv – WCInv. FCFE can then be found by using FCFE = FCFF – Int(1 – Tax rate) + Net borrowing. Finding CFO, FCFF, and FCFE may require careful interpretation of corporate financial statements.
What is the formula for the FCFE? ›
FCFE is calculated as Net Income + Depreciation and Amortization (D&A) – Change in Net Working Capital – Capital Expenditures (Capex) + Net Borrowing.
What is the formula for levered FCF? ›
Levered Free Cash Flow Definition: Levered Free Cash Flow (LFCF), also known as Free Cash Flow to Equity (FCFE), equals a company's Net Income to Common + Depreciation & Amortization +/- Deferred Taxes +/- Change in Working Capital – Capital Expenditures +/- Net Debt Borrowings.
What is the formula for FCF conversion? ›
Free Cash Flow (FCF) = Cash from Operations (CFO) – Capital Expenditures (Capex) EBITDA = Operating Income (EBIT) + D&A.
