How to Calculate Free Cash Flow in Excel (to Firm and Equity)

Part 1 – Calculating the Free Cash Flow to Firm (FCFF)

Steps:

To calculate Free Cash Flow to Firm (FCFF), we have collected an Income Statement of Profit & Loss like the image below.

We also have an Income Statement of Cash Flow, as shown in the image below, to compute the Free Cash Flow to Firm (FCFF).

We will calculate all necessary items needed for FCFF in the second 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 second sheet of our Excel Workbook.
Enter “=” in C5 of the second sheet and select the C7 cell of the first sheet of the Excel file, then press Enter:

='Income Statement'!$C$7

Complete the series by dragging the fill handle to the right.

In C6 of the second sheet, use the following:

='Income Statement'!C10

We have entered D&A for Fiscal Years 2016-17 and completed the series by autofilling to the right.

Calculate EBIT by entering the following formula:

=C5-C6

EBIT= EBITDA – Depreciation & Amortization

C5= EBITDA

C6= D&A

Drag the fill handle to the right.

To calculate Taxes in the cell C8, use the following:

=C7*'Income Statement'!C12

Taxes= EBIT x Tax rate

C7= EBIT

‘Income Statement’!C12 = C12 in the Income Statement sheet.

Drag the fill handle to the right.

In cell C9, enter the following formula:

=C7-C8

NOPAT means Net Operating Profit After Tax

NOPAT=EBIT – Taxes

C7= EBIT

C8= Taxes

Drag the fill handle to the right.

In C10, use the following formula:

='Income Statement'!C19+'Income Statement'!C20

Drag the fill handle to the right.

Investment in Working Capital in cell C11 of the second sheet denotes Working Capital Changes in cell C17 of the first sheet. The formula in C11 needs to be the following:

='Income Statement'!C17

Drag the fill handle to the right.

Enter the reference to the C6 cell in the C12.

Drag the fill handle to the right to complete the series.

In cell C13, enter the following formula to find FCFF:

=C9+C10+C11+C12

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

Drag the fill handle to the right to find the FCFF of each Fiscal Year.

Part 2 – Calculating the 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.

Steps:

In cell C18, insert the following:

=SUM('Income Statement'!C22:C24)

Drag the fill handle to the right.

To calculate FCFE in C19, enter the following formula:

=C13+C18

C13= FCFF

C18= Amount Paid to debt Holders

Drag the fill handle to the right to complete the calculation of FCFE.

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. Here are some ways to mitigate that

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 the Practice Workbook

Free Cash Flow Calculation.xlsx