How to Build a DCF Excel Formula Without Losing Your Mind

It’s 11:45 PM. You’ve got a blank spreadsheet open, a half-cold cup of coffee beside you, and a blinking cursor mocking your life choices.

You typed "dcf excel formula" into a search engine because someone—a professor, a senior analyst, or maybe just your own curiosity—asked you to figure out what a business is actually worth. Right now, your screen is a graveyard of broken parentheses, circular references, and terrifying finance jargon like "weighted average cost of capital" and "terminal value." It feels like you need an advanced degree in mathematics just to get a single number to appear in a cell.

Take a breath. Step away from the keyboard for a second.

Discounted cash flow valuation sounds intimidating because textbooks love to wrap simple arithmetic in intimidating Greek letters. But at its core, a DCF model is just answering one very human question: If I hand over my hard-earned money today for a business, how much cash will it pay me back tomorrow, and is it worth the wait?

We are going to build that answer together, line by line, right inside Excel. No PhD required.

The Core Concept: Why Future Cash Needs a Discount

Before we touch a single cell, let's look at the philosophy behind the math.

Imagine a friend offers you a deal. They will give you £1,000. The catch? They can pay you right now, or they can pay you exactly one year from today. Which do you choose?

You take the money now, obviously. Inflation eats away at purchasing power, and £1,000 today can be invested to earn a return over the next twelve months. Money has a time value. A pound in your hand today is worth more than a pound promised twelve months from now.

A DCF model applies this exact logic to entire companies. When we value a business, we are trying to predict all the free cash flow it will generate over the next five to ten years, and then "discount" those future pounds or dollars back to what they are worth today.

If you want to play with the underlying math of how money compounds and discounts over time, you can always test concepts using our interactive Mortgage Calculator to see how time shifts the value of payments—the underlying math of present value is surprisingly similar.

Step 1: Mapping Out Your Spreadsheet Skeleton

Open a blank Excel sheet. Don't worry about formulas yet. Let's build the scaffolding so your brain can see where the numbers go.

In finance, we typically break a DCF into three clear acts:

1. The Forecast Period: Predicting cash flows for the next 5 years.
2. The Terminal Value: Estimating what the entire rest of the company's life is worth after year 5.
3. The Present Value Calculation: Discounting everything back to today's money and adding it up.

Set up your columns horizontally across the top:

• Column A: Line Items (Revenue, Free Cash Flow, etc.)
• Column B: Year 1
• Column C: Year 2
• Column D: Year 3
• Column E: Year 4
• Column F: Year 5
• Column G: Terminal Year

Now, down Column A, type out your labels:

• Revenue
• Free Cash Flow (FCF)
• Discount Period (1 through 5)
• Discount Factor
• Present Value of FCF

This is your canvas. Now let's paint it.

Step 2: Projecting Free Cash Flow

Let's use a hypothetical company we'll call Apex Widgets. Say you estimate that Apex generated £10 million in Free Cash Flow this year, and you expect that cash flow to grow by a steady 6% every year for the next five years.

We need to tell Excel how to grow that number year over year.

1. In cell B2 (Year 1 FCF), type your starting cash flow: 10 (representing £10 million).
2. In cell C2 (Year 2 FCF), write the growth formula: =B2 * (1 + $H$1), where cell H1 holds your growth rate assumption of 0.06 (6%).
3. Drag that formula across to Column F (Year 5).

Watch out for the classic rookie mistake here: blindly compounding growth forever. Companies cannot grow faster than the global economy forever. If your growth rate in year five is still sitting at 20%, your model is hallucinating. Keep your growth rates grounded in historical reality.

Step 3: Finding Your Discount Rate (WACC)

This is where most people freeze up. The "Discount Rate" is formally known as the Weighted Average Cost of Capital, or WACC.

Don't let the acronym intimidate you. WACC is simply the average rate of return the company’s investors (both debt and equity) expect to make. It’s the hurdle rate. If a project doesn't clear the WACC, it destroys value.

For our hypothetical exercise, let’s assume Apex Widgets has a WACC of 9% (type 0.09 into cell H2).

This 9% is the engine of your entire DCF. Every future cash flow will be divided by this rate raised to the power of the year it occurs.

Step 4: The Core DCF Excel Formula (Present Value)

Now we get to the main event. We need to discount those future cash flows back to today.

In Excel, the formula for Present Value is actually built right in: the PV function. But to truly understand what's happening under the hood—and to avoid the quirky formatting issues the PV function sometimes brings—it’s safer to build the math out manually.

The manual formula for Present Value is: $$\text{Present Value} = \frac{\text{Future Cash Flow}}{(1 + \text{Discount Rate})^{\text{Year}}}$$

Let’s translate that into an Excel formula.

Below your cash flows, set up your "Discount Period" row (numbers 1, 2, 3, 4, 5 in cells B4 through F4).

In cell B5 (Present Value of Year 1), write this exact formula: =B2 / (1 + $H$2)^B4

Let’s break down what Excel is doing in that cell:

• B2 is your Year 1 Free Cash Flow (£10.6 million).
• $H$2 is your 9% WACC, locked with dollar signs so Excel doesn't shift the cell when you drag the formula.
• B4 is the year number (1).

Drag that formula across to Column F.

