Sheet & Flow

Posted on Oct 29 Originally published at onyxaholguin-cyber.github.io

How to Calculate Your Estimated Tax Safe Harbor in Excel or Google Sheets (90% vs 100%/110%, Tested)

#excel #spreadsheets #productivity #tutorial

If you freelance, the last 2026 estimated-tax payment is due Friday, January 15, 2027, and the question most people have at this point is not how much tax will I owe? but how much do I have to pay now so I don't get a penalty? Those are two different numbers. The IRS calls the second one the required annual payment, and it is often a lot smaller than your actual tax, because it can be based on last year's return.

This tutorial builds one small tab that answers it: the required annual payment under both rules, which rule wins, the January 15 payment that gets you there, the balance you will still owe in April, and a period-by-period check of the four installments. It uses only ROUND, MIN, MAX, IF, SUM, INDEX and TEXT, so it works the same in Excel, Google Sheets and LibreOffice. To test it, we built a workbook with these exact formulas, recalculated it in LibreOffice, and compared every cell with the worked example below, with the example in IRS Publication 505, and with an independent Python model on 40 random cases.

Not tax advice. The rules below are quoted from the 2026 Form 1040-ES instructions and IRS Publication 505 (Tax Withholding and Estimated Tax). This sheet covers the regular method (four equal installments) for federal tax only. It does not cover the farming/fishing rules, the annualized income method, or state estimated tax. Ask your preparer if any of those apply to you.

1. The two safe harbors

The 1040-ES instructions say you generally have to pay estimated tax if "You expect to owe at least $1,000 in tax for 2026, after subtracting your withholding and refundable credits." and your withholding and refundable credits will be less than the smaller of:

"90% of the tax to be shown on your 2026 tax return", or
"100% of the tax shown on your 2025 tax return." with the condition that "Your 2025 tax return must cover all 12 months."

For higher incomes there is one change. If your 2025 AGI was more than $150,000 ($75,000 if married filing separately for 2026), you "substitute 110% for 100% in (2b) under General Rule".

So the target is the smaller of the two numbers. If your income jumped this year, last year's tax is usually the smaller one, and paying it in full by January 15 protects you from the penalty even though you will still owe more when you file. If your income fell, 90% of this year's tax is usually smaller.

Withholding (from a W-2 job, for example) counts too, and by default it is spread evenly over the year no matter when it was actually taken out of your pay: "One-fourth of your estimated withholding is considered withheld on the due date of each payment period." (Publication 505 also lets you use the actual withholding dates instead; this sheet uses the default.)

2. Inputs (column B)

Cell	What to type	Example
B2	Expected total 2026 tax (Form 1040-ES worksheet line 11c)	27,600
B3	Total tax on your 2025 return	18,400
B4	2025 AGI (Form 1040 line 11b)	96,000
B5	Married filing separately for 2026? (`Yes`/`No`)	No
B6	Did your 2025 return cover all 12 months? (`Yes`/`No`)	Yes
B7	Federal income tax withheld for 2026 (whole year, expected)	2,400
B8	Estimated payment made by April 15, 2026	3,000
B9	Estimated payment made by June 15, 2026	3,000
B10	Estimated payment made by September 15, 2026	3,000
B11	Payment you plan to make by January 15, 2027	7,000

3. The formulas

B12 (Prior-year %):           =IF(B4>IF(B5="Yes",75000,150000),110%,100%)
B13 (90% of 2026 tax):        =ROUND(90%*B2,0)
B14 (Prior-year safe harbor): =IF(B6="Yes",ROUND(B12*B3,0),"")
B15 (Rule that sets it):      =IF(B14="","90% of 2026 tax",IF(B13<B14,"90% of 2026 tax",TEXT(B12,"0%")&" of 2025 tax"))
B16 (Required annual payment): =MIN(B13,B14)
B17 (Tax not covered by withholding): =B2-B7
B18 (Estimated payments needed?): =IF(OR(B16-B7<=0,B17<1000),"No","Yes")
B19 (Paid through September): =B7+SUM(B8:B10)
B20 (January 15 payment needed): =MAX(0,B16-B19)
B21 (Still owed at filing):    =MAX(0,B2-B19-B11)

