How to Build a Simple DCF Model in Excel

A DCF model in Excel estimates what a stream of future cash is worth today. You forecast the cash flows, discount each one back to today with a discount rate, add a terminal value for the years after the forecast, and sum the results. This guide is for students, analysts and job seekers who want to understand how the spreadsheet works. It explains the mechanics only; it is not investment advice, and every figure below is an example, with the arithmetic checked in Python.

What a DCF model does#

DCF stands for discounted cash flow. The idea is simple: 100 received in five years is worth less than 100 today, because money today can be saved or invested in the meantime. The discount rate is the yearly rate used to shrink future amounts to today's value. The shrunk amount is called the present value.

The formula for one cash flow is:

present value = cash flow / (1 + discount rate) ^ year

A DCF model applies that to every year of a forecast and adds the results. If you have used the NPV function, this is the same idea; see how NPV works in Excel and the comparison of NPV and PV.

Step 1: put the inputs in their own cells#

Keep every assumption in a labelled input cell, never typed inside a formula. That way you can change one number and watch the whole model update.

The terminal growth rate is the yearly growth you assume for cash flows after the forecast ends. It should be modest, because it is assumed to last forever.

Step 2: lay out the forecast cash flows#

Put the years across a row and the forecast free cash flow below them. Free cash flow is, roughly, the cash a business generates from its operations after paying for equipment and other investment. In this example, the figures are in thousands:

The cash flows themselves usually come from a separate forecast. The guide to building a cash flow forecast shows how to lay one out, and the CAGR formula helps you check whether the growth you assumed is realistic.

Step 3: discount each year with locked references#

The discount factor is 1 / (1 + rate)^year, and the present value is the cash flow times that factor. In B6 and B7:

B6: =1/(1+$B$1)^B4
B7: =B5*B6

Select B6:B7 and fill right to column F (Windows: Ctrl+R; Mac: ⌘R). The $B$1 reference stays locked on the discount rate, while B4 and B5 move to C4, C5 and so on. Without the $ signs, the rate reference would slide to C1, D1 and E1, which are empty, and the factors would all become 1. To add the $ signs quickly, press F4 after typing B1 (Mac: ⌘T or Fn+F4).

Step 4: add the terminal value#

The terminal value is one number that stands for all the cash flows after year 5. A common method is the growth formula: next year's cash flow divided by (discount rate minus growth rate). In B10:

=F5*(1+B2)/(B1-B2)

That is 170 × 1.02 / 0.08 = 2,167.5. This value is at the end of year 5, so discount it back five years in B11:

=B10/(1+B1)^F4

The result is about 1,345.8. The formula only works when the discount rate is higher than the growth rate. If they are equal, you divide by zero.

Step 5: add it all up with SUM#

The estimated value is the sum of the yearly present values plus the discounted terminal value:

In finance this total is often called enterprise value. To get the value for shareholders, analysts then subtract net debt. The SUM function guide covers the basics of adding ranges.

Notice that the terminal value is about 71% of the total. That is common in DCF models, and it is why the growth and discount rates matter so much.

You can check the first part with one function. =NPV(B1,B5:F5) returns about 540.6, the same as the SUM of row 7, because NPV assumes the first cash flow comes at the end of year 1. If your cash flows arrive on uneven dates, XNPV takes a list of dates as well and discounts each amount by the exact time.

Step 6: test how sensitive the result is#

A DCF result is an estimate built on assumptions, so show how it moves when the assumptions move. Change one input at a time and record the total:

One percentage point on either input moves the result by roughly 8 to 15%. That is why a single DCF number should never be read as exact. Excel's What-If Analysis > Data Table can build this grid automatically. To work out the rate at which a set of cash flows breaks even instead, see the IRR function.

Common DCF mistakes in Excel#

Most errors in a simple DCF are reference and timing slips. Check these:

1. The rate isn't locked. Missing $ signs make later years use an empty cell as the rate.
2. Year 0 inside NPV. NPV discounts its first value by one year. A cash flow that happens today should be added outside NPV.
3. Terminal value not discounted. Adding 2,167.5 straight to the total overstates the value.
4. Growth rate at or above the discount rate. The terminal formula then breaks or gives a negative number.
5. Rates typed as whole numbers. Use 10% or 0.1, not 10.

Practise it#

A DCF is a chain of simple arithmetic and locked references, and those are exactly the skills a finance Excel test checks. Practise arithmetic formulas on a department budget, then practise absolute references with $ signs: you type each formula and ExcelReady checks it instantly. Then try the NPV exercise on this page, or test yourself with the finance Excel mock test.