Look at what just happened in your spreadsheet. A cash flow of £13.3 million in Year 5 is suddenly showing up as a present-day value of roughly £8.6 million. That is the time value of money doing its job. You have successfully discounted your first set of cash flows.

If you are looking at how different recurring payment schedules affect present values or loan balances in other financial contexts, tools like our EMI Calculator use similar compounding and discounting principles to break down monthly obligations.

Step 5: Calculating the Terminal Value

Companies don't magically cease to exist after year five. They keep operating into perpetuity. How do we account for all the cash flows beyond year five without forecasting out until the year 2150?

We use the Gordon Growth Model to calculate the "Terminal Value."

1. Pick a long-term perpetual growth rate for the company. A safe bet for a mature company is usually around 2% to 3% (reflecting long-term GDP growth). Let's put 0.025 into cell H3.
2. Take your final year's cash flow (Year 5), grow it by that perpetual rate one more time, and divide it by your WACC minus your perpetual growth rate.

In Excel, your Terminal Value formula in cell G2 looks like this: =(F2 * (1 + $H$3)) / ($H$2 - $H$3)

This gives you a massive number representing the estimated value of the company from Year 6 onward.

Now, just like you did with the yearly cash flows, you must discount that Terminal Value back to today's money. In cell G5, write: =G2 / (1 + $H$2)^5

Step 6: Summing It All Up to Find Enterprise Value

You are at the finish line.

You have the present value of the next five years of cash flows, and you have the present value of the terminal value. To find the total Enterprise Value of the company, you just need to add them all up.

In a clean summary cell, write the SUM formula: =SUM(B5:F5) + G5

Hit Enter.

Whatever number pops up in that cell is your baseline valuation for Apex Widgets based on the assumptions you fed the model.

Common pitfall alert: If your final valuation comes out negative, or looks astronomically large (like the GDP of a small nation), check your WACC and growth rates. If your perpetual growth rate is higher than your WACC, the math breaks down because you are dividing by a negative number. Excel will throw a #NUM! error, and your spreadsheet will effectively break. Your discount rate must always be higher than your terminal growth rate.

Why Your DCF Is Always "Wrong" (And Why That's Okay)

If you hand this completed spreadsheet to a seasoned investment banker, do you know what they will do?

They will smile, tweak your growth rate down by 1%, and tell you your valuation is too high.

Here is the dirty secret of finance: A DCF model is not a crystal ball. It is not a machine that spits out an absolute, indisputable truth. It is a sandbox for your assumptions.

Because you are forecasting the future, tiny tweaks to your inputs create massive swings in your final output. This is why professionals never rely on a single DCF output. Instead, they build sensitivity tables.

In Excel, you can use the "Data Table" tool under the What-If Analysis menu to test a matrix of outcomes: what happens to our company value if WACC is 8% instead of 9%? What if growth is 4% instead of 6%?

Building a model forces you to understand the levers that actually drive a business: margins, reinvestment rates, and risk. When you change an input, you get to watch the ripple effect across the entire income statement.

If you enjoy playing with how inputs alter long-term outcomes—whether evaluating business investments or mapping out personal asset accumulation—our broader collection of tools across Investing and Business Finance categories can help you run parallel scenarios without wrestling with manual cell references.

The Relief of a Working Model

Look back at your screen.

The blinking cursor isn't mocking you anymore. Instead, it’s resting inside a neat, clean grid of formulas that you built with your own hands.

You took a chaotic mess of financial theory, broke it down into distinct steps, mapped out your cash flows, applied your discount rates, and extracted a concrete valuation. It might feel fragile right now, but every financial analyst on Wall Street started with this exact same messy spreadsheet late at night.

You don't need to memorize every corporate finance textbook. You just need to know how to set up the skeleton, respect the time value of money, and keep your growth assumptions honest.

Save the file. Close the tab. You earned that coffee.

---

Disclaimer: This guide is for educational and informational purposes only and does not constitute financial or investment advice. Always run your own due diligence or consult a qualified professional before making financial decisions.

For quick financial calculations on the go, check out the free Finlaa app to run numbers anytime, anywhere.

Frequently Asked Questions

What is the most common mistake people make in an Excel DCF?

The most common error is a mismatch in timing conventions—treating mid-year cash flows as if they occur precisely on the final day of the year, or letting the terminal growth rate exceed the WACC. Always double-check that your discount exponent matches the exact period of the cash flow, and ensure your WACC is strictly higher than your terminal growth rate to avoid #NUM! errors.

Should I use the built-in Excel PV function or manual formulas?

While Excel has a dedicated =PV(rate, nper, pmt, [fv]) function, building the math out manually (=CashFlow / (1 + WACC)^Year) is often better for standard corporate DCFs. Manual formulas make it much easier for colleagues, professors, or clients to audit your work row by row without digging into hidden function arguments.

How do I handle negative cash flows in the early years of a startup DCF?

If a company is burning cash in years one and two, the standard DCF formula still works mechanically—negative cash flows will simply yield negative present values, reducing the overall valuation. However, if cash flows are negative due to massive heavy capital expenditures that will eventually pay off, analysts often extend the forecast period (the "high growth phase") until the company normalizes and turns consistently positive before applying the terminal value formula.