Deferred Tax
Corporate Finance Financial Model (Free Excel Download)
Model temporary differences, tax bases, and reversal timing to forecast deferred tax assets and liabilities alongside the statutory tax expense and balance sheet.
professionals from Deloitte
Used by professionals from
About this model
A deferred-tax model translates a 6-category book-tax temporary-difference panel plus a statutory tax rate, a valuation-allowance ratio, and a 5-year pretax book income forecast into a per-category temp-diff roll-forward, a deferred-tax balance schedule that separates gross DTA from gross DTL, a tax-provision walk reconciling current and deferred tax, and a one-page dashboard with traffic-light status. This template lays the full mechanic on six sheets: an Assumptions sheet with the statutory tax rate, valuation-allowance ratio, start fiscal year, the 6 temporary-difference categories tagged DTA or DTL (accelerated depreciation, deferred gain, stock-based comp, R&D capitalisation, warranty reserves, NOL carryforward) each with opening balance, annual addition, and annual reversal, a 5-year pretax book income and permanent-difference row, and dashboard thresholds for VA ratio and ETR gap vs statutory; a Temp_Diffs sheet with per-category 5-year roll-forward blocks (opening, addition, reversal, closing), a category summary block with closing balances per year, and Gross DTA / Gross DTL / Net temp diff aggregate rows computed via SUMIFS against the category type tag, plus a panel delta check row; a DTA_DTL sheet that translates gross temp diffs into deferred-tax balances at the statutory rate, applies the valuation allowance, and computes net DT and the year-over-year change; a Provision sheet that walks pretax book income through permanent differences and a temp-diff book-to-tax adjustment to taxable income, current tax expense, deferred tax expense (from the change in net DT), total tax expense, and effective tax rate; and a Dashboard sheet with start fiscal year, net DT closing (Y5), gross DTA and DTL closing, DTA-to-DTL ratio, valuation allowance, VA ratio with traffic-light status, average ETR over 5 years, ETR gap vs statutory with traffic-light status, NOL carryforward remaining, and cumulative tax, pretax, and VA charged across the 5-year horizon.
The SUMIFS aggregation against the category type tag means a single edit on Assumptions reshapes the entire deferred-tax schedule, provision walk, and dashboard. The Net DT change driving deferred tax expense is computed against an opening Net DT that is derived from Cat_Opening at the statutory rate with the VA ratio applied, so Year 1 reconciles cleanly without a manual Year 0 column. The temp-diff book-to-tax adjustment uses the standard sign convention: DTA categories add (additions) and subtract (reversals) from taxable income, DTL categories do the opposite, so the Provision walk reconciles current tax exactly to taxable income times the statutory rate.
CFOs, tax directors, controllers, and audit teams use this template for ASC 740 / IAS 12 provision walks (reconcile pretax book income to total tax expense and produce an audit-clean current-vs-deferred split), deferred-tax balance-sheet integration (drop the net DT closing line into a 3-statement balance sheet as a non-current asset or liability), and NOL utilisation modelling (flex the NOL reversal per year to model utilisation against taxable income and watch the closing balance run down). The template intentionally assumes a constant statutory rate across the 5-year horizon - real-world rate changes require revaluing opening DT balances at the new rate and flowing the remeasurement through deferred-tax expense, which is out of scope here in favour of clean one-edit responsiveness on the per-category roll-forward.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Deferred Tax
- Six temporary-difference categories: accelerated depreciation, deferred gain, stock-based comp, R&D capitalisation, warranty reserves, NOL carryforward
- Per-category opening, annual addition, annual reversal, and type tag (DTA / DTL) on Assumptions
- Statutory tax rate, valuation-allowance ratio, start fiscal year, and dashboard thresholds
- 5-year pretax book income and 5-year permanent differences (editable per year)
- Per-category temp-diff roll-forward: opening / addition / reversal / closing each year
- Summary block of closing balances by category and Gross DTA / Gross DTL / Net aggregates via SUMIFS on category type
- Panel delta check row that ties closing movement to addition minus reversal at every year
- DTA_DTL schedule with gross DTA balance, valuation allowance, net DTA after VA, gross DTL balance, net DT, and year-over-year change
Deferred Tax Model: How the Template Works and What It Tracks
This deferred tax model template helps you track book-tax temporary differences over a ten-year horizon for a single operating entity. It covers six categories tagged as DTA or DTL, a provision walk, valuation allowance, NOL cap, and dashboard metrics.
The public download is a values-only preview; the underlying model captures the full institutional deferred-tax stack. Rates and financial results described here reflect illustrative model settings, not industry benchmarks.
Core Liabilities and Equity Drivers: Temporary Differences
The model tracks six temporary-difference categories: accelerated depreciation, deferred gains, stock-based compensation, R&D capitalisation, warranty reserves, and net operating loss (NOL) carryforwards. Each category is typed as either a deferred tax asset (DTA) or deferred tax liability (DTL).
- Per-category roll-forwards operate over ten years, with opening balances plus additions minus reversals to yield closing balances. Additions and reversals come from a per-year matrix, allowing you to reflect vesting schedules, Section 174 amortisation profiles, or warranty normalisation.
- The SUMIFS aggregation then groups closing balances by type to produce gross DTA and gross DTL. This design lets you flex individual category behaviour without collapsing all differences into a single constant.
Calculation Flow: From Temporary Differences to Provision
The calculation chain starts with temporary differences, which feed the deferred tax balance schedule. Gross DTA and gross DTL are multiplied by the applicable statutory rate to get DTA and DTL balances.
- A valuation allowance, derived from a four-source realisation analysis, reduces the DTA to a net realisable amount. Net deferred tax is then (DTA balance minus valuation allowance) minus DTL balance.
- Concurrently, the provision walk builds taxable income from pretax income, permanent differences, and a temporary-difference M2B adjustment that excludes the NOL category. NOL usage is capped at 80% of taxable income when the toggle is on.
- Current tax is computed on taxable income using a blended rate, less credits. Deferred tax is the negative change in net deferred tax, and remeasurement captures the effect of statutory rate changes on opening balances.
Total tax is the sum of current, deferred, remeasurement, and UTP change.
Outputs and Checks: Dashboard, ETR, and Validation
The dashboard presents headline metrics such as net deferred tax, average effective tax rate (ETR), valuation allowance ratio, remaining NOL, and a traffic-light status. The ETR reconciliation walk on the Provision sheet explains the difference between statutory and effective rates through state, foreign, permanent differences, valuation allowance, credits, and rate changes.
- A Checks sheet performs 45 PASS/FAIL identity verifications, including roll-forward ties, SUMIFS aggregations, net DT identities, and NOL cap compliance. These checks ensure internal consistency and flag potential input errors.
- The model also includes a 3-statement integration block, allowing analysts to paste deferred tax figures into a broader financial model. All outputs are driven by the initial assumptions, which reside on a dedicated sheet.
Practical Use: Assumptions, Scenarios, and Scope
The model is designed for a single operating entity and allows you to flex tax policy, category additions and reversals, permanent differences, NOL caps, jurisdiction rates, credit generation, and UTP parameters. A key test is changing the Year 3 statutory rate from 21% to 25%, which triggers a remeasurement charge on the Provision sheet.
- The valuation allowance derivation uses four sources of future taxable income: DTL reversal, projected future income, tax planning strategies, and carryback. The model enforces a valuation allowance floor to maintain prudence.
- UTP rollforward follows FIN 48 mechanics with opening, additions, settlements, and lapses. Credits are capped as a percentage of pre-credit current tax.
The single foreign rate and income percentage are simplifying assumptions; real-world foreign ETR can vary. This structure supports evaluating deferred tax positions without requiring a full consolidation.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a deferred-tax model?+
A deferred-tax model tracks the book-tax temporary differences that give rise to deferred tax assets (DTA) and deferred tax liabilities (DTL) on the balance sheet, applies the statutory tax rate and any valuation allowance, and walks pretax book income to total tax expense via current tax (on taxable income) and deferred tax (the change in net DT). It is the workbook auditors and tax directors reach for when reviewing the ASC 740 or IAS 12 provision.
How are DTA and DTL classified here?+
Each of the six categories is tagged DTA or DTL on Assumptions. DTA categories (stock-based comp, R&D capitalisation, warranty reserves, NOL carryforward) reverse into future tax benefits. DTL categories (accelerated depreciation, deferred gain) reverse into future tax expense. Gross DTA and DTL are aggregated via SUMIFS against the type tag, so adding or recharacterising a category is a one-cell edit.
How does the valuation allowance work?+
The VA ratio on Assumptions is applied uniformly to gross DTA each year. Net DTA after VA = gross DTA × rate × (1 - VA ratio). A 0% VA means the company expects to realise the full DTA against future taxable income. A 100% VA means the DTA is fully written down (typical for cumulative-loss companies). Real-world VA assessments use a 3-year cumulative-loss test plus management judgement; the model leaves the ratio as an input cell.
Why does the ETR drift slightly from the statutory rate?+
With zero permanent differences and a fixed VA ratio, ETR exactly equals the statutory rate. ETR drifts from statutory when permanent differences are non-zero, when the VA ratio changes period to period, or when the gross DTA base grows (because the VA absolute dollar grows proportionally even with a fixed ratio). The dashboard flags absolute ETR gaps against user-set thresholds.
How is NOL utilisation modelled?+
NOL carryforward is one of the six DTA categories with an annual reversal modelling utilisation against current-year taxable income. Flex the reversal per year to model expected NOL consumption, and watch the closing balance and dashboard NOL remaining metric to know when the carryforward is exhausted. The model does not enforce the 80% post-TCJA cap automatically - the operator sets the annual reversal manually.
Can I add more temp-diff categories?+
Yes. Extend the category block on Assumptions (one row per category with name, type, opening, addition, reversal), add the corresponding 5-row block on Temp_Diffs (section header + opening + addition + reversal + closing), and add the closing row to the summary block. The SUMIFS aggregation will pick up the new row automatically through Cat_Types and Cat_Opening / Addition / Reversal named ranges.
Have more financial modelling questions? Contact us
Related templates
Depreciation
12-month fixed-asset register with capex schedule and straight-line depreciation across an asset panel.
Stock-Based Compensation
Plan stock-based compensation expense, vesting, and remaining unrecognised cost in one place.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
M&A Modeling & Valuation
Comprehensive M&A valuation with DCF, comparable companies, and precedent transactions analysis.
