Modeldrill / Cheat sheet
Financial modeling formulas cheat sheet
The formulas behind a three-statement model and a DCF, written the way they sit in a real sheet: drivers in one column, every formula filling right, no hard-codes in the calculations. Each entry has the exact formula, the mistake people make, and a drill. Print the page or get the PDF by email.
The ones worth learning first
| What | How |
|---|
| Grow from last year | =C5*(1+C$3) (growth in a locked row) |
| Cost as a share of revenue | =C5*$B$7 |
| Straight-line depreciation | =cost/life or =SLN(cost, salvage, life) |
| Interest on the opening balance | =opening_debt*rate (opening = prior closing) |
| Tax only when profitable | =MAX(0, EBT)*rate |
| Working capital from days | =revenue/365*DSO |
| Unlevered free cash flow | =EBIT*(1-t) + D&A - capex - change in NWC |
| Discount factor | =1/(1+WACC)^period |
| Terminal value (Gordon) | =FCF_final*(1+g)/(WACC-g) |
| Loan payment | =PMT(rate/12, years*12, -principal) |
Model structure
| Inputs blue, formulas black, links green | Font color by cell type; one assumption, one cell |
| Drivers in one column, lock it | =C5*(1+$B$3) fills right; $B$3 never moves drill |
| One formula for a whole block | =C$5*$B7 row locked on top, column locked on the left drill |
| Timeline | =B2+1 for years; =EDATE(B2, 1) or =EOMONTH(B2, 1) for months |
| Flag a period | =IF(C2<=$B$4, 1, 0) then multiply, do not nest IFs |
| Check row | =ROUND(assets - liabilities - equity, 2) should be 0 |
| No hard-codes in calc rows | =ISFORMULA(C10) audit with Ctrl+` (show formulas) |
| Scenario switch | =CHOOSE($B$2, base, upside, downside) drill |
| Scenario by name | =INDEX(C4:E4, MATCH($B$2, $C$3:$E$3, 0)) drill |
Revenue and costs
| Grow revenue from one assumption | =B5*(1+$B$3) drill |
| Price times volume | =C3*C4 drill |
| Year over year growth | =C5/B5-1 drill |
| Cost as a share of revenue | =C5*$B$7 drill |
| Gross profit and margin | =C5-C6 =C7/C5 drill |
| Fixed plus variable opex | =$B$9+C5*$B$10 drill |
| EBITDA and margin | =C7-C8 =C9/C5 drill |
| Payroll from headcount | =C3*C4*(1+$B$6)^(year-1) drill |
| Subscribers month by month | =B4+C2-C3 (opening + adds - churn) drill |
| Churn | =B4*$B$5 |
| Revenue mix | =C5/SUM(C$5:C$7) drill |
| Break-even units | =fixed/(price-variable) drill |
Depreciation and fixed assets
| Straight-line | =SLN(cost, salvage, life) or =cost/life drill |
| From the year of purchase | =IF(C$2>=$B4, $C4/$D4, 0) stop after life ends drill |
| Waterfall of several assets | =SUMPRODUCT(($B$4:$B$9<=C$2)*(($B$4:$B$9+$D$4:$D$9)>C$2)*($C$4:$C$9/$D$4:$D$9)) |
| PP&E roll-forward | closing = opening + capex - depreciation drill |
| Capex as % of revenue | =C5*$B$12 |
| Declining balance | =DB(cost, salvage, life, period) or =DDB(...) |
Income statement to net income
| EBIT | =EBITDA - D&A |
| Interest on opening debtInterest on the average balance creates a circular reference; opening balance avoids it. | =B20*$B$21 (prior closing balance) drill |
| EBT | =EBIT - interest |
| Tax only when profitable | =MAX(0, C14)*$B$15 drill |
| Losses carried forward | NOL used =MIN(opening NOL, MAX(0, EBT)); taxable =MAX(0, EBT - NOL used) drill |
| Net income | =EBT - tax drill |
| Effective rate check | =tax/EBT |
Working capital and cash flow
| Receivables from days | =C5/365*$B$8 (DSO) drill |
| Inventory from days | =C6/365*$B$9 (DIO on COGS) |
| Payables from days | =C6/365*$B$10 (DPO on COGS) |
| Change in NWC | =(C11-B11) increase uses cash |
| Cash flow from operations | =net income + D&A - change in NWC drill |
| Cash roll-forward | closing cash = opening + CFO + CFI + CFF drill |
| Minimum cash | =MAX($B$3, cash before revolver) |
| Cash conversion cycle | =DSO + DIO - DPO |
Debt
| Loan payment | =PMT(rate/12, years*12, -principal) drill |
| Interest and principal split | =IPMT(rate, period, n, -principal) =PPMT(...) drill |
| Amortization table | closing = opening - principal paid; interest = opening*rate drill |
| Revolver draw or repay | =MAX(-opening_revolver, MIN(capacity - opening, min_cash - cash_before)) drill |
| Cash sweep | =MIN(opening_debt, MAX(0, cash_available - min_cash)) |
| Revolver and cash linked | cash after = cash before + draw; interest on opening drill |
| DSCRTest =IF(DSCR<covenant, "Breach", "OK"). | =CFADS/(interest + scheduled principal) drill |
| Net debt | =debt - cash |
| Leverage | =net debt/EBITDA |
DCF
| Unlevered free cash flow | =EBIT*(1-t) + D&A - capex - change in NWC drill |
| Discount factor | =1/(1+$B$2)^C3 drill |
| Mid-year convention | =1/(1+WACC)^(C3-0.5) switch with IF($B$4=1, ...) drill |
| Present value of each year | =C10*C11 |
| NPV with year 0 outsideNPV() discounts the first value one full period. | =NPV(rate, C5:G5)+B5 drill |
| IRR | =IRR(B5:G5) |
| Profitability index | =PV of inflows/initial outlay |
| Dated cash flows | =XNPV(rate, values, dates) =XIRR(values, dates) drill |
| Terminal value, perpetual growth | =G10*(1+$B$6)/($B$2-$B$6) drill |
| Terminal value, exit multiple | =G9*$B$7 (final EBITDA x multiple) drill |
| PV of terminal value | =TV*final discount factor |
| Enterprise to equity value | =EV - debt + cash then /shares drill |
| Implied multiple check | =TV/final EBITDA |
| WACC | =E/(D+E)*ke + D/(D+E)*kd*(1-t) |
| Cost of equity (CAPM) | =rf + beta*(rm-rf) |
Sensitivities and returns
| NPV across discount rates | =NPV(C$2, $B$5:$B$9)+$B$4 fill across a row of rates drill |
| Two-way grid | Row input and column input with a Data Table (What-If Analysis), or an explicit formula with mixed references drill |
| Terminal value grid | =FCF*(1+C$1)/($A2-C$1) growth across, WACC down drill |
| Payback period | =years before + unrecovered/cash that year drill |
| Discounted payback | same on the discounted cumulative line |
| CAC payback (months) | =CAC/(ARPU*gross margin) drill |
| Lifetime value | =ARPU*gross margin/monthly churn |
| Cohort payback | cumulative margin per cohort vs acquisition cost, month by month drill |
| MOIC and IRR for an exit | =exit equity/entry equity =IRR of the equity cash flows |
| CAGR | =(end/start)^(1/years)-1 |
Checks and shortcuts
| Balance check | =ROUND(total assets - total liabilities - equity, 2) |
| Cash check | balance sheet cash = cash flow statement closing cash |
| Trace | Ctrl+[ precedents, Ctrl+] dependents, F5 Enter back |
| Lock references | F4 cycles $B$1, B$1, $B1 |
| Fill | Ctrl+R right, Ctrl+D down, Ctrl+Enter into a selection |
| Evaluate a piece | Select part of the formula, F9, then Esc |
| Paste values | Ctrl+Alt+V, V, Enter |
| Freeze the timeline | Alt, W, F, F |
modeldrill.com: practice every one of these with instant checking. 84 entries.
Questions
Why interest on the opening balance?
Interest on the average balance depends on the closing balance, which depends on the cash sweep, which depends on interest: a circular reference. Opening balance keeps the model closed; if the difference matters, enable iterative calculation deliberately with a switch.
Why does NPV() give the wrong answer?
Excel's NPV discounts the first value by one full period, so the year-0 investment must sit outside the function: =NPV(rate, year1:yearN) + year0. XNPV with dates is safer.
Gordon growth or exit multiple?
Both, and compare. Perpetual growth needs g below the long-run economy's growth; the exit multiple needs a defensible comparable set. If the implied multiple from the Gordon method looks wrong, one of the inputs is.
How do I keep a model from breaking when I fill right?
Put every driver in a single locked column ($B$3) or a locked row (C$3), build each row as one formula and fill it across, and keep a check row at the bottom of each schedule that must stay at zero.
A cheat sheet does not make them stick
Doing them with instant checking does. The free exercises need no account.
Start practicing
Modeldrill