Unlevered Free Cash Flow Formula in Excel: Three Routes to the Same Number

Unlevered free cash flow is the cash a business produces before it pays anyone who lent it money. It is the number a DCF discounts at WACC. It is "unlevered" because it ignores how the company is financed. Two identical businesses, one with debt and one without, should have the same unlevered free cash flow.

The formula is short. Getting it right from a real set of financial statements is where people slip. This guide builds it three ways on one company so you can check your own model against itself. If the three routes disagree, one of your lines is wrong.

For education and planning only, not financial advice.

The formula

` Unlevered FCF = EBIT × (1 − tax rate) + D&A − Capex − Increase in net working capital `

EBIT × (1 − tax rate) is called NOPAT, net operating profit after tax. It is what the company would pay in tax if it had no debt. That is the whole idea. Interest is a financing cost, so it stays out, and so does the tax saving it creates.

The worked company

One year of a mid-sized business. All figures in $ millions.

| Income statement | | |---|---| | Revenue | 2,400 | | Cost of goods sold | 1,440 | | SG&A (includes 24 of stock-based comp) | 420 | | D&A | 96 | | EBIT | 444 | | Interest expense | 60 | | Pre-tax income | 384 | | Tax at 25% | 96 | | Net income | 288 |

| Balance sheet | Prior year | This year | |---|---|---| | Accounts receivable | 300 | 330 | | Inventory | 260 | 285 | | Accounts payable | 180 | 195 | | Net working capital | 380 | 420 |

Capex for the year is 130.

Route 1: from EBIT

This is the textbook route and the one to build first.

In Excel, with years across columns and the tax rate in a named cell:

`excel NOPAT =C8*(1-TaxRate) Change in NWC =(C20+C21-C22)-(B20+B21-B22) Unlevered FCF =C30+C9-C25-C26 `

Here row 8 is EBIT, row 9 is D&A, rows 20 to 22 are receivables, inventory and payables, row 25 is capex, row 26 is the change in NWC and row 30 is NOPAT. Keep capex and the NWC change as positive numbers and subtract them in the FCF line. Mixing sign conventions across rows is the most common way this formula breaks.

Route 2: from net income

Sometimes you start from net income because that is what a data provider gives you. Net income already has interest taken out, and it has the interest tax saving baked in. So you add back interest after tax, not the full amount.

`excel Unlevered FCF =C12+C10*(1-TaxRate)+C9-C25-C26 `

Row 12 is net income and row 10 is interest expense. The 45 is the bridge. NOPAT of 333 minus net income of 288 is 45. That is exactly the after-tax cost of debt for the year.

Route 3: from cash flow from operations

The cash flow statement gives you cash flow from operations (CFO). For this company it would show:

To get to unlevered FCF from there you make three moves:

The last line is a judgment call, and it is the one that most changes valuations. CFO adds stock-based comp back because it is not cash. But it is a real cost to shareholders. They pay it through dilution instead of through the bank account. If you leave it added back, you get 283. That overstates unlevered FCF by 9.3% on this company. If stock comp stays the same share of cash flow in the out years, the terminal value is overstated by the same 9.3%. For software companies where stock comp runs 10% to 20% of revenue, the gap is much larger.

Pick one treatment and use it everywhere. If you subtract stock comp in the cash flow, use today's diluted share count for the per-share price. That means basic shares plus the options and RSUs already granted, with nothing added for future grants. If you leave stock comp added back, you must also add an estimate of future dilution to the share count. Doing neither leaves the cost out of the valuation entirely.

The three routes side by side

| | From EBIT | From net income | From CFO | |---|---|---|---| | Starting point | NOPAT 333 | Net income 288 | CFO 368 | | After-tax interest | already excluded | +45 | +45 | | D&A | +96 | +96 | already in CFO | | Stock-based comp | already expensed | already expensed | −24 | | Change in NWC | −40 | −40 | already in CFO | | Capex | −130 | −130 | −130 | | Unlevered FCF | 259 | 259 | 259 |

Build all three in a check block under your model and add one cell:

`excel =IF(MAX(C31:C33)-MIN(C31:C33)<0.5,"OK","CHECK") `

When that cell says CHECK, something upstream is wrong. It catches sign errors and missing add-backs before they reach the valuation.

Five mistakes and what each one costs

Each line below is the same company with one mistake. The percent is how far it lands from the correct 259.

| Mistake | Result | Error | |---|---|---| | Adding back interest pre-tax (60, not 45) from net income | 274 | +5.8% | | Leaving stock-based comp added back from CFO | 283 | +9.3% | | Adding the NWC increase instead of subtracting it | 339 | +30.9% | | Using EBITDA instead of NOPAT as the start | 370 | +42.9% | | Forgetting after-tax interest from net income | 214 | −17.4% |

The last row is not quite a mistake. 214 is roughly levered free cash flow, the cash left for shareholders after lenders are paid (before any new borrowing or repayment). It is a real number, but it belongs in an equity valuation discounted at the cost of equity. Discounting it at WACC mixes two frameworks and undervalues the company.

Two checks on the result

Cash conversion. Unlevered FCF divided by NOPAT is 259 / 333 = 77.8%. A mature business usually sits somewhere around 70% to 100%. Well above 100% for years in a row means capex is below D&A, which a growing company cannot keep up. Well below 50% means growth is eating the cash, which is fine early on but should improve in your out years.

FCF margin. 259 / 2,400 = 10.8% of revenue. Compare it to the company's own history before you project it. If your year 5 margin is double anything the company has ever printed, the forecast needs a reason.

Which tax rate to use

Use the rate you expect the company to pay on operating profit. The effective rate from the income tax footnote is the usual starting point. If the company carries tax losses forward, the cash tax rate will be lower for a few years. Model that as a separate line so the lower rate ends when the losses run out, rather than baking a low rate into the terminal year forever.

Putting it in a forecast

Once the formula works on one historical year, project each input as a percent of revenue: margin for EBIT, D&A and capex as a percent of revenue, and net working capital as a percent of revenue so the change scales with growth. The FCF line stays the same formula in every column. That is what makes a sensitivity table possible later.

If you want this already built, the Numbersmith DCF Valuation Model has a five-year unlevered FCF build from these inputs, a full WACC, two terminal value methods and formula-driven sensitivity grids, all with open formulas.