# Deferred Tax A 5-year deferred-tax model: 6 book-tax temporary-difference categories tagged DTA or DTL (accelerated depreciation, deferred gain, SBC, R&D capitalisation, warranty reserves, NOL), per-category opening / addition / reversal / closing roll-forward, gross DTA and DTL aggregated via SUMIFS on category type, deferred-tax balances at statutory rate with valuation allowance, a Provision walk (pretax + perm + temp-diff M2B = taxable, current vs deferred tax, ETR), and a dashboard with net DT, average ETR, VA ratio, NOL remaining, and traffic-light status. - Canonical: https://finamodel.com/templates/deferred-tax - Excel download: https://finamodel.com/templates/deferred-tax.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Founders & operators, CFOs, Tax directors, Controllers, Audit teams - Tags: deferred tax, dta, dtl, tax provision, valuation allowance ## Overview 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's included - 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 - Provision walk: pretax + permanent diffs + temp-diff M2B = taxable income, current tax, deferred tax, total tax, ETR - Dashboard with start year, closing net DT (Y5), gross DTA / DTL, DTA-to-DTL ratio, VA, VA ratio with status, average ETR, ETR gap vs statutory with status, NOL carryforward, cumulative tax / pretax / VA - 5-year pretax book income forecast and 5-year permanent differences (editable per year) - Temp_Diffs sheet with per-category opening / addition / reversal / closing roll-forward across 5 years, summary block by category, and Gross DTA / Gross DTL / Net temp diff aggregates plus a panel delta check row - DTA_DTL sheet: gross DTA at statutory rate, valuation allowance, net DTA after VA, gross DTL at statutory rate, net DT, and year-over-year change - Provision sheet: pretax book income, permanent differences, temp-diff book-to-tax adjustment, taxable income, current tax, deferred tax, total tax, effective tax rate - Dashboard with start fiscal year, net DT closing (Y5), gross DTA / DTL, DTA-to-DTL ratio, valuation allowance, VA ratio, average ETR, ETR gap vs statutory, NOL carryforward, and cumulative tax / pretax / VA ## 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. ## Built for ASC 740 / IAS 12 provision walks When the question is "how does pretax book income become total tax expense, and what sits on the balance sheet as deferred tax?", the cleanest answer is a category-level temp-diff roll-forward plus a current-vs-deferred provision split. This template gives controllers and tax directors an audit-ready walk that ties statutory rate, permanent differences, temp-diff movements, and valuation-allowance changes to the GAAP tax expense. ## Designed for one-edit responsiveness Every input - statutory rate, VA ratio, category opening / addition / reversal, type tag, pretax income, permanent differences, threshold - is a named-range or named-cell input. Flex one number and the temp-diff roll-forward, deferred-tax balance schedule, provision walk, and dashboard all recompute - no formula rewrites. ## Honest about scope This is a deferred-tax provision and balance-sheet model. It does not model statutory-rate changes mid-period (which require revaluing opening DT balances at the new rate), it does not apply the 80% post-TCJA NOL utilisation cap automatically, and it does not split DTAs across federal and state jurisdictions. The trade-off favours one-edit responsiveness and audit-cleanliness over multi-jurisdiction complexity. ## Built for ASC 740 / IAS 12 provision walks When the question is "how does pretax book income become total tax expense, and what sits on the balance sheet as deferred tax?", the cleanest answer is a category-level temp-diff roll-forward plus a current-vs-deferred provision split. This template gives controllers and tax directors an audit-ready walk that ties statutory rate, permanent differences, temp-diff movements, and valuation-allowance changes to the GAAP tax expense. ## Designed for one-edit responsiveness Every input - statutory rate, VA ratio, category opening / addition / reversal, type tag, pretax income, permanent differences, threshold - is a named-range or named-cell input. Flex one number and the temp-diff roll-forward, deferred-tax balance schedule, provision walk, and dashboard all recompute - no formula rewrites. ## Honest about scope This is a deferred-tax provision and balance-sheet model. It does not model statutory-rate changes mid-period (which require revaluing opening DT balances at the new rate), it does not apply the 80% post-TCJA NOL utilisation cap automatically, and it does not split DTAs across federal and state jurisdictions. The trade-off favours one-edit responsiveness and audit-cleanliness over multi-jurisdiction complexity. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: tax policy, category panel, pretax forecast, thresholds. - Statutory tax rate, valuation-allowance ratio, start fiscal year - 6 temporary-difference categories with type tag (DTA / DTL), opening, addition, reversal - 5-year pretax book income and 5-year permanent differences - VA ratio and ETR-gap traffic-light thresholds (green / amber) ### Temp_Diffs Per-category 5-year roll-forward with aggregates by type. - One block per category: opening (rolls from prior closing), addition (constant), reversal (constant), closing (= opening + addition - reversal) - Summary block listing closing balances by category for SUMIFS aggregation - Gross DTA / Gross DTL closing per year via SUMIFS against the category type tag - Net temp diff and a panel delta check row that ties at every year ### DTA_DTL Year-end deferred-tax balances at statutory rate with valuation allowance. - Gross DTA closing pulled from Temp_Diffs, then × Tax_Rate = DTA balance - Valuation allowance = DTA balance × VA_Ratio - Net DTA after VA = DTA balance - VA - Gross DTL closing pulled from Temp_Diffs, then × Tax_Rate = DTL balance - Net DT = Net DTA - DTL balance - Change in Net DT per year, with Year 1 opening computed from Cat_Opening ### Provision Pretax-to-total-tax walk with current and deferred split. - Pretax book income and permanent differences pulled from Assumptions per year - Temp-diff book-to-tax adjustment via SUMIFS over Cat_Addition / Cat_Reversal by type - Taxable income = Pretax + Perm + M2B - Current tax = Taxable × Tax_Rate - Deferred tax = -(Change in Net DT) from DTA_DTL - Total tax = Current + Deferred; ETR = Total / Pretax with zero guard ### Dashboard Headline metrics with traffic-light status and cumulative aggregates. - Start fiscal year (driven by Start_Year input) - Net DT closing (Y5) with Net DTA / Net DTL flag - Gross DTA / Gross DTL closing (Y5) and DTA-to-DTL ratio - Valuation allowance and VA ratio with On track / Watch / Heavy status - Average ETR (5y) and ETR gap vs statutory with On track / Watch / Distorted status - NOL carryforward remaining (Y5) - Cumulative tax expense, pretax income, and VA charged across 5 years ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: tax policy, category panel, pretax forecast, thresholds. - Statutory tax rate, valuation-allowance ratio, start fiscal year - 6 temporary-difference categories with type tag (DTA / DTL), opening, addition, reversal - 5-year pretax book income and 5-year permanent differences - VA ratio and ETR-gap traffic-light thresholds (green / amber) ### Temp_Diffs Per-category 5-year roll-forward with aggregates by type. - One block per category: opening (rolls from prior closing), addition (constant), reversal (constant), closing (= opening + addition - reversal) - Summary block listing closing balances by category for SUMIFS aggregation - Gross DTA / Gross DTL closing per year via SUMIFS against the category type tag - Net temp diff and a panel delta check row that ties at every year ### DTA_DTL Year-end deferred-tax balances at statutory rate with valuation allowance. - Gross DTA closing pulled from Temp_Diffs, then × Tax_Rate = DTA balance - Valuation allowance = DTA balance × VA_Ratio - Net DTA after VA = DTA balance - VA - Gross DTL closing pulled from Temp_Diffs, then × Tax_Rate = DTL balance - Net DT = Net DTA - DTL balance - Change in Net DT per year, with Year 1 opening computed from Cat_Opening ### Provision Pretax-to-total-tax walk with current and deferred split. - Pretax book income and permanent differences pulled from Assumptions per year - Temp-diff book-to-tax adjustment via SUMIFS over Cat_Addition / Cat_Reversal by type - Taxable income = Pretax + Perm + M2B - Current tax = Taxable × Tax_Rate - Deferred tax = -(Change in Net DT) from DTA_DTL - Total tax = Current + Deferred; ETR = Total / Pretax with zero guard ### Dashboard Headline metrics with traffic-light status and cumulative aggregates. - Start fiscal year (driven by Start_Year input) - Net DT closing (Y5) with Net DTA / Net DTL flag - Gross DTA / Gross DTL closing (Y5) and DTA-to-DTL ratio - Valuation allowance and VA ratio with On track / Watch / Heavy status - Average ETR (5y) and ETR gap vs statutory with On track / Watch / Distorted status - NOL carryforward remaining (Y5) - Cumulative tax expense, pretax income, and VA charged across 5 years ## Features - **SUMIFS-driven DTA / DTL aggregation:** Gross DTA and Gross DTL per year are computed via SUMIFS against the category type tag ("DTA" / "DTL") on Assumptions. Add or recharacterise a category by editing one row and the entire deferred-tax schedule, provision walk, and dashboard recompute. - **Net DT change drives deferred tax:** Deferred tax expense per year is -(Closing Net DT - Opening Net DT), where the Year 1 opening is computed from Cat_Opening balances at the statutory rate with the VA ratio applied. This keeps current tax and deferred tax cleanly separated in the Provision walk and reconciles to total tax = (pretax + perm + temp-diff M2B) × rate adjusted for VA changes. - **ETR walk with traffic-light status:** Dashboard reports the 5-year average ETR, the absolute gap versus the statutory rate, and an On track / Watch / Distorted flag against user-set thresholds. With zero permanent differences and stable VA, ETR equals the statutory rate; permanent diffs and VA movements show up as ETR drift. ## Use cases - **ASC 740 / IAS 12 provision walk:** Reconcile pretax book income to total tax expense via taxable income, current tax, and deferred tax. Auditors get a one-page walk that ties the GAAP tax expense back to statutory rate, permanent differences, temp-diff movements, and valuation-allowance changes. - **Deferred-tax balance sheet integration:** The Net DT closing line on DTA_DTL drops into the balance sheet as a non-current asset (when positive) or non-current liability (when negative). The Provision deferred-tax-expense line drops into the IS tax line. Pair with the 3-statement template to wire deferred tax into the full statements. - **NOL utilisation modelling:** NOL carryforward sits as a DTA category with an annual reversal modelling utilisation against 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. ## Frequently asked questions ### 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. ## Related templates - [Depreciation](https://finamodel.com/templates/depreciation) - [Stock-Based Compensation](https://finamodel.com/templates/stock-based-compensation) - [3 Statement Model](https://finamodel.com/templates/3-statement-model)