How to Do Discounted Cash Flow in Excel: A Plain-English Guide
30 July 2026
How to Do Discounted Cash Flow in Excel: A Plain-English Guide
It is 11:45 PM. You have three browser tabs open, a half-empty cup of tea getting cold beside your keyboard, and a massive spreadsheet grid staring back at you like an unblinking eye.
Somewhere in that grid is a future business valuation, a property investment, or a project proposal you promised to have ready for a morning meeting. You know you need to run a discounted cash flow analysis. You also know that the last time you tried, you ended up trapped in a labyrinth of parentheses, getting a "#VALUE!" error that felt less like a math problem and more like a personal rejection.
Take a breath. Step away from the tea for a second.
Discounted cash flow—or DCF, if we are speaking corporate shorthand—sounds like the kind of phrase business school professors use to clear a room. But at its core, it is actually one of the most grounded, common-sense concepts in finance. It answers a very human question: What is money that I am going to get tomorrow actually worth to me today?
If you have ever wondered whether a business investment is worth your hard-earned capital, or how to project future earnings without losing your sanity in Excel, you are in the right place. We are going to walk through this together, strip away the Wall Street smoke and mirrors, and build a working DCF model in Excel that you can actually understand and use.
Why Future Money Is Worth Less Than Today’s Money
Before we touch a single Excel cell, we need to agree on a fundamental truth of the universe: £100 today is better than £100 five years from now.
This isn't just about inflation making a pint of milk more expensive down the road. It is about opportunity. If you have £100 today, you can put it in a savings account, invest it in a portfolio, or plough it back into your business to make it grow. By the time five years rolls around, that £100 should have turned into something more.
Conversely, if someone promises to hand you £100 five years from now, and you need that cash today to pay bills, you are stuck.
This is where the concept of the time value of money comes in. To compare cash coming in at different points in the future, we have to pull all those future numbers back to the present day. We call this process "discounting."
Think of it like looking through the wrong end of a telescope. Objects far away look smaller than they actually are. In a DCF model, money far out in the future looks smaller when we calculate its present value, because we are shrinking it down by a specific percentage—known as the discount rate—to account for time and risk.
The Core Formula: Demystifying the Math
If you open a finance textbook, the DCF formula looks like an ancient text written to ward off accountants:
$$\text{DCF} = \frac{CF_1}{(1+r)^1} + \frac{CF_2}{(1+r)^2} + \dots + \frac{CF_n}{(1+r)^n}$$
Let’s translate that alphabet soup into plain English.
- $CF$ stands for Cash Flow in any given year ($CF_1$ is Year 1, $CF_2$ is Year 2, and so on).
- $r$ stands for the Discount Rate (the return you expect or require to take on this risk).
- $n$ stands for the Year number.
All the formula is doing is taking each year's expected cash pile, dividing it by one plus your discount rate raised to the power of that year, and adding them all up.
If you are calculating loans, mortgages, or regular payments elsewhere on your financial journey, you might use specific tools like a Loan EMI Calculator to see how regular outflows behave over time. But a DCF is different: it looks at unpredictable, lumpy, future cash inflows and reduces them to a single lump sum today.
Setting Up Your Excel Grid: The Blueprint
Let’s build a model together. Imagine you are looking at a small business acquisition or an expansion project. You want to see if the project is worth an initial investment of £50,000.
Open a blank Excel sheet and let's lay out the structure. A clean DCF model should read like a story from top to bottom:
- The assumptions (Discount Rate)
- The timeline (Years 0 to 5)
- The raw projections (Cash Flows)
- The discount math (Present Value of each year)
- The grand total (Net Present Value)
Step 1: Laying Out Your Inputs
In the top-left corner of your spreadsheet, set up a clean assumptions box:
- Cell A1:
Project Discount Rate| Cell B1:10%(Format this as a percentage) - Cell A2:
Initial Investment| Cell B2:-50000(Make it negative, because cash is leaving your pocket today)
Step 2: Creating the Timeline
In Row 5, create your column headers across the page:
- Cell A5:
Year - Cell B5:
Year 0 - Cell C5:
Year 1 - Cell D5:
Year 2 - Cell E5:
Year 3 - Cell F5:
Year 4 - Cell G5:
Year 5
In Row 6, underneath those headers, put your actual timeline numbers: 0, 1, 2, 3, 4, 5.
Step 3: Entering Your Cash Flows
In Row 7, label it Free Cash Flow. Now let's enter some realistic projections for what this project will spit out each year after paying its bills and taxes:
- Year 0 (Cell B7):
=B2(Link it straight to your initial investment of -£50,000) - Year 1 (Cell C7):
12000 - Year 2 (Cell D7):
15000 - Year 3 (Cell E7):
18000 - Year 4 (Cell F7):
22000 - Year 5 (Cell G7):
25000
Right now, if you use a simple SUM function to add all these cash flows together ($12k + $15k + $18k + $22k + $25k = $92,000$, minus your $50,000$ investment), you get a profit of £42,000.
Stop right there. This is where most beginners make a massive mistake. You cannot simply add future money to today's money. We have to discount those future years first.
The Magic Formula: Calculating Present Value in Excel
Now we need a row that calculates what each of those future cash flows is actually worth today, given our 10% discount rate.
In Row 8, label it Present Value (PV).
For Year 0, the cash flow is happening right now, today. It doesn't need to be discounted because it's already present money. So in Cell B8, just type:
=B7
Now for Year 1 (Cell C8). We want to take the Year 1 cash flow (£12,000) and divide it by $(1 + r)^1$. In Excel speak, referencing our discount rate in cell $B$1, the formula looks like this:
=C7 / (1 + $B$1)^C6
Let's break down why we put dollar signs around $B$1: that creates an absolute reference. When you drag that formula horizontally across to Years 2, 3, 4, and 5, Excel will automatically update the cash flow cell (C7 becomes D7, E7, etc.) and the year cell (C6 becomes D6, E6, etc.), but it will keep the discount rate locked firmly on cell B1.
Type that formula into Cell C8, and drag it all the way across to Cell G8.
Look at what happens to your numbers. That £25,000 arriving in Year 5? When pulled back through the lens of a 10% discount rate, its present value is actually only about £15,523. That is the reality check DCF models provide.
Bringing It All Together: The Net Present Value (NPV)
Now that every single cash flow lives in today's money terms, we can finally add them up safely.
In finance, the sum of all discounted future cash flows minus your initial investment is called the Net Present Value (NPV).
If your NPV is positive, the investment is theoretically generating more than your required 10% return. It’s a green light. If your NPV is negative, you are destroying value compared to what you could earn elsewhere, and you should walk away.
You can calculate this manually in Excel by summing up all the present value cells from Year 1 to Year 5, and then adding your initial investment (which is negative).
=SUM(C8:G8) + B7
Type that into a summary cell, hit enter, and watch your number appear. In our example, the NPV comes out positive at roughly £18,245.
The Shortcut: Excel’s Built-in NPV Function
Excel actually has a built-in function to save you from creating a whole row of intermediate present value calculations. It's called =NPV().
However, be careful here. Excel's NPV function is notoriously tricky and trips up even seasoned professionals.
If you use =NPV(rate, value1, value2...), Excel assumes that the first value you select occurs at the end of Year 1, not Year 0. It does not automatically subtract your initial investment.
To use the Excel NPV function correctly with our model, you would write:
=B7 + NPV(B1, C7:G7)
Notice the structure: you take your Year 0 cash flow (the initial outlay) and add it to the NPV of the future cash flows from Year 1 to Year 5. If you test this, you will get the exact same result: a clean, reliable valuation of £18,245.
What Trips People Up: Common DCF Edge Cases and Mistakes
Building the grid is the easy part. Knowing what numbers to put into the grid is where art meets science. Here are the traps that catch people out:
1. Mixing Up Nominal and Real Cash Flows
If your cash flow projections include the effects of inflation (meaning you expect to raise prices by 3% every year because things cost more), your discount rate must also account for inflation. If you use a real cash flow with a nominal discount rate, your valuation will be completely skewed. As a rule of thumb for beginners: keep your cash flows in today's price terms, and use a nominal discount rate that reflects your actual cost of capital.
2. Forgetting the Terminal Value
In our example, we looked at a neat 5-year timeline. But what happens after Year 5? Does the business or project magically vanish into thin air?
Of course not. Successful businesses or assets keep generating cash forever into the future. To capture that, DCF models use a Terminal Value calculation at the end of the forecast period, estimating the value of all cash flows beyond Year 5 using a modest long-term growth rate. If you are valuing a project with a strict, hard end date (like a single real estate development or a specific machinery lease), you can ignore terminal value. But if you are valuing a going concern, leaving it out will drastically undervalue the asset.
3. Falling Victim to "Garbage In, Garbage Out"
A spreadsheet model feels objective because it uses math. But remember: the cash flow numbers you type into Row 7 are just educated guesses. If you plug wildly optimistic growth numbers into a DCF, Excel will happily spit out a massive, beautiful valuation—that is completely wrong. Always run a sensitivity analysis: what happens to your NPV if cash flows are 20% lower than you hope? What if your discount rate jumps by 2%?
A Simpler Way to Think About Your Financial Future
When you are staring at a complex financial model late at night, it is easy to feel overwhelmed by the rows and columns. But remember what a DCF is really doing. It is simply helping you look past the noise of future promises and figure out what something is worth today.
Whether you are evaluating a commercial property, pricing a business acquisition, or mapping out a personal investment strategy, breaking your numbers down year by year gives you clarity. You don't need a master's degree in finance to make a smart choice; you just need a structured way to test your assumptions.
And if your current spreadsheet session is actually part of a broader personal property or mortgage evaluation, you don't have to build everything from scratch. You can test your core assumptions instantly using free tools like a Mortgage Calculator to see how monthly commitments stack up before you ever discount a single future cash flow.
Take a deep breath, save your Excel file with a sensible name, and close those extra browser tabs. You’ve got the framework down.
Frequently Asked Questions
What is a good discount rate to use in a DCF model?
There is no single "correct" discount rate, as it depends entirely on risk. For a low-risk, stable project, a discount rate might sit around 6% to 8%. For a risky startup, early-stage venture, or speculative business, investors often demand 15%, 20%, or even higher to compensate for the high chance of failure. A good baseline is your own "cost of capital"—what you would expect to earn if you invested that money elsewhere with a similar risk profile.
Why does Excel's NPV function give me a weird number?
The most common mistake with Excel's =NPV() function is including the Year 0 initial cash flow inside the range of cells you select. Excel assumes the first cell in your range is Year 1. Always leave your Year 0 cash flow outside the NPV function, and add it manually to the final result (e.g., =Initial_Outlay + NPV(Rate, Future_Cash_Flows)).
How many years should I project in my cash flow timeline?
Most standard DCF models project between 3 to 10 years. Five years is the most common sweet spot for businesses; it is far enough out to capture growth and stabilization, but close enough that your revenue and expense projections aren't pure science fiction. Anything beyond that timeline is usually lumped into a "terminal value" calculation.
Disclaimer: This guide is for informational and educational purposes only and should not be construed as professional financial advice. Always run your own due diligence or consult with a qualified financial professional before making major investment decisions.
For quick financial checks on the go, download the free Finlaa app and run your numbers anywhere.
