DCF model in Excel with Claude or ChatGPT: build and stress-test it

A discounted cash flow model can be built in Excel with Claude for Excel or ChatGPT for Excel in a few prompts, but the valuation is only usable once a person has checked three things the assistant can get wrong: the discount factor applied to each year, the year in which the terminal value is discounted, and whether the growth rate is applied to the final forecast cash flow before the terminal value is calculated. In the illustrative five-year model used below, a terminal value discounted one year too late understates enterprise value by USD 135.4 million, about 6% of the correct figure of USD 2,183.1 million.

Both assistants now work inside the workbook. Anthropic documents Claude for Excel as an add-in for its Pro, Max, Team and Enterprise plans that can build or populate models, explain formulas with cell-level citations and change assumptions while keeping formula relationships intact. OpenAI documents ChatGPT for Excel as generally available across its plans since May 5, 2026, with calculations running in Excel and a permission step before the assistant edits a workbook. The product documentation for both tools was verified on November 3, 2026.

The sections below set out the documented requirements and limits of each tool, a worked example with prompts, the correct output computed independently, and a stress test of the weighted average cost of capital (WACC) and the terminal growth rate. Readers who want the valuation theory first can start with the site's explanation of discounted cash flow, its formula and its Excel set-up.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣

WHY FINANCE TEAMS BUILD AND STRESS-TEST DCF MODELS.

A DCF model estimates the value of a business or a project as the present value of the cash flows it is expected to generate. Corporate development teams use it to price acquisitions, controllers and FP&A teams use it for impairment testing and capital allocation, and lenders use a version of it to judge whether projected cash flows can service debt. The arithmetic is simple. The difficulty lies in the assumptions, because a small change in the discount rate or in long-term growth can move the result by a large amount.

For that reason a DCF is rarely presented as a single number. Reviewers expect a sensitivity table showing how value changes when WACC and terminal growth move within a plausible range, and they expect to see how much of the total value depends on the terminal value. When the terminal value accounts for most of enterprise value, as it often does, the long-term growth and discount-rate assumptions carry most of the risk in the model.

AI assistants are useful here in two ways. They can lay out a clean model structure quickly from a description of the forecast, and they can explain an inherited model formula by formula. Both tasks still leave the reviewer responsible for the mechanics and for every assumption, which is why the worked example below gives the expected result in full.

··········

CLAUDE FOR EXCEL AND CHATGPT FOR EXCEL: DOCUMENTED REQUIREMENTS FOR DCF WORK.

The table summarizes what each vendor's documentation states. Anything not listed was not found in the official pages and should be confirmed with the vendor before relying on it.

........

Item	Claude for Excel	ChatGPT for Excel
Plans	Pro, Max, Team, Enterprise	Generally available across all plans since May 5, 2026
Admin control	Deployable by admins through the Microsoft 365 Admin Center	Off by default in Enterprise, Edu and Teacher workspaces until an admin enables it
Excel versions	Excel on the web, Windows (Microsoft 365), Mac 16.46 or later	Not detailed on the pages reviewed
Model building	Builds new models or populates templates	Creates or updates models, including scenario analysis
Traceability	Cell-level citations in answers	Links answers to the cells it references and updates
Edit safeguards	Warns before overwriting data; asks confirmation for risky operations	Asks permission before making changes; edits can be undone
Documented gaps	Excel data tables, macros and VBA unsupported	Complex formulas or edge cases may need manual refinement

........

The gap in the Claude column has a direct effect on DCF work. Anthropic lists Excel data tables among unsupported capabilities, and the standard way to build a two-variable sensitivity grid in Excel is the What-If Analysis data table. With Claude for Excel, the practical route is to ask for the grid as ordinary formulas, one valuation per cell, or to build the data table yourself through Data, What-If Analysis, Data Table, as documented by Microsoft. Microsoft also notes that workbooks can be set to recalculate automatically except for data tables, in which case the table is refreshed with F9, a setting worth checking before trusting a sensitivity grid in any shared model.

For teams that prefer to work outside Excel, ChatGPT's data analysis feature reads uploaded .xlsx and .csv files and runs Python with pandas in a sandbox. OpenAI documents limits of 10 files per conversation and spreadsheets of roughly 50 MB, and states that the code environment cannot make outbound network requests. The result in that case is a calculation outside the workbook, which then has to be rebuilt in Excel if the model is meant to stay live.

