Jason Zou
02Reverse DCFPart of the diligence pack

What a share price implies

A reverse DCF runs the DCF arithmetic backwards, from today's share price to the growth rate, exit multiple or margin path that would justify it. The workbook does this with four formula cells and one grid, so it recalculates with the model and needs no goal seek. Whether the implied assumptions are believable is still your judgement.

Part of
The stock diligence pack. Its market-implied block sits on the DCF tab of the workbook
Output
The DCF and Sensitivity tabs of the workbook, and the section of the assumptions memo on what the price implies
Tested on
Spotify, valuation date 4 September 2026
Model output · DCF tab, market-implied blockStreet case · ALL OK
What the $543.17 price assumes, holding the Street case cash flows (the case built on consensus forecasts)
3.6%implied perpetual growth, against 3.0% in the model
12.5ximplied year-ten EV/EBIT, against 11.3x from the model's own terminal value
LineStreet caseCell
Market enterprise value, which is the market value of the equity taken back to the balance sheet date, less the net cash in the bridge€85,990mDCF · C61
Present value of the explicit UFCF, FY2026 to FY2035€32,985mDCF · C21
Terminal value the price requires€127,807mDCF · C62
UFCF in FY2035, the terminal year€8,172mDCF · Q12
EBIT in FY2035€10,217mDCF · Q5
WACC, euro basis10.27%DCF · C22
Market-implied perpetual growth3.6%DCF · C63
Market-implied exit EV/EBIT, FY203512.5xDCF · C64
SourceSPOT_DCF.xlsx · DCF tab, Street case, euro millions unless stated; rebuilt from the plugin's input file and recalculated in LibreOffice Methodplain formulas that read the DCF tab's own cash flows, so the block recalculates with every input ReadingOn the Street cash flows the price needs 3.6% perpetual growth where the model assumes 3.0%, or a 12.5x year-ten EV/EBIT where the model's Gordon terminal value implies 11.3x. The gap is modest, since the Street case DCF itself comes out 5.2% below the price.

What the block says

Hold the Street case's ten years of cash flow fixed, and Spotify's $543.17 price only works if free cash flow keeps growing at 3.6% a year after 2035, where the model assumes 3.0%. Read as a multiple, the price pays 12.5x year-ten EBIT for everything beyond the forecast, against 11.3x in the model. The gap is modest, since the Street DCF sits 5.2% below the price.

The seven steps below show how the block is built, cell by cell.

How the reverse DCF is built

A forward DCF is a chain of formulas from assumptions to a value per share. A reverse DCF reads the same chain the other way. You fix the price, lock every link but one, and solve for the one you left open. Seven steps, using the Spotify workbook's own cells.

StepsBuilding the market-implied block · Spotify, Street case · € millionsStep 1 of 7
Read the chain
  1. Assumptionsg 3.0% · WACC 10.27%
  2. Cash flowsUFCF, FY2026 to FY2035
  3. Value todayEV €81,011m
  4. Equity€92,763m
  5. Per share$515.03
  6. Price$543.17, to compare

Start from a forward DCF that is all formulas

You need a linked forward model first. It holds ten years of unlevered free cash flow, a WACC and discount periods, a terminal method, the bridge from enterprise value to equity and a diluted share count, all as formulas. Spotify's workbook has 1,984 of them and no typed values, so anything read off it stays live when an input moves.

In the workbook
DCF · C29=C21+C28€81,011m
DCF · C47=C46*Inputs!$C$13$515.03

Turn the price into the number the model produces

The model values the operating business at the balance-sheet date, but the price is equity per share at the valuation date. So run the bridge backwards. The $543.17 price times 209.4m diluted shares is €97,830m in the model's currency. Divide by the 1.018 roll-forward to move back to 30 June 2026, then take off €10,122m of net cash. The market is paying €85,990m for the business the DCF values.

In the workbook
DCF · C61=Inputs!$C$20/C43-C41€85,990m

Choose what to hold fixed

A reverse DCF can free only one unknown, so everything else is locked. This block holds the ten forecast years fixed and releases the terminal assumption, because the usual question is what the price assumes about the long run. The ten years are worth €32,985m today. Whatever the market pays above that, €53,005m, has to be terminal value.

