Free cash flow is considered as a firm’s available funds once all costs have been paid. Cash is an essential component of every firm. It is necessary for corporate operations; some investors would prefer cash flow statements over other financial figures. Free cash flow is the amount of cash that a company produces even after it pays the costs and debts. It aids in determining your true financial position of free cash flow based on the cash statements. In this article, I will show how to calculate free cash flow to firm and equity in Excel.
Free Cash Flow or FCF is the portion of cash flow that is “available” for managerial usage after all kinds of investments and repayments of a company are done. Free cash flow can be categorized into 2 classes:
- Free Cash Flow to Firm (FCFF)
- Free Cash Flow to Equity (FCFE)
1. Calculating Free Cash Flow to Firm (FCFF)
FCFF, or Free Cash Flow to Firm, is the amount of cash available to the whole firm, including debt holders and stockholders. The amount of cash available after investing in work capital and fixed capital is referred to as FCFF or Free Cash Flow to Firm.
📌 Steps:
- To calculate Free Cash Flow to Firm (FCFF), we have collected an Income Statement of Profit & Loss like the image below. Remember that there is a group of data that we need to calculate FCFF.
- We gathered an Income Statement of Cash Flow, as shown in the image below, to compute Free Cash Flow to Firm (FCFF). Remember that the following data is necessary to compute FCFF.
- First of all, in this step, we will calculate all necessary items needed to calculate FCFF in the 2nd sheet of our Excel Workbook titled Free Cash Flow to Firm.
- Earnings Before Interest, Taxes, Depreciation, and Amortization, or EBITDA is an Operating Profit that is in the C7 of the first sheet of the Excel file. So, we need the C7 of the first sheet in the 2nd sheet of our Excel Workbook.
- To do so, we will enter “=” in C5 of the second sheet >> select the first sheet of the Excel file >> select the C7 cell of the first sheet of the Excel file >> press Enter and the C5 of the second sheet will like the following:
='Income Statement'!$C$7
- For now, we have put EBITDA for Fiscal Years 2016-17 and to complete the process we will drag the fill handle in the right direction.
- Now, in this step, we will take Depreciation & Amortization from the 1st sheet needed to calculate FCFF in the 2nd sheet of our Excel Workbook titled Free Cash Flow to Firm.
- Depreciation & Amortization: Depreciation is the expenditure incurred by a fixed asset to represent its predicted decline. Amortization is a method of spreading the cost of an intellectual asset over its life.
- To accomplish so, insert “=” in C6 of the second sheet >> choose the first sheet of the Excel file >> choose the C10 cell from the first sheet of the Excel file >> When you hit Enter, the C6 of the second page will look like this:
='Income Statement'!C10
- Again, for this moment, we have entered D&A for Fiscal Years 2016-17 and to complete the series we will pull the fill handle to the right.
- We can calculate EBIT by entering the following formula:
=C5-C6Here,
EBIT= EBITDA – Depreciation & Amortization
C5= EBITDA
C6= D&A
- To accomplish the EBIT series, we will drag the fill handle to the right.
- To calculate Taxes, we will enter: =>>select C7>> press ‘*’>>go to 1st sheet and select C12>>press Enter.
- So, in the cell C8, the following formula will look like this:
=C7*'Income Statement'!C12Here,
Taxes= EBIT x Tax rate
C7= EBIT
‘Income Statement’!C12 = C12 in the Income Statement sheet.
- Afterward, drag the fill handle rightwards.
- So, in cell C9 we will enter the following formula:
=C7-C8Here,
NOPAT means Net Operating Profit After Tax
NOPAT=EBIT – Taxes
C7= EBIT
C8= Taxes
- Further to complete the series we will drag the fill handle to the right.
- In C10 we will enter = sign >> go to 1st sheet >> select C19 >> enter “+” sign >> select C20 so in the formula box the following will be shown:
='Income Statement'!C19+'Income Statement'!C20
- Now similar to the previous drag the fill handle rightwards.
- Investment in Working Capital in cell C11 of the second sheet denotes Working Capital Changes in cell C17 of the first sheet. So, the formula box of C11 will look like the following:
='Income Statement'!C17
- Again, we will drag the fill handle rightwards.
- We will enter C6 cell in the C12, D&A has already been collected from the first sheet so for this time we will not go to the first sheet.
- Now drag the fill handle to the right to complete the series.
- In cell C13, we will enter the following formula to find FCFF:
=C9+C10+C11+C12Here,
Free Cash Flow to Firm (FCFF) = NOPAT (Net Operating Profit After Tax ) – Investment in Fixed Asset – Investment in Working Capital + D&A
C9= NOPAT
C10= Investment in Fixed Asset
C11= Investment in Working Capital
C12= D&A
- Now we will drag the fill handle to the right to find the FCFF of each Fiscal Year.
Read More: How to Calculate Annual Cash Flow in Excel
2. Calculating Free Cash Flow to Equity (FCFE)
FCFE, or Free Cash Flow to Equity, is the amount of cash available to the company’s owner after debt, investment, and interest payments have been made. FCFE, or Free Cash Flow to Equity, is the amount of cash available after investing in working capital and fixed capital and repaying debt.
📌 Steps:
- In cell C18, we will enter: =SUM( >> go to first sheet>> select C22:C24 >> enter ‘)’ >> press ENTER. So, the following formula in cell C18 will look like the following:
=SUM('Income Statement'!C22:C24)
- Like many previous stages, we will drag the fill handle rightwards.
- To calculate FCFE in C19 we will enter the following formula:
=C13+C18Here,
C13= FCFF
C18= Amount Paid to debt Holders
- Lastly, we will drag the fill handle rightwards to complete the calculation of FCFE.
Read More: How to Calculate Incremental Cash Flow in Excel
How to Decide If a Free Cash Flow Is “Good”
Since FCF considers variations in working capital, it may give meaningful insights about an enterprise’s worth and the sustainability of its basic trends. Good FCF denotes the ability of a company in the way that it can pay debts, pay dividends, buy its stocks, and speed up a company’s development.
What to Do If Free Cash Flow Is Negative:
The cash a corporation has left over after paying its operational expenditures is tracked by free cash flow. If there is no leftover, then the cash flow is negative. To solve the problem, we have to do the followings:
- Spend less money.
- Regularly examine all expenditure
- Prepare an emergency fund to cover unforeseen costs.
Limitations of Using Free Cash Flow in Business Plan:
- Low free cash flow does not automatically indicate a failed firm.
- Although much less susceptible to accounting fraud than other estimates, free cash flow is not fully immune.
Download Practice Workbook
You can download the practice workbook from the following download button.
Conclusion
Follow these steps and stages on how to calculate free cash flow in Excel. You are welcome to download the workbook and use it for your practice.
Related Articles
- How to Calculate Discounted Cash Flow in Excel (with Easy Steps)
- How to Forecast Cash Flow in Excel (with Detailed Steps)
- How to Calculate Cumulative Cash Flow in Excel
- How to Draw a Cash Flow Diagram in Excel
- How to Track Cash Flow in Excel
- How to Create a Personal Cash Flow Statement in Excel
- How to Calculate Operating Cash Flow Using Formula in Excel
- How to Calculate Payback Period in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel
- How to Calculate Net Cash Flow in Excel
<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
