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
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. There you go.
How to calculate 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 FCF, enter the formula "=B3-B4" into cell B5. There you go.
How do you calculate 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.
Is there a cash flow function in Excel? ›
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.
What is the formula for calculating cash flow? ›
Free Cash Flow = Net income + Depreciation/Amortization – Change in Working Capital – Capital Expenditure. Net Income is the company's profit or loss after all its expenses have been deducted.
How do you calculate present value of Free Cash Flow? ›
Formula to Calculate Present Value (PV) Present value, a concept based on time value of money, states that a sum of money today is worth much more than the same sum of money in the future and is calculated by dividing the future cash flow by one plus the discount rate raised to the number of periods.
How to calculate operating cash flow? ›
How to calculate the operating cash flow formula
- Operating cash flow = total cash received for sales - cash paid for operating expenses.
- OCF = (revenue - operating expenses) + depreciation - income taxes - change in working capital.
- OCF = net income + depreciation - change in working capital.
You figure free cash flow by subtracting money spent for capital expenditures, which is money to purchase or improve assets, and money paid out in dividends from net cash provided by operating activities.
How do you calculate free cash flow from Pat? ›
PAT + Depreciation + Amortization + Deferred Taxes + Interest charges – Change in working capital – change in fixed asset investments. The above equation is the free cash flow to the firm or the FCFF.
What is difference between cash flow and free cash flow? ›
Comparing Cash Flow vs. Free Cash Flow. Cash flow is seen as a straightforward measure of the net cash that came into or left the business during a given period of time. Free cash flow is a figure that tells investors how much cash your business has on hand after funding its operating and investing needs.
Build a flow in Excel for the web
- Open your Excel workbook in Excel for the web.
- On the ribbon, on the Automate tab, select Automate Work.
- Browse the prebuilt templates, and select one.
- Follow the prompts to connect to the app or service that you want to integrate with Excel for the web. Then select Create flow.
To have a healthy free cash flow, you want to have enough free cash on hand to be able to pay all of your company's bills and costs for a month, and the more you surpass that number, the better. Some investors and analysts believe that a good free cash flow for a SaaS company is anywhere from about 20% to 25%.
How do you calculate cash flow quickly? ›
How to Calculate Net Cash Flow
- Net Cash-Flow = Total Cash Inflows – Total Cash Outflows.
- Net Cash Flow = Operating Cash Flow + Cash Flow from Financial Activities (Net) + Cash Flow from Investing Activities (Net)
- Operating Cash Flow = Net Income + Non-Cash Expenses – Change in Working Capital.
A cash flow statement tracks the inflow and outflow of cash, providing insights into a company's financial health and operational efficiency. The CFS measures how well a company manages its cash position, meaning how well the company generates cash to pay its debt obligations and fund its operating expenses.
How to go from net income to free cash flow? ›
FCFF Formula
- NOPAT = EBIT × (1 – Tax Rate %)
- Free Cash Flow to Firm (FCFF) = NOPAT + D&A – Change in NWC – Capex.
- FCFF = Net Income + D&A + [Interest Expense × (1 – Tax Rate)] – Change in NWC – Capex.
- FCFF = Cash from Operations (CFO) + [Interest Expense × (1 – Tax Rate)] – Capex.
FCFF can also be calculated from EBIT or EBITDA: 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.
How to get to FCF from net income? ›
An alternative FCF formula is net income plus non-cash expenses minus the increase in working capital and capital expenditure, offering an additional perspective on cash flow dynamics and financial health.
What is the FCF ratio? ›
The FCF ratio is the ratio of free cash flow to operating cash flow. Free cash flow is the cash left over after deducting capital expenditures from operating cash flow. Capital expenditures are the cash spent on acquiring or maintaining long-term assets, such as buildings, equipment, and software.
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.
