Deferred Tax in Excel: Build a DTA/DTL Schedule (2026)
Roughly one-in-three financial models on a mid-market deal ships with a broken deferred tax roll — that's the single most common audit finding when a Big Four team re-performs a target model in a Q of E. A deferred tax model in Excel is not optional accounting decoration; it is the mechanism that keeps effective tax rate, cash taxes, and after-tax free cash flow in agreement across the three statements. Get it wrong and every downstream metric — DCF value, credit ratios, LBO returns — moves.
This guide builds a full deferred tax schedule from the ground up: opening balance, book-tax reconciliation, temporary vs permanent differences, DTA and DTL roll-forwards, NOL carryforwards under the 80% limitation, valuation allowance logic, and clean integration into a three-statement financial model. Every formula runs in Excel 365 and Excel 2021.
What Is a Deferred Tax Model?
A deferred tax model tracks the timing differences between when an item hits the income statement (book) and when it hits the tax return (tax). Those timing gaps become deferred tax assets (future deductions) or deferred tax liabilities (future taxes owed), measured at the enacted tax rate and rolled forward each period.
The mechanic is straightforward once you internalize the identity: book income tax expense = current tax + change in deferred tax. Anything that widens a temporary difference this year lands in the deferred tax movement, and anything that narrows one reverses out. Permanent differences never touch the deferred schedule — they only affect the effective tax rate.
Why This Belongs in Every Serious Model
- The cash tax vs book tax split drives free cash flow. Miss it and your DCF discounts the wrong number.
- Deferred taxes create a non-cash add-back on the cash flow statement — without them, CFO does not tie.
- NOL carryforwards are one of the largest hidden assets in loss-making targets; unmodeled NOLs undervalue the deal.
- ASC 740 requires disclosure of the reconciliation between statutory and effective rate — analysts working off 10-Ks need to reverse-engineer it.
ℹ️ Note: This guide follows U.S. GAAP (ASC 740) and IFRS (IAS 12) conventions. The mechanics are identical; the presentation differs — IFRS nets DTA/DTL by taxable entity, U.S. GAAP presents them net on the balance sheet as a non-current item after ASU 2015-17.
How Do Deferred Tax Assets and Liabilities Arise?
Deferred tax assets and liabilities arise from temporary differences between book carrying values and tax bases — depreciation timing, revenue recognition timing, accrued expenses, and NOL carryforwards. A DTL means you will owe more tax later (book income exceeded taxable income); a DTA means you will pay less tax later (taxable income exceeded book income). Both reverse over time.
The Two Difference Types
| Difference Type | Reverses? | Hits Deferred Schedule? | Example |
|---|---|---|---|
| Temporary | Yes | Yes | Accelerated tax depreciation vs straight-line book |
| Permanent | No | No | Tax-exempt muni interest, non-deductible fines |
| Timing (legacy term) | Yes | Yes | Same as temporary — pre-ASC 740 vocabulary |
| Carryforward | Yes | Yes (as DTA) | NOLs, tax credits, capital loss carryforwards |
| Purchase price step-up | Depends | Yes | Asset write-up in taxable vs non-taxable acquisition |
The Sign Convention That Trips People Up
The direction of the difference matters more than the sign of the deferred balance:
- Book > Tax income → you deferred paying tax → DTL on the balance sheet.
- Tax > Book income → you prepaid tax → DTA on the balance sheet.
Accelerated depreciation is the canonical DTL: your tax deduction is larger than your book expense in the early years, so taxable income is lower than book income, so you paid less cash tax than book tax expense implies — the difference sits as a DTL until the book depreciation catches up.
💡 Pro Tip: Build the schedule around cumulative temporary differences, not annual movements. The DTL at any date equals cumulative temporary difference × tax rate. Working from cumulative balances makes it trivial to catch sign errors — the cumulative gap should hit zero at the end of the asset's life.
How Do You Build a Deferred Tax Schedule in Excel Step by Step?
Build the schedule in six blocks: (1) timeline and rate inputs, (2) book vs tax income reconciliation, (3) cumulative temporary differences, (4) DTA/DTL calculation, (5) NOL carryforward tracker, (6) valuation allowance. Each block feeds the next, and the final DTA and DTL balances flow to the balance sheet with the annual change flowing to the income statement and cash flow statement.
graph TD
A[Book Income Before Tax] --> B[Add/Subtract Permanent Differences]
B --> C[Add/Subtract Temporary Differences]
C --> D[Taxable Income]
D --> E[Apply NOL Carryforward]
E --> F[Current Tax Payable]
A --> G[Book Tax Expense = Book Income x Rate]
G --> H[Deferred Tax Movement = Book Tax - Current Tax]
H --> I[Roll Forward DTA/DTL Balances]
I --> J[Apply Valuation Allowance]
J --> K[Net DTA/DTL to Balance Sheet]
Step 1: Set Up the Timeline and Rate Inputs
Reserve rows 1–8 for inputs. Every rate that could change during the forecast belongs here — do not hard-code rates into formulas.
Row 3: Federal statutory rate 21.0%
Row 4: State blended rate (net) 4.0%
Row 5: Combined effective rate 25.0% =C3+C4*(1-C3)
Row 6: NOL utilization cap 80.0% Post-TCJA limit for post-2017 NOLs
Row 7: NOL carryforward life Indef Post-2017 NOLs; pre-2018 = 20 years
Row 8: Enacted rate change year 2028 Optional scenario switch
Row 5's state formula is the U.S. GAAP standard: state tax is deductible federally, so the combined rate is not a simple sum. If you are running a UK, Canadian, or multi-jurisdictional model, replace with the appropriate blended calculation.
Step 2: Reconcile Book Income to Taxable Income
Below the inputs, build the reconciliation. Every temporary and permanent difference gets its own row so you can trace what created the deferred balance.
2024A 2025E 2026E 2027E
Book pretax income 100,000 115,000 132,000 151,800
+ Depreciation (book) 20,000 22,000 24,200 26,620
- Depreciation (tax MACRS) (35,000) (28,000) (20,000) (16,000)
+ Non-deductible fines 500 - - -
+ Meals & entertainment (50%) 800 850 900 950
- Tax-exempt muni interest (1,200) (1,200) (1,200) (1,200)
+ Stock-based compensation 8,000 9,000 10,000 11,000
- SBC tax deduction (exercised) (12,000) (7,500) (6,000) (5,000)
+ Warranty accrual 3,000 3,300 3,600 4,000
- Warranty paid (2,500) (3,000) (3,300) (3,700)
Taxable income before NOL 81,600 110,450 140,200 168,470
The formula in the taxable income row is a straight SUM of everything above it:
=SUM(C11:C21)
Split the reconciliation into two blocks for clarity — permanent differences that never reverse (fines, muni interest, M&E) and temporary differences that do (depreciation gap, SBC gap, warranty gap). The two blocks reconcile differently on the deferred schedule.
Step 3: Build the Cumulative Temporary Difference Table
For each temporary difference, track the cumulative gap between book and tax carrying value. This is the number that gets multiplied by the tax rate to produce the deferred balance.
Temporary Difference Opening +Book -Tax Closing
Depreciation gap 50,000 (15,000) 35,000
=D_book - D_tax
SBC deduction timing (8,000) 8,000 (12,000) (12,000)
Warranty accrual timing 2,000 3,000 (2,500) 2,500
The formula for the closing cumulative difference in a single cell (using a LET for readability):
=LET(
opening, C25,
book_amount, D26,
tax_amount, D27,
opening + book_amount - tax_amount
)
Example: If you started with a $50k cumulative depreciation gap (tax > book by $50k), took $20k of book depreciation this year and $35k of tax depreciation, the closing gap is $50,000 - $20,000 + $35,000... wait — signs matter. Track book added and tax deducted consistently. The simpler mental model: cumulative gap this year = cumulative gap last year + (tax deduction − book deduction).
Step 4: Calculate the DTA and DTL
Multiply each cumulative temporary difference by the effective tax rate. Positive cumulative differences that reduced this year's taxable income (accelerated tax deductions) become DTLs. Negative cumulative differences (book deductions the taxman has not yet allowed) become DTAs.
Closing gap Rate Balance Classification
Depreciation 35,000 25% 8,750 DTL
SBC deduction timing (12,000) 25% (3,000) DTA
Warranty accrual 2,500 25% 625 DTA (book expensed early)
NOL carryforward (18,400) 25% (4,600) DTA
Formula in the balance column:
=ROUND(C36*$C$5, 0)
Split the total into DTA and DTL columns using SUMIF against the classification tag — U.S. GAAP presents them net, but you should track them separately for disclosure and analysis:
DTA gross =SUMIFS($F$36:$F$40, $G$36:$G$40, "DTA")
DTL gross =-SUMIFS($F$36:$F$40, $G$36:$G$40, "DTL")
Step 5: Add the NOL Carryforward Tracker
NOLs are DTAs by nature — they represent future tax deductions. Post-TCJA (2018+) NOLs carry forward indefinitely but can only offset 80% of taxable income in any year.
2024A 2025E 2026E 2027E
Opening NOL balance 25,000 18,400 0 0
+ NOL generated this year 0 0 0 0
- NOL utilized (6,600) (18,400) 0 0
Closing NOL balance 18,400 0 0 0
NOL utilization cap check:
Taxable income before NOL 81,600 110,450 140,200 168,470
80% cap on utilization 65,280 88,360 - -
Available NOL 25,000 18,400 - -
NOL used = MIN(cap, avail, +TI) 6,600 18,400 - -
The NOL utilized formula enforces both the 80% cap and the balance available:
=MIN(C52*$C$6, C50, MAX(C52, 0))
Where C52 = taxable income before NOL, C6 = the 80% cap input, and C50 = the opening NOL balance. The outer MAX(C52, 0) prevents utilization when taxable income is negative (you cannot use an NOL against a loss — it just adds to the NOL balance).
⚠️ Warning: The 80% limit applies only to post-2017 NOLs. If a target has pre-2018 NOLs, they can offset 100% of taxable income but expire 20 years after generation. In a purchase, track the two vintages separately and use the pre-2018 NOLs first — expiration risk is higher.
Step 6: Apply the Valuation Allowance
A valuation allowance is a contra-DTA that reduces gross DTAs to the amount management believes is more likely than not (>50% probability) to be realized. Under ASC 740-10-30, you evaluate positive and negative evidence — a history of losses is negative evidence that typically requires a full valuation allowance against most DTAs.
Model the valuation allowance as a simple percentage-of-DTA input with an override flag:
2024A 2025E 2026E 2027E
Gross DTA 10,225 8,900 6,200 4,100
Valuation allowance % 100% 50% 0% 0%
Valuation allowance amount (10,225) (4,450) - -
Net DTA - 4,450 6,200 4,100
Formula for the valuation allowance amount:
=-ROUND(C60*C61, 0)
For a more sophisticated model, drive the allowance percentage from a scoring formula that weights recent profitability, forecast profitability, and NOL expiration risk:
=IF(SUMPRODUCT(C58:E58) < 0, 100%,
IF(F58 < 0, 50%,
IF(F58 > 0, 0%, 25%)))
Where the range represents the trailing three years of pretax income. Negative trailing income triggers full allowance; positive current year and forecast triggers zero allowance.
What Is the Difference Between DTA and DTL?
A deferred tax asset (DTA) represents future tax deductions or refunds — you have paid tax on income the book world has not yet recognized, or you have book expenses the tax world has not yet allowed. A deferred tax liability (DTL) represents future tax payable — you took a tax deduction the book world has not yet expensed, or you recognized book income the tax world has not yet taxed.
| Attribute | Deferred Tax Asset (DTA) | Deferred Tax Liability (DTL) |
|---|---|---|
| Direction | Future tax benefit | Future tax cost |
| Origin | Tax income > book income | Book income > tax income |
| Balance sheet | Asset | Liability |
| Valuation allowance | Applies (reduces DTA) | Does not apply |
| Realization test | More-likely-than-not | Recorded at gross |
| Typical driver | Warranty accruals, SBC, NOLs | Accelerated depreciation, unrealized gains |
| Reverses when | Book expense catches tax deduction | Book expense catches tax deduction |
| Cash impact | Reduces future cash taxes | Increases future cash taxes |
The Netting Rule That Matters for Presentation
Under ASU 2015-17, U.S. GAAP requires all DTAs and DTLs to be classified as non-current and netted by tax jurisdiction. You cannot net a U.S. federal DTA against a UK DTL, but you can net a U.S. federal DTA against a U.S. federal DTL. Build your model with jurisdiction tags on each row if the target operates in multiple countries.
How Does the Deferred Tax Schedule Flow Into the Three Statements?
The deferred tax schedule touches all three statements: closing DTA and DTL balances land on the balance sheet, the annual change flows to income tax expense on the income statement, and deferred tax expense is added back on the cash flow statement as a non-cash item. Wire the three connection points and the CFO ties by construction.
graph LR
A[Deferred Tax Schedule] --> B[Closing DTA/DTL to BS]
A --> C[Deferred Tax Expense to IS]
A --> D[Non-cash Add-Back to CFO]
C --> E[Book Tax Expense = Current + Deferred]
E --> F[Net Income]
F --> D
D --> G[Cash Taxes Paid = Current Tax Only]
Balance Sheet Wiring
Non-current deferred tax asset ='Def Tax'!F60
Non-current deferred tax liability ='Def Tax'!F45
If DTA and DTL are in the same jurisdiction, net them on the presentation but keep them separate on the calculation tab for disclosure.
Income Statement Wiring
Current tax expense =Taxable_income_after_NOL * Combined_rate
Deferred tax expense =Change_in_net_DTL - Change_in_net_DTA
Total tax expense =Current + Deferred
Effective tax rate =Total_tax_expense / Pretax_book_income
The effective tax rate check is your single best control: it should reconcile to statutory + permanent differences ± discrete items. If your ETR is drifting more than 100 basis points a year without a permanent difference driver, something is miscoded.
Cash Flow Statement Wiring
Net income from IS
+ Depreciation & amortization from PP&E schedule
+ Deferred tax expense =Change_in_DTL - Change_in_DTA
+ Stock-based compensation from SBC schedule
+/- Working capital changes from WC schedule
= Cash flow from operations
The deferred tax add-back is what makes cash taxes paid ≈ current tax payable. Model it wrong and CFO will be off by the deferred movement every year.
💡 Pro Tip: Build a one-line reconciliation
Cash taxes paid = current tax expense + change in tax payable liabilityat the bottom of your cash flow tab. If it does not tie to your CFO line for taxes, you have either a deferred tax error or a tax payable working capital error — both of which are common and both of which invalidate DCF outputs.
What Are the Most Common Sources of Book-Tax Differences?
The high-frequency book-tax differences appear in nearly every model. Get comfortable with these five and you cover roughly 80% of real-world deferred tax activity.
1. Accelerated Tax Depreciation (DTL)
The largest DTL for most industrials. Book uses straight-line over the asset's economic life; tax uses MACRS with double-declining half-year convention. The gap builds through the early years and reverses through the tail.
2. Stock-Based Compensation (DTA that becomes windfall)
Book expenses SBC over the vesting period at grant-date fair value. Tax deducts the intrinsic value at exercise (or vesting for RSUs). The cumulative timing gap is a DTA during vesting; on exercise, the tax deduction often exceeds cumulative book expense — the "windfall" flows through the P&L under ASU 2016-09.
3. NOL Carryforwards (DTA)
Losses generate a DTA at the enacted tax rate. Post-2017 NOLs are indefinite-life but 80%-capped. Section 382 limits usage after an ownership change — a common issue in acquired targets.
4. Accrued Expenses (DTA)
Warranty, bonus, and litigation accruals hit the income statement when accrued but only reduce taxable income when paid. During the accrual, you carry a DTA.
5. Unrealized Gains and Losses (DTA or DTL)
Mark-to-market securities and unrealized FX gains hit book income before tax income (or vice versa). This is a temporary difference until realization.
ℹ️ Note: Goodwill from a stock acquisition creates a permanent difference (non-deductible), while goodwill from an asset acquisition or Section 338(h)(10) election is amortized over 15 years for tax and creates a large DTL that reverses as book impairs or as tax amortizes it.
Valuation Allowance: When and How Much?
Valuation allowance judgment is where deferred tax modeling stops being mechanical. ASC 740-10-30 requires a more-likely-than-not (>50%) threshold, evaluated using all positive and negative evidence.
The Four Sources of Positive Evidence
- Future reversals of existing DTLs — the strongest evidence. If you have $10M of DTLs reversing over the next 5 years, that supports $10M of DTAs regardless of forecast profitability.
- Future taxable income excluding reversals — supported by budget, backlog, contract commitments.
- Taxable income in carryback years — for jurisdictions with carryback (limited under TCJA).
- Tax planning strategies — sale-leasebacks, entity restructurings that create taxable income before NOL expiration.
Cumulative Loss Position: The Presumptive Trigger
A cumulative three-year pretax loss is generally considered significant negative evidence that is difficult to overcome. When this trigger fires, you should model a full valuation allowance and treat any subsequent release as a discrete item in the year of release.
The standard test formula:
=IF(SUM(prior_3_years_pretax_income) < 0, "Full VA presumed", "Case-by-case")
Common Deferred Tax Modeling Mistakes
The same handful of errors appear in nearly every model that has not been through a formal audit.
1. Modeling Cash Taxes at the Effective Book Rate
The single most common error. Cash taxes should be built from taxable income after NOL, at the statutory rate — not book income at the ETR. This mistake overstates or understates DCF value by 5–15% depending on the deferred tax pattern.
2. Ignoring the NOL 80% Cap
Post-2017 NOLs cannot fully shelter taxable income. A model that applies NOLs to zero out taxable income overstates the DTA and understates cash taxes.
3. Applying the Wrong Rate to Reversals
If tax rates change during your forecast, the DTA/DTL should be remeasured at the enacted rate expected in the year of reversal. A rate change is a discrete P&L item in the year of enactment.
4. Confusing Permanent and Temporary Differences
Non-deductible fines, tax-exempt interest, and 50% M&E are permanent — they change the ETR but never touch the deferred schedule. Analysts new to tax accounting frequently push these into the DTL roll-forward, breaking the reconciliation.
5. Missing the Valuation Allowance Release
When a company transitions from cumulative loss to sustained profitability, the release of a full valuation allowance is often the single largest one-time item in the P&L. It should be modeled as a discrete tax benefit in a specific forecast year, not smoothed.
⚠️ Warning: If your effective tax rate is materially different from statutory in a period where you did not model a permanent difference driver, your deferred tax schedule is wrong. Do not ship the model until you can reconcile ETR line-by-line back to statutory.
Frequently Asked Questions
How do you calculate deferred tax liability in Excel?
Multiply the cumulative temporary difference (book carrying value minus tax carrying value) by the enacted tax rate expected in the year of reversal. The formula is =cumulative_temp_diff * enacted_rate. For an accelerated depreciation DTL: =(NBV_book - NBV_tax) * combined_rate. Roll the balance forward each period by adding the new temporary difference movement times the rate.
What is the difference between current and deferred tax expense?
Current tax expense is what you owe the tax authority this year, calculated on taxable income at the statutory rate. Deferred tax expense is the movement in your net DTL minus movement in net DTA — it captures the timing difference between book and tax recognition. Total book tax expense = current + deferred, and this ties to your effective tax rate applied to pretax book income.
Do you need a valuation allowance on every DTA?
No. A valuation allowance is required only when it is more likely than not (>50% probability) that some or all of the DTA will not be realized. Companies with sustained profitability, sufficient reversing DTLs, or reliable forecast income typically carry no valuation allowance. Loss-making companies, distressed targets, and companies in industries with volatile earnings usually carry partial or full allowances.
How do NOL carryforwards affect deferred tax in Excel?
An NOL creates a DTA equal to NOL balance × enacted tax rate. Under U.S. TCJA rules, post-2017 NOLs carry forward indefinitely but can only offset 80% of taxable income in any given year, and Section 382 limits usage after ownership changes. Model NOLs as a separate rolling balance, apply the 80% cap in the utilization formula, and reduce the DTA as NOLs are consumed.
How does deferred tax flow to the cash flow statement?
Deferred tax expense is a non-cash item — it does not represent cash paid. On the cash flow statement (indirect method), you add back deferred tax expense to net income because the cash outflow was only the current portion. The identity is: cash taxes paid = current tax expense − change in tax payable, while book tax expense = current + deferred. The deferred piece appears as a reconciling item in CFO.
Building the Schedule With AI Assistance
Deferred tax is exactly the kind of high-context, high-cost-of-error work where AI assistance changes the calculus. Once your reconciliation and roll-forward tabs are structured, tools like VeloraAI can audit the schedule for classification errors (permanent vs temporary), reconcile the ETR walk from statutory to effective, and flag when a valuation allowance trigger should be reconsidered — the checks that a senior manager would run manually in a formal quality review. Wire the schedule once, verify each block against a hand-calculated tie-out, then let a copilot catch the drift as your assumptions evolve. The model that matters is the one that still ties on the fifth iteration.