In the workbook
DCF · C21=SUM(H19:Q19)€32,985m

Undo the discounting, then rearrange the terminal formula

The terminal value sits 9.0 years out, so gross the €53,005m up at the WACC over the model's own terminal period. That gives €127,807m. The Gordon formula, TV = UFCF2035 × (1 + g) ÷ (WACC − g), now has only g unknown, so it rearranges to 3.6%. Divided by FY2035 EBIT, the same terminal value is 12.5x. Both are single cells, which is why there is no goal seek.

In the workbook
DCF · C62=(C61-C21)*(1+C22)^C27€127,807m
DCF · C63=(C62*C22-Q12)/(C62+Q12)3.64%
DCF · C64=C62/Q512.5x

Prove it by running forwards

Put 3.64% back into the forward chain and the value per share has to come out at exactly $543.17. At the model's own 3.0% it comes out at $515.03. If the round trip missed, the reverse read would be wrong. Move the slider to run the forward model at any growth rate.

The forward chain in one line
DCF · C47=((C21+C24/(1+C22)^C27+C41)*C43)/C45*Inputs!$C$13$515.03 at g = 3.0%

When there is no formula to rearrange, sweep

Revenue growth and margin sit inside the forecast, so value is not a simple expression of them and cannot be solved backwards. Grid 3 runs the forward calculation for 25 pairs of ten-year revenue growth and terminal margin, each cell a complete simplified DCF in one formula, and shades each against the price. The reverse reading is the line between the shaded cells. The nearest cell is $547.22 at 10% growth and a 28% margin.

One cell of grid 3
Sensitivity · E34=((SUMPRODUCT($B$26*(1+$A34)^$B$23:$K$23*(($B$27+(E$32-$B$27)*$B$23:$K$23/10)*(1-$B$28)+$B$29)*$B$24:$K$24*(1+DCF!$C$22)^(-$B$25:$K$25))+($B$26*(1+$A34)^10*(E$32*(1-$B$28)+$B$29))*(1+DCF!$C$23)/(DCF!$C$22-DCF!$C$23)/(1+DCF!$C$22)^DCF!$Q$17+DCF!$C$41)*DCF!$C$43)/DCF!$C$45*Inputs!$C$13$547.22

Read it against the model

Implied figures mean little on their own. Against the model's own 3.0% growth and 11.3x multiple, they say the price sits modestly above the Street case, whose DCF value is 5.2% below it. Every implied number is conditional on what was held fixed, which is why the block recalculates with the model. Whether 3.6% is believable for this business is still your call.

In the workbook
DCF · C55=C24/Q511.3x, the model’s own exit multiple
DCF · C49=C47/C48-1−5.2%

Every cell reference opens the workbook at that cell. The bear, management and bull cases produce their own implied growth and multiple from the same formulas.

02Try the gridFictional XMPL · simplified formula

Try it on a fictional company

XMPL is a made-up company with round-number inputs. The grid runs the same formula as grid 3 on the workbook's Sensitivity tab, without the stub period and the equity bridge. Move the price, the WACC or the terminal growth and see which growth and margin pairs still clear the price.

Grid 03XMPL_reverse_grid · fictional company · illustrative, not a run
Fictional XMPL, grid 3's formula simplified (no stub or bridge)
above the pricewithin 2% of itbelow itclosest to the pricepinnedCAGR* growth the price requires at that margin

Cells towards the lower right are worth more than the price, and cells towards the upper left less. Hover a cell to read it, click or press Enter to pin it, and use the arrow keys to move between cells.

Fixed inputs and sliders

Revenue
$1.0bn to start, growing at the column's CAGR for ten years
Margin
EBIT margin fades in a straight line from 12% to the row's terminal margin
Reinvestment
Capex runs at 2% of revenue above D&A, and working capital is ignored, as in grid 3
Tax
25% on EBIT
Shares
100m, no net debt, end-of-year discounting
Terminal
Gordon growth on year-ten unlevered free cash flow, at the slider's WACC and growth. Growth is held at least one point below the WACC
CAGR*
The revenue growth at which the cell exactly equals the price for that margin, found by narrowing in on it step by step in code (bisection)

