How to Build a DCF in Excel in 30 Minutes: A Step-by-Step Modeling Test Walkthrough

You open the email, download the file, and see it: "Build a DCF and give us a share price. You have 30 minutes."

This is one of the most common modeling tests in investment banking recruiting, and it's very passable once you have a plan. Most people who struggle don't lack technical knowledge. They lose time on formatting, overthink assumptions, or never get to the final number.

This post walks you through a full 30-minute DCF case with real numbers, a minute-by-minute time budget, and the Excel habits that keep you fast and accurate. Build along with it once or twice before your test.

What a "30-Minute DCF" Case Usually Looks Like

Formats vary by bank, but the timed DCF usually follows one pattern. You get a short prompt with:

• Historical revenue (often just one base year)
• Assumptions for growth, margins, D&A, capex, working capital, and taxes
• Inputs for WACC, or sometimes the WACC itself
• A terminal value method (perpetuity growth, exit multiple, or both)
• Balance sheet items to bridge from enterprise value to equity value

Your job is to build a clean, working model and output an implied share price. A sensitivity table is often a bonus.

Interviewers care about three things: Did you finish? Does it calculate correctly? Can someone else follow it? Beautiful formatting comes last.

Your 30-Minute Time Budget

Print this in your head before you start:

The golden rule: get to a share price first, then improve. A rough model with an answer beats a gorgeous model that stops at Year 3.

The Case Prompt

Here's the practice case we'll use. All numbers are in $ millions except per-share data.

Company: a fictional software business, "NorthPeak"

• 2024 revenue: $500
• Revenue growth: 10%, 9%, 8%, 7%, 6% for 2025 to 2029
• EBIT margin: 20% of revenue
• Tax rate: 25%
• D&A: 4% of revenue
• Capex: 5% of revenue
• Change in net working capital: 10% of the change in revenue
• Risk-free rate: 4.0%
• Beta: 1.4
• Equity risk premium: 5.0%
• Pre-tax cost of debt: 8.0%
• Target capital structure: 80% equity, 20% debt
• Terminal growth rate: 3.0%
• Debt: $250, Cash: $70
• Diluted shares outstanding: 50 million

Assume end-of-year discounting (no mid-year convention) unless told otherwise.

Step 1: Set Up the Layout (Minutes 0 to 3)

Don't spend more than three minutes here. You just need a structure that doesn't fight you later.

The layout that works

• Top section: all assumptions in one block (growth rates, margins, tax rate, WACC inputs)
• Middle section: the projection, with years across the columns (2024A in column C, 2025E to 2029E in D to H)
• Bottom section: WACC, terminal value, and the bridge to share price

Formatting habits that save you

• Blue font for hard-coded inputs, black for formulas. Interviewers notice this immediately.
• Never type a number inside a formula. Write =D5*$C$12, not =D5*0.2. Changing assumptions later then takes seconds.
• Use absolute references (F4) when pointing to a single assumption cell.

Step 2: Project Revenue and EBIT (Minutes 3 to 10)

Put the growth rates in a row above revenue, then each year's revenue is just last year's revenue times (1 + growth).

In Excel: =C6*(1+D5), then drag right with Ctrl+R.

EBIT is revenue times the 20% margin. Here's where you should land:

Quick check: revenue should grow every year, but by smaller amounts as growth decelerates. If 2029 revenue is way off $734, look for a broken reference.

Step 3: Build Unlevered Free Cash Flow (Minutes 10 to 16)

This is the heart of the DCF. Memorize the formula so you never hesitate:

UFCF = EBIT × (1 − tax rate) + D&A − Capex − Increase in NWC

The first piece, EBIT after taxes, is called NOPAT. We use EBIT, not net income, because a DCF values the whole business for all capital providers. Interest is captured in the WACC, not in the cash flows.

The worked numbers

Figures rounded to one decimal, so some columns may be off by 0.1 if you add them by hand. Excel keeps the full precision.

The working capital trap

The NWC assumption is 10% of the change in revenue, not 10% of revenue. For 2025, revenue increases by $50, so the cash outflow is $5.0.

In Excel: =(D6-C6)*$C$15. This is the single most common place candidates slip up on this type of case, so read that line of the prompt twice.

Sign conventions

Pick one approach and stick with it. I like entering capex and NWC as negative numbers and then summing the column with =SUM(). It makes the UFCF row impossible to get wrong.

Step 4: Calculate WACC (Minutes 16 to 19)

