Track Taxes in Google Sheets: Live Tax Tracker (2026)

Here's how to build it, with formulas that actually tie.

The Core Structure

Before writing a formula, get the architecture right. The tax tracker lives on a dedicated Tax tab. It references:

• P&L! - for pretax income by period
• Assumptions! - for rate inputs (federal, state, blended)
• Balance Sheet! - for deferred tax asset/liability opening balances

Nothing hard-codes into the Tax tab. Every input is a live reference. That's what makes the whole thing stay current when Q3 closes and someone updates the P&L.

Link the Provision to Your P&L in Google Sheets

The current provision is always a function of pretax income times your effective statutory rate. But the ETR is never just federal plus state - permanent differences (meals, stock comp, R&D credits) move it.

Start with the provision calc:

// Tax tab - column C = current quarter, column D = YTD

// Pretax income pulled live from P&L
=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!B:B,"<="&Assumptions!$B$4,'P&L'!A:A,"Pretax Income")

// Statutory rate (federal + blended state, net of federal deduction)
// Assumptions!B8 = 21% federal, Assumptions!B9 = 6.5% blended state
=Assumptions!$B$8 + Assumptions!$B$9*(1-Assumptions!$B$8)

// Statutory provision
=Tax!C4 * Tax!C6

// Permanent difference add-back (meals at 50%, non-deductible penalties)
=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!A:A,"50% Meals Add-Back")
  + SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!A:A,"Non-Deductible Penalties")

// Final provision = statutory provision + permanent diffs × statutory rate
=Tax!C7 + Tax!C8 * Tax!C6

For a company with $2.7M pretax income, a 21% federal rate, and a 6.5% blended state rate (net of federal deduction), the statutory rate is roughly 26.3%. That's a ~$710K statutory provision. Add $37.8K of non-deductible permanent items taxed at 26.3%, and you land at $724K - the number that flows to the income statement.

The key: pretax income is never typed into the Tax tab. It comes from P&L via SUMIFS. When the P&L moves, the provision moves.

Quarterly Estimated Payment Tracker

Corporations use a safe harbor to avoid underpayment penalties. Per IRS Publication 542, "a corporation that expects to owe $500 or more in taxes for the year generally must make estimated tax payments." The safe harbor is 100% of prior year tax, divided into 4 equal installments.

If prior year tax was $610K, your safe harbor is $152.5K per quarter. Your tracker should show the gap between what's been paid and what's due:

// Assumptions tab:
// B15 = Prior year tax liability ($610,000)

// Tax tab: Estimated Payments section

// Safe harbor installment per quarter
=Assumptions!$B$15 / 4    // = $152,500

// Amount paid this quarter (link from treasury tab or manual entry)
=Treasury!D12

// Gap (negative = underpaid)
=Tax!D18 - Tax!D17

// YTD cumulative gap
=SUMIFS(Tax!E18:E21, Tax!C18:C21, "<="&TODAY())

Conditional format the gap column: red when negative, green when positive. Takes 30 seconds and saves a CFO conversation.

One non-obvious issue: safe harbor uses tax liability, not provision. If your provision includes deferred tax, you're overstating the required payment. Pull from prior year actual payments, not from the prior year model.

ETR Bridge

The ETR bridge is what auditors and boards want. It explains why your GAAP effective rate is 28.4% when statutory is 26.3% - and the 140 basis point difference is the story.

Wire the Amount column to live formula outputs from your provision calc. The Rate Impact column is just =Amount/Pretax_Income*10000 (converts to basis points). Every row updates automatically when pretax income or an assumption changes.

This is the version you send to the auditor - not a manually typed table that's stale by the time they read it.

Deferred Tax Rollforward in Google Sheets

Deferred taxes are where most Sheets models break down. The typical failure: someone builds a DTA/DTL schedule with opening balances typed in rather than linked, and the rollforward disconnects from the income statement.

The correct structure:

// Opening DTA - pulled from prior period Balance Sheet
=IFERROR(
  INDEX('Balance Sheet'!$C:$C,
    MATCH("Deferred Tax Asset",'Balance Sheet'!$A:$A,0)),
  0)

// Current period deferred provision (timing difference movement)
// E.g., accelerated depreciation creates a DTL
=SUMIFS('Fixed Assets'!E:E,'Fixed Assets'!B:B,"Depreciation Timing Difference")
  * Tax!$C$6

// Closing DTA = opening + current movement
=Tax!H4 + Tax!H12

// Check: closing DTA must tie to Balance Sheet - flag if non-zero
=Tax!H13 - INDEX('Balance Sheet'!$C:$C,MATCH("Deferred Tax Asset",'Balance Sheet'!$A:$A,0))

Put that check row in red conditional formatting. If it's non-zero before the board pack goes out, you have an error.

If you're working through DTA movement and trying to understand why a specific temporary difference is reversing faster than your model expects, ModelMonkey can query across your tabs - read the depreciation schedule, the tax provision, and the balance sheet simultaneously - and explain what's driving the discrepancy without you manually tracing 4 sheets.

Refreshing External Tax Rates in Google Sheets

State tax rates change. If your model has Assumptions!B9 typed as 6.5%, that's right until a state adjusts its corporate rate - and then you're presenting wrong numbers.

IMPORTXML can pull current rate information from public sources:

// Pulls from a public rate source - cached up to 6 hours per Google's documentation
=IFERROR(
  IMPORTXML("https://taxfoundation.org/data/all/state/state-corporate-income-tax-rates/",
    "//table//tr[td[contains(text(),'California')]]/td[2]"),
  6.5%)   // fallback: last confirmed rate

One important note from Google's own IMPORTXML documentation: the function "may cache results for up to 6 hours." For tax rates that rarely change mid-quarter, this is acceptable. For anything time-sensitive, set an Apps Script time trigger to force recalculation:

// Tools > Script editor - set on hourly time trigger
function refreshTaxImports() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var taxSheet = ss.getSheetByName("Tax");
  // Touch a cell to force recalculation
  var cell = taxSheet.getRange("A1");
  cell.setNote("Refreshed: " + new Date().toISOString());
}

Keep the IFERROR fallback. If the source page structure ever changes, you want a visible last-known value, not a silent #VALUE! that flows into your provision.

Putting It Together

In summary, a well-built tax tracker has 4 linked sections:

1. Provision calc - pretax income from P&L × blended statutory rate ± permanent diffs
2. Estimated payment tracker - safe harbor installments vs. payments made, with gap column
3. ETR bridge - fully formula-driven rate impact table
4. Deferred tax rollforward - opening balance from Balance Sheet, movement from timing differences, closing that ties back

The whole tab updates when the P&L moves. The only manual entries are actual payments made (which should come from treasury anyway). As of June 2026, the 21% federal corporate rate remains unchanged from 2018, but state blended rates vary by apportionment - always pull them from Assumptions, never hard-code them into the Tax tab.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.