The readout under the grid applies the market-implied block's four lines to whichever cell you pin. In the Spotify workbook the nearest cell to the price is $547.22 at 10% growth and a 28% terminal margin.

The four formulas

The market-implied block on the DCF tab reads the model's own cash flows and solves for growth by rearranging the Gordon formula. Because it is made of ordinary formulas, it recalculates whenever an input changes, in Excel and in LibreOffice.

Each line below is one cell on the DCF tab. The notes at the end of the page say why the workbook uses closed-form formulas rather than goal seek or data tables.

DCF tab · market-implied block
// what today's price assumes, holding the explicit forecast
Market EV = market cap ÷ roll-forward − net bridge
TV required = (Market EV − PV explicit UFCF) × (1 + WACC)t
implied g = (TV × WACC − UFCFN) ÷ (TV + UFCFN)
implied exit = TV required ÷ EBITN
// solves UFCF × (1+g) ÷ (WACC − g) = TV for g
// Spotify, Street case: 85,990 → 127,807 → 3.6% and 12.5x
02Rules and checksdcf_build.py · dcf-build-process.md

Modelling rules in the DCF builder

These rules are written into the builder script and its reference document, and each one can be seen at work in the Spotify workbook. Both files are in the plugin source viewer on the diligence page.

  • Interest on opening balances. Interest is calculated on the balance at the start of each year, so the model has no circular reference and needs no iteration setting.
  • SBC is a real cost by default. Stock-based compensation is left in as an expense. Adding it back is a toggle, and switching it on means diluting the share count for future grants by hand, because the model doesn't do that for you.
  • Diluted shares by the treasury stock method. The count is basic shares, plus in-the-money options net of the shares bought back at the strike price, plus all unvested RSUs.
  • Stub period, mid-year convention and roll-forward. The model uses all three so that its value can be compared like for like with today's price.
  • Discount rate in the cash-flow currency. Euro cash flows use a Bund-based risk-free rate.
  • The exit multiple is applied to year-ten EBIT. The exit terminal value is EBIT × EV/EBIT, 18x for Spotify. The reference document allows EV/EBITDA where D&A is large and non-cash, and EV/(EBITDA − capex) for heavy reinvesters, but neither is wired into the builder.
  • Each projection row is one formula copied across the forecast columns. The builder writes formulas, not typed-in values, and each input carries a note saying where it came from.
  • Grid 1's centre must equal the DCF value per share. Grid 1 runs WACC against terminal growth and grid 2 WACC against the exit multiple. The WACC axis follows the model's WACC, but the growth axis is fixed at 2% to 4% and the multiple axis at 12x to 24x, so the check holds only at 3% growth and 18x, as in Spotify's case.

The tool

  • Calibrating the Street case to consensus revenue and EPS, and recording the gap to management targets
  • Drafting the management, bull and bear cases as deviations from the Street case, each with a reason given
  • Solving implied growth and the implied exit multiple from the price
  • Building all three grids as live formulas, and checking that grid 1's centre equals the DCF value per share

You

  • Judging whether the implied growth and margin are achievable for this business
  • Reviewing and signing off the scenario deviations
  • Checking the memo's explanation of the gap between the Street DCF and the price

Notes

The XMPL grid

XMPL is a made-up company with round-number inputs, and its grid runs grid 3's formula without the stub period or the equity bridge. It shows how the calculation works and values nothing. The Spotify figures come from the rebuilt workbook and each links to its cell.

How it is checked

The Checks tab tests that grid 1's centre equals the DCF value per share, and the validation script tests that the exit-multiple toggle reproduces the DCF tab's exit value, $654.11 for Spotify. Grid 2 is not checked, and grid 3 is a simplified calculation labelled as one.

Closed form

Excel data tables cannot be written by openpyxl or evaluated by LibreOffice, and goal seek is a manual step. So each grid cell is a closed-form DCF in one formula, and the implied growth rate comes from rearranging the Gordon formula. Nothing needs a macro.