Blog · Finance metrics and formulas · Finance and FP&A teams
DCF sensitivity analysis shows how selected discount-rate and terminal assumptions change a business valuation. Rebuild a small example, check the center cell, and compare outputs from an analyst-prepared workbook.
DCF sensitivity analysis shows how a business valuation changes when selected discount-rate or terminal assumptions change while the operating forecast stays fixed. Start with the analyst's approved workbook, reproduce its base value, and compare the surrounding cells against that reference. For acquisition planning, the useful result is an assumption-dependent range and a clear explanation of what drives it.
Ask the analyst for the valuation date, workbook version, forecast cash flows, timing convention, discount rate, terminal approach, and enterprise-to-equity bridge. Keep these with every exported scenario. A cell labeled only "value" cannot tell a reviewer whether debt has already been deducted.
For free cash flow to the firm, discounting uses the weighted average cost of capital. Free cash flow to equity uses the required return on equity; these are different approaches, as CFA Institute explains in its free cash flow valuation reading.
Use the sensitivity analysis definition for the broader concept. Here, keep the cash-flow forecast fixed and change only the assumptions named on the axes. A downside operating case that also changes revenue, margins, and investment needs is a separate scenario.
Consider a fictional US industrial-services acquisition. The analyst supplies the following annual free cash flows, in USD, with payments at each year-end. The valuation date is immediately before the first forecast year. The discount rate is 10.0%, terminal growth is 2.5%, and the forecast ends after year three.
Enterprise value = Sum of discounted forecast cash flows + Discounted terminal value
Terminal value at the end of year three = Year-three cash flow × (1 + Terminal growth) / (Discount rate − Terminal growth)
This simplified example assumes the terminal cash flow already reflects the investment needed to support its growth. In a working acquisition file, the analyst must substantiate that assumption.
Place discount rate in B1, terminal growth in B2, year numbers in A5:A7, and cash flows in B5:B7. Calculate present value in C5 and fill down:
=B5/(1+$B$1)^A5
| Forecast year | Cash flow (USD) | Present value at 10.0% (USD) |
|---|---|---|
| 1 | 1,000,000 | 909,090.91 |
| 2 | 1,200,000 | 991,735.54 |
| 3 | 1,400,000 | 1,051,840.72 |
| Total | 3,600,000 | 2,952,667.17 |
Keep full precision in the workbook. Currency in this table and the bridge below is displayed to cents; the sensitivity grid is rounded to whole dollars.
| Bridge step | Calculation | Amount (USD) |
|---|---|---|
| First terminal-year cash flow | 1,400,000 × 1.025 | 1,435,000.00 |
| Terminal value at year three | 1,435,000 / (0.10 − 0.025) | 19,133,333.33 |
| Present value of terminal value | 19,133,333.33 / 1.10^3 | 14,375,156.52 |
| Enterprise value | 2,952,667.17 + 14,375,156.52 | 17,327,823.69 |
Enter the terminal value in B9, its present value in B10, and enterprise value in B11:
B9: =B7*(1+B2)/(B1-B2)
B10: =B9/(1+B1)^A7
B11: =SUM(C5:C7)+B10
The terminal component represents 14,375,156.52 / 17,327,823.69 = 82.96% of enterprise value, rounded to two decimal places. That makes the terminal assumptions a central review question.
Use the same cash-flow rows for every cell. Put growth assumptions across F4:H4 and discount rates down E5:E7. In F5, enter this formula and fill across and down:
=SUMPRODUCT($B$5:$B$7,1/(1+$E5)^$A$5:$A$7)+($B$7*(1+F$4)/($E5-F$4))/(1+$E5)^$A$7
| Discount rate | Growth 2.0%: value (USD) | Growth 2.5%: value (USD) | Growth 3.0%: value (USD) |
|---|---|---|---|
| 9.0% | 18,761,047 | 20,055,939 | 21,566,647 |
| 10.0% | 16,363,636 | 17,327,824 | 18,429,752 |
| 11.0% | 14,500,086 | 15,242,747 | 16,078,240 |
The center cell is the base enterprise value rounded to whole dollars. Across a row, higher terminal growth increases value. Down a column, a higher discount rate reduces value for these positive cash flows.
The lowest displayed value is $14,500,086 and the highest is $21,566,647. Their difference is $7,066,561. This is the range generated by the selected assumptions; it carries no probability or confidence level.
Excel also supports two-variable data tables, which Microsoft documents as a way to vary two inputs to one formula. For the menu workflow and the distinction between complete scenarios and sensitivities, use scenario analysis in Excel. Covirage is not affiliated with Microsoft.
First, the center grid cell must equal B11 before display rounding. Use a small currency tolerance to accommodate spreadsheet precision:
=ABS(G6-$B$11)<0.01
Second, verify that the base present values plus the discounted terminal value equal enterprise value. A mismatch often means the terminal value was discounted for the wrong number of periods or included twice.
Third, reconcile the enterprise-to-equity bridge separately. Assume the file supplies excess cash of $500,000 and debt of $3,000,000, with no other bridge items. Base equity value is $17,327,823.69 + $500,000 − $3,000,000 = $14,827,823.69. Apply the same bridge to every scenario before comparing equity values.
Give the acquisition committee both the table and an explanation of which assumptions require more evidence. A large terminal share may justify closer review of sustainable margins and reinvestment. A wide discount-rate range calls for scrutiny of the analyst's risk assumptions. For operating investment cases, cost-benefit sensitivity analysis addresses a different decision.
Bring a file of calculated valuation outputs with their discount-rate, terminal-assumption, and scenario columns from the analyst-prepared workbook. Covirage maps the columns, its tools compute and check comparisons between supplied outputs, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export; creating the account takes about a minute.
See Covirage for Excel analysis for the supporting file-analysis workflow.
Use assumptions that directly affect the selected valuation formula, commonly the weighted average cost of capital and terminal growth rate. Label both axes clearly and identify the base assumptions. An exit-multiple approach needs a different terminal assumption axis.
No. The endpoints depend on the assumptions chosen for the table, and the valuation formula is nonlinear. Use the approved base case as the reference, then explain the evidence supporting the surrounding assumptions rather than averaging the extreme cells.
Terminal growth affects both the next year's cash flow and the denominator of a perpetuity calculation. When growth approaches the discount rate, the denominator shrinks. Review whether the terminal cash flow, reinvestment requirements, and long-term growth assumption fit together.
Bring calculated scenario outputs and their assumption columns from your analyst-prepared file. Covirage maps the columns, its tools compute and check comparisons, and the external AI model explains the results with figures traced to their rows.