Returns and scenarios Updated November 2026

Sensitivity table

A sensitivity table recalculates one output (NPV, IRR, share price) for a grid of input values using Excel's Data Table (Data, What-If Analysis, Data Table). Put the output formula in the corner cell, the input values along the top row and left column, then point the row and column input cells at the assumption cells.

Syntax

Corner cell = output;  Data > What-If Analysis > Data Table > Row input cell / Column input cell  (creates {=TABLE(row, col)})

Part	What it is
`Row input cell`	The assumption cell that the values across the top replace (e.g. exit multiple)
`Column input cell`	The assumption cell that the values down the side replace (e.g. WACC)
`Corner cell`	A link to the output, e.g. =NPV_cell; the table fills the grid with that output

Examples

One-variable: NPV of -1000, 400, 400, 400 at discount rates 8%, 10%, 12%
Corner = NPV cell; column input = rate cell

Result: 30.84, -5.26, -39.27

Two-variable: profit = (price - 6) x volume - 1000
Top row prices 10, 12; left column volumes 400, 500

Result: price 10: 600, 1000; price 12: 1400, 2000

Table of terminal values by WACC (down) and growth (across)
Corner = TV cell; row input = g cell; column input = WACC cell

Result: a grid of TV values

Common mistakes

Input cells on another sheet: Data Table requires the row and column input cells on the same sheet as the table; link the assumptions through a cell on the table's sheet.
The table shows the same number everywhere: the corner cell does not reference the output, or calculation is set to Automatic except for data tables (press F9).
Typing over the TABLE array: it cannot be edited cell by cell; select the whole grid and delete, then rebuild.

Practice Sensitivity table

These drills check your answer on live data, free, no account needed.

NPV across discount ratesBuild a sensitivity row: the NPV of the cash flows in row 5 at each discount rate in row 7. Write B8 with NPV and fill right to F8.
Profit grid by price and volumeFill the grid B6:F9 with operating profit for every combination of price (column A) and units (row 5): (price less variable cost) times units, less fixed costs. One formula, filled right and down.
Terminal value grid by rate and growthShow how sensitive the growth-based terminal value is. Fill B5:E8 with the final cash flow in B2 grown once by the growth rate in row 4, divided by the discount rate in column A less that growth rate.

Questions

Why is my data table slow?

Each cell recalculates the whole model. Set Calculation to Automatic except for data tables while editing and press F9 when you need the table refreshed.

Can I build a sensitivity table without a Data Table?

Yes, if the output is a short formula you can write it directly with mixed references, e.g. =($B5-6)*C$4-1000, and copy it across the grid.

Scenario switchBuild a scenario selector so one cell switches the model between base, upside and downside: =CHOOSE(case, ...) or =INDEX(range, MATCH(name, names, 0)).
NPV in ExcelExcel NPV discounts every value one period, so the time zero investment goes outside the function: =NPV(rate, cash flows) + initial. Exact examples, errors.
Terminal valueTerminal value captures all cash flows after the forecast period. Perpetuity growth and exit multiple formulas with examples, and how to discount it right.
Break-even pointBreak-even units = fixed costs / (price - variable cost per unit); break-even revenue = fixed costs / contribution margin ratio. Examples, margin of safety.