Stock-Based Compensation in Excel: Build an SBC Schedule (2026)
Stock-based compensation at the median US-listed software company runs 18–22% of revenue — a figure that has roughly doubled in a decade and is now the single largest non-cash expense on most tech income statements. Yet most analyst-built DCF models still wave SBC through with a single percentage assumption and a vague "diluted shares" cell at the bottom of the page. That's where the valuation goes wrong. This guide walks through building a defensible stock-based compensation Excel model — a clean SBC schedule, a forecast that ties to grant data, a treasury-stock-method dilution build, and the correct treatment in your DCF. Every formula below is copy-paste ready for Excel 365 or Excel 2021.
What Is Stock-Based Compensation in a Financial Model?
Stock-based compensation (SBC) is the GAAP expense recognized when a company grants equity-linked instruments — restricted stock units (RSUs), performance shares (PSUs), stock options, and ESPPs — to employees. Under ASC 718, the grant-date fair value is amortized as compensation expense over the requisite service (vesting) period, hits the income statement as opex, and is added back as non-cash on the cash flow statement.
The reason analysts get SBC wrong is that it is two distinct modeling problems pretending to be one:
- An income-statement expense that compresses GAAP margins and reduces taxable income.
- A future-dilution event that increases the share count used in per-share metrics and in equity valuation.
Treat only the first and you'll overstate per-share value. Treat only the second and you'll double-count when you also add SBC back in unlevered free cash flow. The schedule below handles both cleanly.
💡 Pro Tip: SBC expense recognized in a given year is mostly from grants issued in prior years that are still vesting. Build your schedule by grant cohort and let Excel sum across the cohorts — do not just forecast SBC as one annual line.
What Should an SBC Schedule Include in Excel?
A defensible SBC schedule has three blocks on one tab. Keep them visually separated by a blank row and a header band so reviewers can navigate the page in seconds.
| Block | What It Tracks | Output Lines |
|---|---|---|
| Grant Cohort Schedule | Each grant year's notional value, vesting curve, forfeitures | Annual expense by cohort |
| Forecast Driver | Forward grants as % of revenue, % of opex, or $/FTE | Future cohort sizes |
| Dilution Bridge | Outstanding awards, vested, exercised, forfeited | Diluted shares for valuation |
A clean layout looks like this. Put it in a tab called SBC:
Rows 5–25: Historical and forecast grant cohorts (one row per cohort)
Rows 30–45: Forecast drivers (revenue, opex, headcount, grant assumptions)
Rows 50–70: Dilution bridge (BoP shares + grants − exercises − forfeits = EoP)
Rows 75–80: Output lines that flow to the income statement and shares model
The output rows are what the rest of your three-statement financial model or DCF model consumes. Everything else is supporting calculation.
graph TD
A[Grant Cohorts by Year] --> B[Vesting Schedule]
B --> C[Annual SBC Expense]
C --> D[Income Statement: Opex]
C --> E[Cash Flow: Add-back]
A --> F[Outstanding Awards]
F --> G[Treasury Stock Method]
G --> H[Diluted Share Count]
H --> I[Per-Share Valuation]
How Do You Forecast Stock-Based Compensation Expense?
You forecast SBC expense by projecting the dollar value of new annual grants — typically as a percentage of revenue, percentage of cash opex, or dollar amount per full-time employee — and then amortizing each grant ratably over its vesting period, usually four years for tech. Sum the amortization across all unvested cohorts to get the current-year expense.
The three forecast methods, in order of accuracy:
- % of revenue — Easiest, but mechanical. Works when SBC scales linearly with growth. Most tech companies disclose this ratio in their 10-K.
- % of cash operating expense — More defensible because SBC is fundamentally a people cost, not a revenue-driven cost.
- $/FTE × headcount forecast — Most accurate. Requires a separate headcount model but ties SBC to the underlying driver (people).
The Cohort Approach in Excel
Each row in your grant schedule represents one fiscal year's worth of new grants. Each column is a forecast year. Build the matrix so that a $100M grant in year 1 with four-year cliff-free ratable vesting shows $25M of expense in years 1, 2, 3, and 4.
=IF(AND(COLUMN()-COLUMN($B5)>=0, COLUMN()-COLUMN($B5)<$D5), $C5/$D5, 0)
Where $C5 is the cohort's total notional value, $D5 is the vesting period in years, and $B5 is the grant year. Drag this across the matrix and you have an automatically-amortizing SBC expense engine.
ℹ️ Note: Cliff vesting (e.g., 25% after one year, then monthly) and graded vesting produce slightly different expense curves. ASC 718 lets you choose straight-line or graded — most tech companies use straight-line over the requisite service period because it is simpler to forecast.
Sample Forecast Driver Block
| Row | Driver | Yr +1 | Yr +2 | Yr +3 | Yr +4 | Yr +5 |
|---|---|---|---|---|---|---|
| 30 | Revenue ($M) | 500 | 650 | 825 | 1,000 | 1,200 |
| 31 | SBC % of revenue | 22.0% | 21.0% | 20.0% | 19.0% | 18.0% |
| 32 | New grants ($M) | 110 | 136.5 | 165.0 | 190.0 | 216.0 |
| 33 | Vesting period (years) | 4 | 4 | 4 | 4 | 4 |
| 34 | Forfeiture rate | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% |
| 35 | Net cohort value | 104.5 | 129.7 | 156.8 | 180.5 | 205.2 |
The "Net cohort value" feeds row 5 onward in your cohort matrix. The forfeiture rate is the estimated percentage of grants that will lapse before vesting (turnover).
⚠️ Warning: Do not double-count forfeitures. ASC 718 allows companies to either estimate forfeitures up-front or recognize them as they occur. Check the 10-K — if the company recognizes forfeitures as they occur, your model should too, or your SBC expense will be biased low.
How Do You Model Dilution from Stock-Based Compensation?
You model SBC dilution by tracking outstanding awards (RSUs, options, PSUs) through a roll-forward, converting them to incremental shares using the treasury stock method for options and full-share-count for RSUs, then adding them to basic shares outstanding to derive diluted shares. Use that diluted count in any per-share metric or equity valuation. For companies with complex equity structures — multiple share classes, convertible instruments, and large option pools — a dedicated cap table model in Excel tracks the full dilution picture across all instruments at once.
Build the Dilution Bridge
Set up a single block from row 50 to row 70. One column per year. Eight rows tracking the moving parts.
| Row | Line | Formula Logic |
|---|---|---|
| 51 | Basic shares — BoP | Prior year EoP |
| 52 | + Net new grants (RSUs) | From cohort schedule × shares per $ |
| 53 | + Options exercised | Historical pattern or % of vested |
| 54 | − Buybacks | From treasury policy |
| 55 | − Forfeitures | Cohort × forfeiture % |
| 56 | Diluted shares — EoP | Sum above |
| 58 | Options outstanding | Roll-forward separately |
| 59 | Avg strike price | Weighted average |
The "shares per
quot; conversion in row 52 uses the company's average share price at the grant date. For a $100M RSU grant when shares trade at $40, the company is issuing 2.5M new shares (subject to vesting and forfeiture).=$C5/AVERAGE(SharePrice[GrantYear])
Treasury Stock Method for Options
For outstanding options that are in-the-money, only the incremental dilutive shares count. The treasury stock method (TSM) assumes the company uses option exercise proceeds to buy back shares at the average market price.
=MAX(0, OptionsOutstanding - (OptionsOutstanding * AvgStrike / AvgMarketPrice))
Example: 5M options outstanding at a $20 strike with the stock at $50 produces incremental dilution of 5M − (5M × $20 / $50) = 5M − 2M = 3M new shares. Add 3M to basic shares to get the option-dilution-adjusted diluted count.
LET-Powered Diluted Share Formula
For analysts on Excel 365, this single formula handles the entire diluted-share calculation including options under TSM, unvested RSUs, and a buyback offset:
=LET(
basic, BasicShares,
options, OptionsOutstanding,
strike, AvgStrike,
px, AvgMarketPrice,
rsus, UnvestedRSUs,
buyback, BuybackShares,
tsm, MAX(0, options - options*strike/px),
basic + tsm + rsus - buyback
)
Drop this into row 56 across your forecast columns and the diluted share count updates automatically as your grant and buyback assumptions change.
How Do You Treat Stock-Based Compensation in a DCF?
In a DCF model you can treat SBC two ways, and you must be consistent: either expense SBC in operating profit (the orthodox method) and divide enterprise value by fully diluted shares including future grants, or add SBC back to FCF (the cash-only method) and dilute the per-share value separately. Never add SBC back to FCF and also use today's basic share count — that is the most common SBC error in analyst DCFs.
The Two Methods Side by Side
| Treatment | EBITDA | Unlevered FCF | Equity Value Divided By |
|---|---|---|---|
| Method A: Expense SBC | Includes SBC in opex | No add-back | Fully diluted shares incl. future grants |
| Method B: Add back SBC | Excludes SBC | SBC added back | Basic shares only (you've already paid via FCF) |
| ❌ Wrong: Mix | Excludes SBC | Added back | Basic shares only |
Most sell-side desks and Aswath Damodaran have moved toward Method A because it captures the real economic cost of compensating employees with equity. Method B was popular before companies issued so much SBC that ignoring it materially overstated value.
graph LR
A[Revenue] --> B[Cash Opex]
B --> C[GAAP EBITDA]
C --> D[Less: SBC kept in expense]
D --> E[Operating Income]
E --> F[Unlevered FCF Method A]
F --> G[÷ Diluted Shares incl. future grants]
G --> H[Per-Share Value]
Building Future-Grant Dilution into Terminal Value
Method A requires you to estimate all future dilution through the terminal year, not just what is outstanding today. The clean approach is to dilute the share count by the same forecast growth rate used for SBC expense, then apply the terminal-year diluted count to your terminal value.
DilutedShares_Terminal = DilutedShares_Y5 * (1 + AnnualDilutionRate)^N
Where AnnualDilutionRate is typically 1.5–3.0% per year for mature tech companies and 4–7% for high-growth pre-profitability software.
💡 Pro Tip: Pull the historical net-dilution rate from each year's 10-K cash flow statement: "issuance of common stock for stock-based compensation" minus "repurchases of common stock." That gives you a clean number to forecast forward instead of guessing.
Common SBC Modeling Mistakes
These are the errors that show up in 80% of analyst-built SBC schedules — and the audit fixes that catch them.
Mistake 1: Treating SBC as a Pure Cash Add-Back
Adding SBC back to FCF without diluting future shares overstates equity value by 15–30% for typical tech companies. The SBC is a real economic cost — the company is paying employees with currency it manufactures, and that currency belongs to existing shareholders.
Mistake 2: Using a Flat SBC % of Revenue
SBC ratios decline as companies mature. Modeling Snowflake or Palantir at a constant 30% of revenue through year 10 implies the company never reaches operational efficiency. Build a fade schedule that brings SBC toward 8–12% of revenue at maturity for software, 4–7% for less SBC-heavy industries.
Mistake 3: Ignoring Forfeitures
A 5–10% annual forfeiture rate on outstanding RSU grants meaningfully reduces both expense and dilution. Skip this and your model overstates expense and dilution by roughly the same percentage.
Mistake 4: Forgetting the Tax Shield
SBC is tax-deductible when the award vests (RSUs) or is exercised (options), creating a real cash tax benefit. The excess tax benefit hits the cash flow statement under ASC 2016-09. Your model's tax rate should reflect this — many DCFs use a flat statutory rate and miss the SBC-driven cash tax savings.
⚠️ Warning: The tax benefit on SBC can be volatile. When the stock price drops below the grant-date fair value, the company gets a smaller deduction than the expense it recognized, creating a tax shortfall. Build your effective tax rate as an output of the SBC schedule, not a hard-coded assumption.
Mistake 5: Modeling Options and RSUs as the Same Thing
Options dilute via the treasury stock method (only the incremental spread). RSUs dilute one-for-one when vested. PSUs may never vest if performance targets miss. A single "SBC outstanding" line that lumps them together is wrong.
Worked Example: Forecasting SBC for a $500M ARR SaaS
Here is the full forecast for a hypothetical SaaS company growing from $500M to $1.2B in revenue with a fading SBC ratio.
| Year | Revenue ($M) | SBC % | Gross Grants ($M) | Forfeit (5%) | Net Grants ($M) | Amortized Expense ($M) | Net Dilution (M shares) |
|---|---|---|---|---|---|---|---|
| Y+1 | 500 | 22.0% | 110.0 | 5.5 | 104.5 | 75.0 | 1.8 |
| Y+2 | 650 | 21.0% | 136.5 | 6.8 | 129.7 | 92.0 | 2.1 |
| Y+3 | 825 | 20.0% | 165.0 | 8.3 | 156.8 | 113.0 | 2.5 |
| Y+4 | 1,000 | 19.0% | 190.0 | 9.5 | 180.5 | 130.0 | 2.7 |
| Y+5 | 1,200 | 18.0% | 216.0 | 10.8 | 205.2 | 148.0 | 2.9 |
Notice that the amortized expense in any given year lags the gross grants — because the grants vest over four years, year 1 only expenses 1/4 of the new grant plus the residual amortization from any prior cohorts already in the schedule. That is why a single-line SBC forecast as a percentage of revenue is always slightly off in the early years of any model where SBC ratios are changing.
Example: If your model starts with three years of historical cohorts already amortizing, your Year 1 expense is roughly: (1/4 × $104.5M) + (1/4 × prior-Y1 cohort) + (1/4 × prior-Y2 cohort) + (1/4 × prior-Y3 cohort) — a smoothed blend, not just 1/4 of the new year's grants.
Connecting the SBC Schedule to the Three Statements
Once the SBC tab outputs land cleanly, link them into your three-statement model with three simple flows:
- Income Statement — pull the amortized expense line into Operating Expenses (usually split across COGS, R&D, S&M, and G&A using a historical allocation ratio).
- Cash Flow Statement — add back the same number under "Adjustments to reconcile net income to operating cash flow" — but only if you are using Method B for valuation.
- Balance Sheet — debit Additional Paid-In Capital for the expense (when grants vest). The offset is the credit to compensation expense on the income statement.
Many AI-assisted modeling tools — including VeloraAI — can now generate SBC schedule formulas, vesting matrices, and TSM dilution formulas directly from the underlying grant data in natural language. That removes most of the mechanical setup so analysts can spend their time on the assumptions that actually move valuation — forecast SBC ratios, fade rates, and forfeiture estimates — rather than rebuilding the schedule by hand for every model.
Frequently Asked Questions
Is stock-based compensation a real expense in a DCF?
Yes. SBC is a real economic expense because the company transfers ownership of itself to employees, diluting existing shareholders. Treating it as non-cash and ignoring the dilution overstates equity value by 15–30% at typical tech-company SBC ratios. The correct method is to expense SBC in operating profit and divide by fully diluted shares including future grants, or add it back and dilute separately — but never both.
How do you forecast SBC as a percentage of revenue?
Pull 3–5 years of historical SBC from the cash flow statement and divide by revenue to get the base ratio. Then build a fade schedule that brings the ratio toward industry mature levels — 8–12% for enterprise software, 4–7% for diversified tech, 1–3% for non-tech. Apply the fade over the explicit forecast period so the terminal year reflects a steady-state company.
What is the treasury stock method for option dilution?
The treasury stock method assumes a company uses option exercise proceeds to repurchase shares at the average market price, so only the incremental dilutive shares count. The formula is MAX(0, Options − Options × Strike / Price). Out-of-the-money options produce zero dilution. Use TSM for options, ESPPs, and warrants; use full-share-count for RSUs and PSUs.
Should SBC be added back in unlevered free cash flow?
It depends on which valuation method you are using. Under Method A (expense and dilute), do not add back — SBC stays in operating expense and you use fully diluted shares. Under Method B (cash-only), add back SBC and use basic shares, then handle dilution separately in the equity bridge. Mixing the two — adding back and using basic shares — is the most common DCF error and overstates value.
What vesting period should I assume for new SBC grants?
For most US tech companies, assume four years of straight-line vesting with a one-year cliff for new-hire grants and ratable monthly vesting for refreshers. Performance-share units (PSUs) often have three-year cliffs with performance multipliers. Disclosed weighted-average vesting periods are in every 10-K under "Share-Based Compensation" footnotes — use those if available rather than guessing.
Next Steps
Once you have a clean SBC schedule, the highest-leverage next move is to stress-test it. Run a sensitivity table on SBC % of revenue and forfeiture rate and watch your per-share value move — for most software DCFs, a 5-percentage-point change in the SBC ratio shifts fair value by 10–15%. That single sensitivity will tell you whether your model is defensible or whether you have been hiding the most important valuation driver behind a single hard-coded cell.