MIN ignores the empty text that B14 returns when last year's return was a short year, so B16 falls back to the 90% rule on its own. Both safe-harbor amounts are rounded to whole dollars, as Publication 505 does in its example. B18 follows the order of the 1040-ES worksheet (lines 14a and 14b): if withholding alone covers the required annual payment, or the tax left after withholding is under $1,000, you don't need estimated payments.

B19 counts the full year's withholding, because by the fourth due date all four quarters of it are treated as withheld.

4. Check each period (rows 24 to 27)

The required annual payment is due in four equal parts, and the penalty is figured per period: "The penalty is imposed on each underpayment for the number of days it remains unpaid." So it also helps to see which earlier periods were short. Put the period numbers 1 to 4 in A24:A27, then in row 24 (fill down to 27):

C24 (Required by this due date): =$B$16*A24/4
D24 (Credited by this due date): =$B$7*A24/4+SUM($B$8:INDEX($B$8:$B$11,A24))
E24 (Short by):                  =MAX(0,C24-D24)
F24 (Status):                    =IF(E24>0,"Short","OK")

D24 adds a quarter of the year's withholding per period plus every estimated payment made so far, so a catch-up payment counts toward the periods after it, not before.

Worked example

A freelance designer who also has a small W-2 job. 2025 total tax was $18,400 on AGI of $96,000; with a busy 2026 she now expects $27,600 of tax. She sent $3,000 on each of the first three due dates and has $2,400 withheld at the W-2 job.

90% of 2026 tax = $24,840. Prior-year safe harbor at 100% (her AGI is under $150,000) = $18,400. The smaller is the prior-year number, so the required annual payment is $18,400 and B15 shows 100% of 2025 tax.
Paid through September (withholding + three payments) = $11,400.
January 15 payment needed = $7,000.
Even after that payment she will still owe $9,200 when she files. The safe harbor avoids the penalty; it doesn't reduce the tax. Keep that money in the tax account.

The period check shows why paying earlier is better than waiting:

Period	Due	Required	Credited	Short
1	Apr 15, 2026	$4,600	$3,600	$1,000
2	Jun 15, 2026	$9,200	$7,200	$2,000
3	Sep 15, 2026	$13,800	$10,800	$3,000
4	Jan 15, 2027	$18,400	$18,400	$0

The first three periods were each short, so a small penalty can still apply for the days those amounts were unpaid. The $7,000 brings her to the safe harbor by January 15, and sending it sooner (say in November) shortens the time the shortfall stays unpaid.

Higher-income check (the IRS's own example). Publication 505 uses 2025 tax of $42,581, expected 2026 tax of $71,253 and 2025 AGI of $180,000. Type those into B3, B2 and B4 and the sheet shows B12 = 110%, 90% of 2026 tax $64,128, prior-year safe harbor $46,839, and a required annual payment of $46,839, the same numbers as the publication.

Under $1,000 check. If your expected tax is $3,000, your 2025 tax was $4,000 and $2,200 will be withheld, the tax left after withholding is $800, so B18 shows No: under the general rule you don't need estimated payments.

Two dates worth knowing

The fourth period runs September 1 to December 31, so it is four months long, and its payment is due January 15, 2027.
The 1040-ES instructions add: "You don’t have to make the payment due January 15, 2027, if you file your 2026 tax return by February 1, 2027, and pay the entire balance due with your return." That only works if your paperwork is ready by then.

Common mistakes

Using this year's tax when last year's is smaller. If your income went up, 90% of this year's tax may be far more than you need to pay to be penalty-free.
Forgetting the 110% rule. Over $150,000 of 2025 AGI ($75,000 married filing separately), 100% of last year isn't enough.
Thinking the safe harbor is the bill. You still owe the rest of the tax by the filing deadline (B21).
Counting withholding by date. By default it is spread evenly over the four periods, which is why D24 uses $B$7*A24/4.
Using it with very uneven income. If most of your 2026 income came late in the year, the annualized income installment method (Form 2210 Schedule AI, explained in Pub. 505) may lower the earlier installments. This sheet doesn't do that calculation.

DEV Community