Two components, then a weighted average.

Cost of equity (CAPM)

Cost of equity = Risk-free rate + Beta × Equity risk premium

4.0% + 1.4 × 5.0% = 11.0%

After-tax cost of debt

Interest is tax-deductible, so:

8.0% × (1 − 25%) = 6.0%

Weighted average

WACC = (E/V × Cost of equity) + (D/V × After-tax cost of debt)

(80% × 11.0%) + (20% × 6.0%) = 8.8% + 1.2% = 10.0%

Clean number, easy discounting. Real cases won't always be this tidy, but the mechanics are identical.

Step 5: Calculate Terminal Value (Minutes 19 to 22)

The terminal value captures everything after 2029. With the perpetuity growth (Gordon Growth) method:

TV = UFCF in final year × (1 + g) / (WACC − g)

$98.7 × 1.03 / (10.0% − 3.0%) = $101.6 / 0.07 = $1,451.6

Two mistakes that cost people offers

1. Forgetting to grow the final-year cash flow. The formula needs next year's cash flow, which is 2029 UFCF times (1 + g).
2. Discounting the TV by the wrong number of years. The terminal value sits at the end of 2029, so you discount it back 5 years, the same as the 2029 cash flow.

The 10-second cross-check

Divide the TV by 2029 EBITDA (EBIT + D&A = $146.9 + $29.4 = $176.2):

$1,451.6 / $176.2 ≈ 8.2x EBITDA

That's a sensible implied exit multiple. If you got 40x, something is broken. Mentioning this check at the end of your test shows judgment, not just mechanics.

Step 6: Discount and Bridge to Share Price (Minutes 22 to 26)

Discount factors

At 10%, the discount factor for year t is =1/(1+WACC)^t:

Sum of PV of cash flows: $319.6

PV of terminal value: $1,451.6 × 0.621 = $901.3

You can also use Excel's =NPV(WACC, D20:H20) for the cash flows. It assumes the first cash flow arrives at the end of period 1, which matches this case. Building the discount factor row manually is safer, though, because the interviewer can see every step.

From enterprise value to share price

Notice that the terminal value is about 74% of enterprise value. That's normal for a DCF, and it's exactly why the sensitivity table matters.

Step 7: Sanity Checks and Sensitivity (Minutes 26 to 30)

Run these checks in 60 seconds

• Does UFCF grow smoothly? Ours goes 72.0 to 98.7. No weird jumps.
• Is WACC greater than g? If not, the Gordon formula breaks.
• Implied exit multiple reasonable? 8.2x, yes.
• Did you subtract debt and add cash? Not the other way around.

Build a sensitivity table

If you have time left, use Data > What-If Analysis > Data Table to flex WACC and terminal growth. Here's what our model produces:

One percentage point of WACC moves the price by roughly $3 to $4 per share. That's the point to make if they ask what drives the valuation.

Data table tip: the row and column inputs must live on the same sheet as the table, and the output cell goes in the top-left corner. If your file is slow, switch calculation to "Automatic except for data tables."

Excel Shortcuts That Actually Save Time

You don't need 50 shortcuts. You need these:

• Ctrl+R: fill right (projecting across years)
• F4: toggle absolute references
• Ctrl+[ : jump to the cells a formula references (great for debugging)
• Alt+= : AutoSum
• Ctrl+Shift+1: number format with commas
• F2: edit a cell and see its references highlighted

If you're still reaching for the mouse to copy formulas across rows, fix that before test day. It's easily a few minutes saved.

What to Do If You're Running Out of Time

It happens. Here's how to triage:

• At minute 20 without a UFCF row? Skip formatting entirely and push to the share price.
• Short on time for WACC? Hard-code a placeholder like 10% in a clearly labeled blue cell, finish the model, then come back.
• No time for the sensitivity table? Fine. Write one line in a text box: "Next step: sensitize WACC and terminal growth." It shows you know what's missing.

A finished model with a flagged shortcut beats an unfinished one every time.

How to Practice This Week

1. Build this exact case from a blank sheet and check that you get $20.82.
2. Rebuild it timed. Aim for 30 minutes, then 25.
3. Change the inputs (try 12% growth in Year 1 or a 22% margin) and make sure everything flows through.
4. Explain it out loud in two minutes, as if the interviewer just walked in and asked, "Walk me through what you built."

Do this three or four times and the 30-minute DCF stops being scary. It becomes the part of the process where you can actually stand out.