··········

WORKED DCF EXAMPLE: INPUTS AND PROMPTS.

The example uses an illustrative company with five years of forecast unlevered free cash flow. All figures are invented for the exercise and are in USD millions, except the share count.

........

Input (illustrative)	Value
Unlevered free cash flow, years 1 to 5	120, 132, 143, 152, 160
WACC	9.0%
Terminal growth rate	2.5%
Cash flow timing	End of each year
Net debt	400
Diluted shares outstanding	50 million

........

The first prompt asks for the structure and fixes the conventions that most often go wrong. Stating the timing convention and the terminal-value method explicitly removes the two choices an assistant would otherwise make silently.

Build a DCF on a new sheet called "DCF" using the inputs in A1:B8.
Use end-of-year discounting: discount factor for year t = 1/(1+WACC)^t.
Terminal value = FCF year 5 x (1 + g) / (WACC - g), discounted with
the year 5 discount factor. Show PV of each year, PV of terminal value,
enterprise value, equity value (EV minus net debt) and value per share.
Keep every input in a labelled cell and reference it; no hard-coded numbers
inside formulas.

The second prompt asks for the sensitivity grid. With Claude for Excel it requests plain formulas because data tables are not supported; with ChatGPT for Excel the same wording works and also produces a grid that can be audited cell by cell.

Add a sensitivity grid of value per share: WACC 8.0%, 9.0%, 10.0% in rows,
terminal growth 2.0%, 2.5%, 3.0% in columns. Build each cell as a full
formula that recomputes the DCF for that pair (no Excel data table).
Then report terminal value as a percentage of enterprise value and the
implied terminal multiple of year 5 free cash flow.

A third prompt is useful on any model the assistant did not build, such as one received from a target company or an adviser.

Trace how the value per share is calculated. List every input it depends on,
the discount factor used for each forecast year and for the terminal value,
and flag any hard-coded number inside a formula.

··········

[[ADPLUS_300x250_1]]

EXPECTED DCF RESULT TO CHECK THE AI OUTPUT.

The figures below were computed independently in Python with the same inputs and conventions. If the workbook built by the assistant shows different numbers, one of the formulas differs from the specification in the first prompt.

........

Year	FCF	Discount factor at 9.0%	Present value
1	120.0	0.9174	110.1
2	132.0	0.8417	111.1
3	143.0	0.7722	110.4
4	152.0	0.7084	107.7
5	160.0	0.6499	104.0
Total			543.3

........

The terminal value is 160 × 1.025 / (0.09 − 0.025), which equals 2,523.1. Discounted with the year 5 factor of 0.6499, it has a present value of 1,639.8. Enterprise value is therefore 543.3 + 1,639.8 = 2,183.1, and the terminal value represents 75.1% of it. Deducting net debt of 400 gives equity value of 1,783.1, or USD 35.66 per share on 50 million shares. The terminal value equals 15.77 times year 5 free cash flow, a multiple that can be compared with trading multiples of similar companies as a reasonableness check.

........

Value per share (USD)	g = 2.0%	g = 2.5%	g = 3.0%
WACC 8.0%	40.19	43.76	48.03
WACC 9.0%	33.17	35.66	38.57
WACC 10.0%	27.91	29.73	31.81

........

The grid shows how sensitive the valuation is to the two long-term assumptions. Moving WACC by one percentage point in either direction at 2.5% growth changes value per share from USD 35.66 to USD 43.76 or USD 29.73, a range of about 23% upward and 17% downward. A one-point change in WACC moves value far more than a half-point change in growth, which is consistent with the terminal value carrying three quarters of enterprise value. The inputs behind the discount rate deserve as much review as the forecast itself; the site's article on calculating and applying WACC in corporate decisions covers how each component is estimated.

··········

DCF ERRORS AI ASSISTANTS MAKE AND HOW TO CATCH THEM.

A common mechanical error is discounting the terminal value over six years instead of five. The terminal value calculated from year 6 cash flow is a value at the end of year 5, so it takes the year 5 discount factor. In the example, using the year 6 factor gives enterprise value of 2,047.7 and USD 32.95 per share, an understatement of 135.4 in enterprise value. The quickest check is to compare the discount factor next to the terminal value with the one next to year 5: they should be identical.

The second error is omitting the growth step. Dividing year 5 cash flow directly by (WACC − g) values the perpetuity as if growth started a year later. In the example this gives enterprise value of 2,143.1 and USD 34.86 per share. The check is to look for the factor (1 + g) in the terminal value formula.

The third concerns Excel's NPV function. Microsoft's documentation states that NPV assumes cash flows are equally spaced and occur at the end of each period, and that a cash flow at the start of the first period must be added outside the function. An assistant that passes a year 0 amount into NPV, or that uses NPV on a model built with a mid-year convention, produces a result discounted by one period too many or too few. When the model uses a mid-year convention, which some practitioners apply to reflect cash arriving through the year, the terminal value convention has to be chosen consistently, and that choice should be written into the prompt and the model notes.

Four further points require human review before the model is used.

Growth rate against WACC. The Gordon growth formula only works when growth is below WACC; a grid that includes a growth rate close to the discount rate produces implausibly large values and should be rejected.

Hard-coded numbers. An assistant may type a computed value into a cell instead of a formula. The third prompt above asks for these to be flagged, and Excel's own formula auditing tools can confirm it.

Net debt definition. Whether leases, pension deficits, minority interests or excess cash belong in the bridge from enterprise to equity value is a judgement the assistant cannot make from the workbook alone.

Terminal value share. A terminal value equal to three quarters of enterprise value, as in the example, means the result depends mostly on the long-term assumptions; the site's overview of how terminal value is determined explains the alternative exit-multiple method used as a cross-check.

Anthropic states that Claude for Excel is not recommended for final client deliverables without human review or for audit-critical calculations without verification. OpenAI notes that complex formulas or edge cases may still require manual refinement. In practice the assistant prepares the model and a person signs off the conventions, the inputs and the result.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁤

DATA HANDLING WHEN A DCF MODEL GOES TO CLAUDE OR CHATGPT.

Valuation models often contain non-public forecasts and deal information, so the data terms belong in the decision. Anthropic states that when a user signs in with a Claude account, the add-in sends conversations to Anthropic, including the workbook content Claude reads and attached files; inputs and outputs are deleted on the backend within 30 days except in the cases its privacy documentation describes. Chat history is stored locally in the browser, Claude for Excel does not inherit custom data retention settings an organization may have set, and its activity is not included in Enterprise audit logs, although sessions are included in the Compliance API for Enterprise organizations that have it enabled.

Anthropic also warns that external files such as downloaded templates and vendor files can contain hidden instructions intended to manipulate the add-in, and advises using it only with trusted spreadsheets. A model received from a counterparty in a transaction falls squarely into that category, so it is safer to copy the numbers into a clean internal template before asking either assistant to work on it.

On the OpenAI side, Enterprise, Edu and Teacher administrators decide which users can access ChatGPT for Excel. For uploaded files in ChatGPT, OpenAI documents that files are retained according to the chat's retention period and deleted from its systems within 30 days after the chat, account or custom GPT is deleted, subject to the exceptions it lists. Teams that compare the two assistants on wider spreadsheet work can find a side-by-side test in the site's comparison of ChatGPT and Claude for spreadsheet analysis.

··········

DCF MODELS BUILT WITH CLAUDE OR CHATGPT: WHAT THE REVIEWER SIGNS OFF.

Claude for Excel and ChatGPT for Excel can both produce a working DCF from a structured prompt, explain the formulas with references to specific cells and recompute value when an assumption changes. The documented differences are practical: Claude for Excel cannot create Excel data tables, so its sensitivity grids need ordinary formulas, while ChatGPT for Excel is available on every ChatGPT plan but is switched off by default for Enterprise, Edu and Teacher workspaces.

What neither documentation covers is the valuation judgement. The discount rate, the terminal growth rate, the timing convention, the components of net debt and the reasonableness of the implied terminal multiple stay with the reviewer. A short control routine covers most of the mechanical risk: compare the assistant's output with an independently calculated result such as the one above, confirm that the terminal value uses the final forecast year's discount factor and includes the growth step, check that growth stays well below WACC across the sensitivity grid, and look for hard-coded values before the model is circulated.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org