tax rate estimates

K

kens450r

Confused about dryer sheets
Joined
Oct 7, 2020
Messages
8
I am trying unsuccessfully estimate tax amounts for my financial spreadsheet. What I compute using a manual calculation using excel is not anywhere near what tax calculators on Forbes advisor or Smart Asset sites are coming up with which is frustrating. Without reasonable correct tax projections it's very hard to map out my FIRE and beyond I'm aiming for 2025 or 2026 and trying to figure out on my own...

Nothing matches and I'm locked down on my master financial projections sheet until this is figured out...

Let's use 2023 taxes for instance: married filing jointly. Lets make is simple using the top of the 12% bracket of $89,450. without the std. deduction I get $9,414. Smart Asset is coming up with $6,970 and Forbes advisor comes up with $6, 970 too.. I assume that the sites are using the std. deduction of $27,700 for married filing jointly so if I take $89,450 less the std. deduction that's an income of $61,750.. I manually calculate that out to $6,090. ($89,450 - $27, 700 less the first $11,000 not taxed = $51,750 at 12% which comes out to $6,210. What is going wrong here??

Also, how is everyone figuring their tax rates on a running year to year basis without manually calculating them? Very cumbersome if you are shuffling taxable and non taxable withdrawals to see how the numbers fall in place through all of your retirement years.


Surely somebody has a good spreadsheet to cover all the bases. I have some reasonable Excel skills but getting a spreadsheet up and running that keeps all the balls up in the air and tracking properly is getting the better of me...

I paid $7k to a financial advisor in 2021 to run retirement numbers with taxes and their numbers for taxes are way higher... Just not getting it...

I"m spending all my hours outside of work on this for weeks and it's taking the fun out of it. I have a full on reshuffling of retirement money too that needs to be done. Asset location and allocation...
 
Last edited:
Should be $6970, figured as follows for MFJ (Married filing jointly)

$89,450 AGI (adjusted gross income).
$27,700 Subtract standard deduction (assuming you are <65 and not blind)
$61,750 taxable income

Tax brackets
$0 to $22,000 = 10%
$22,000 to $89,450 = 12%


Taxes due
= (22,000 x .10) + ((61,750 - 22,000) x .12)
= (2200) + (39750 x .12)
= (2200) + (4770)
= $6970



I don't know what you are talking about with the "first $11,000 not taxed" language. First, all taxable income from the first dollar is taxed. And, second, if you are talking about the 10% bracket, $11,000 is for single filers, not MFJ. You don't need a spreadsheet for this calculation, just a correct understanding of the tax brackets.


This post has all the brackets and other important data https://www.early-retirement.org/fo...-trigger-income-levels-for-2023-a-115879.html.

PS - the $9414 taxes for $89,450 taxable income is also incorrect. It should be $10,294. It appears that you started with 89,450, subtracted 11,000, then applied 12% to the remaining 78,450, which would give $9414. But that's not how the tax brackets work. What you should do is start from the bottom. So for the first $22,000 of income, you apply a 10% rate (so $2200 tax). Then for the amount from $22,000 to $89,450 (the next $67,450 of income), you apply a 12% rate (so $8094 tax). Then you add those amounts (so 2200 + 8094 = $10,294 tax).

PPS - the tax brackets and standard deduction currently are adjusted for inflation, so I just assume an inflation rate and apply that to the brackets for every year in my spreadsheet. I assume the same inflation rate for my pension income, investments and spending, so that I am essentially planning in all current dollars.
 
Last edited:
I would just use an estimate of an overall tax rate. Maybe vary that some based on whether or not you're looking at significantly different income scenarios. For example:

$6970/89450 = 7.8% so in estimating, to be conservative, I'd probably use 8% to even 10% if I knew my income was going to be around the 12% bracket cut off.

The next dollar will be taxed at 22% so if you're planning on a higher income stream, you'll have to adjust accordingly - and so on for each bracket.
 
kens450r said:
I am trying unsuccessfully estimate tax amounts for my financial spreadsheet. What I compute using a manual calculation using excel is not anywhere near what tax calculators on Forbes advisor or Smart Asset sites are coming up with which is frustrating. Without reasonable correct tax projections it's very hard to map out my FIRE and beyond I'm aiming for 2025 or 2026 and trying to figure out on my own...

Nothing matches and I'm locked down on my master financial projections sheet until this is figured out...

Let's use 2023 taxes for instance: married filing jointly. Lets make is simple using the top of the 12% bracket of $89,450. without the std. deduction I get $9,414. Smart Asset is coming up with $6,970 and Forbes advisor comes up with $6, 970 too.. I assume that the sites are using the std. deduction of $27,700 for married filing jointly so if I take $89,450 less the std. deduction that's an income of $61,750.. I manually calculate that out to $6,090. ($89,450 - $27, 700 less the first $11,000 not taxed = $51,750 at 12% which comes out to $6,210. What is going wrong here??

Also, how is everyone figuring their tax rates on a running year to year basis without manually calculating them? Very cumbersome if you are shuffling taxable and non taxable withdrawals to see how the numbers fall in place through all of your retirement years.


Surely somebody has a good spreadsheet to cover all the bases. I have some reasonable Excel skills but getting a spreadsheet up and running that keeps all the balls up in the air and tracking properly is getting the better of me...

I paid $7k to a financial advisor in 2021 to run retirement numbers with taxes and their numbers for taxes are way higher... Just not getting it...

I"m spending all my hours outside of work on this for weeks and it's taking the fun out of it. I have a full on reshuffling of retirement money too that needs to be done. Asset location and allocation...
Click to expand...

Gumby nailed it... you can use https://www.irscalculators.com/tax-calculator to confirm it... set to 2023, MFJ, $89,450 of unearned income and you will get $6,970 for federal income tax.

Your $61,750 of taxable income is correct... the first $22,000 is taxed at 10% resulting in $2,200 in tax and the remaining $39,750 is taxed at 12% resulting in $4,770 in tax for a total tax of $6,970.

One of the problems that you have is that if you want income to be to the top of the 12% tax bracket then the income needs to be $117,150 ($89,450 + $27,600), not $89,450. If your income is $117,150 and your deductions are $27,700 then your taxable income would be $89,450 and equal to the top of the 12% tax bracket in 2023.

I have taxes built into my spreadsheet but is becomes quite complicated and a little unwieldly, especially where your income is a mix of ordinary income (interest, pension, taxable SS) and preferenced income taxed at different and lower rates (qualified dividends and LTCG). In short, I define taxable income each year as interest + dividends + 85% of SS - standard deduction. Preferenced income (dividends are not taxed since I model Roth conversions to the top of the 0% referenced tax bracket which is $200 less than the top of the 12% tax bracket). The I apply the 10% and 12% ordinary tax rates and like Gumby does, I increase the tax brackets and standard deduction each year for inflation using an assumed inflation rate.
 
Last edited:
I use a Fed/State *effective* tax rate estimate in some cases.

For example, we've been floating back and forth between the 12% and 22% current Fed brackets. But on my yearly tax prep report (TurboTax) there is an effective rate of 10.74% for 2022. We were in the 22% bracket that year.

Then I add some slop for NJ graduated income tax, and use a worst case effective rate (at least for a few years) of 15%.
 
I also have a financial planning spreadsheet. It was helpful back in my early retirement years when I was delaying SS.
Now that I'm 74, it's of limited utility going forward.

While I've done my own income taxes every year since college, I chose not to try to model income taxes in my spreadsheet.
I just compute my AGI from my various income streams and I've been happy with that.

If I did feel a need to include taxes in my spreadsheet, I'd just use an estimated percentage of my AGI. My AGI increases a little bit each year and my Federal TAX as a percentage of AGI has been rather steady in the 16-17% range. My marginal tax rate is 24%...
 
target2019 said:
I use a Fed/State *effective* tax rate estimate in some cases.

For example, we've been floating back and forth between the 12% and 22% current Fed brackets. But on my yearly tax prep report (TurboTax) there is an effective rate of 10.74% for 2022. We were in the 22% bracket that year.

Then I add some slop for NJ graduated income tax, and use a worst case effective rate (at least for a few years) of 15%.
Click to expand...

I sort of did the same - pretty much just a SWAG. It turned out to be way too conservative. I don't try to guess the future anymore so it is a moot point.
 
I built a spreadsheet some 10 years ago including the federal tax tables at the time. Each year I included the new tax tables, IRMAA etc. As time went on, our tax situation became more complicated, making the estimates, just that, estimates. Keep that in mind. I really don't think that being off by 10% is unexpected no matter what formula you use.

If you use any tax software, you can use it for estimating each tax year using uninflated numbers. My spreadsheet now is used for looking out 1 year, although it does go out 30 years. I mean, why not? Once the formulas are there, it should be simply copy - paste for the next "n" years. At the end of each year, I fill in actual numbers. It was more intended as a planning device to see if I was on target. I am not spending near as much as I am "allowed", so the spreadsheet has moved on to serving a different purpose for us.
 
I just make a copy of my previous year’s return in TurboTax and do what ifs to get an estimate.
 
jebmke said:
I sort of did the same - pretty much just a SWAG. It turned out to be way too conservative. I don't try to guess the future anymore so it is a moot point.
Click to expand...
What I described is a bit more than a SWAG. For estimating into the future, more than a year, 15% effective will be plenty for us, until I croak.

Now for this years taxes, I use 2023 TurboTax, and make appropriate changes where necesssary.

YMMV.
 
Whether it's complicated or not, I've found great value in banging on a spreadsheet until my numbers matched other calculators. It forces me to understand it and learn where the triggers are. The spreadsheets are an unmanageable, complicated mess by the time I'm done but I'll know how the calculation is done.
Always start with actual IRS forms, worksheets, instructions vs. some random website fishing for clicks.
 
Tax brackets are adjusted for inflation each year using Series Id:*****CUUR0000SA0
Not Seasonally Adjusted
Series Title:**All items in U.S. city average, all urban consumers, chained, not seasonally adjusted
Area:**********U.S. city average
Item:**********All items
Base Period:***DECEMBER 1999=100

The start of bracket values are rounded down to nearest $50.

The amount of bracket inflation is based on the Sept to Aug average from the previous years. Example: 2023 brackets are inflated from the 2022 brackets by the percent change in ratio of the [average of monthly index values from sept 2021 to Aug 2022] to [average of monthly index values from sept 2020 to Aug 2021]
For forecasting taxes, I use the average of the previous 8 years change in ratio as a guess that is as good as any for determining future tax brackets.

Each year, I update the brackets to the as-published brackets and forecast future taxes by adjusting the brackets as I described. Within Excel, I made a function that calculates tax using the forecasted brackets along with other inflation adjusted values such as deductions.

not perfect but way more detailed than is necessary.
 
Yes I get the correct totals now. Incorrect inputs = incorrect outputs:facepalm:
 
My average (federal) tax rate is a meaningless figure because I have significant portions of my income as qualified dividends (QD) and long-term cap gains (LTCG), both of which are taxed at lower rates (sometimes 0%) than ordinary income.

My homemade spreadsheet is a skeleton version of various income tax forms as well as the all-important QDLTCG worksheet found in the instruction booklet.
 
We do not know what is going to happen after 2025 yet because the TCJA tax rate cuts are set to expire 12/31/25.

Good going Gumby :) Let us not forget when worst comes to worst we can print out the forms and instructions and sharpen our pencil.
 
Thanks for this thread. I never thought of trying to project my taxes out for future years. I think it will be a good exercise to understand whether it would make sense and whether I am able to do any conversions.
 
Wile E. Coyote said:
We do not know what is going to happen after 2025 yet because the TCJA tax rate cuts are set to expire 12/31/25.
Click to expand...
This will show you where my brain is at. I saw the date you wrote and thought the old tax rates would take place *starting on* 12/31/25. So, the old tax rates would become retroactive to the beginning of 2025. :blush:
 
statsman said:
This will show you where my brain is at. I saw the date you wrote and thought the old tax rates would take place *starting on* 12/31/25. So, the old tax rates would become retroactive to the beginning of 2025. :blush:
Click to expand...

So, you missed the word expire? I think we no longer shoot people for doing that. LOL :angel:
 
Excel Spreadsheet for Fed and NYS

Here is an Excel spreadsheet that I developed. Fill in the sections in green text.
I live in NY so the first $20,000 of our pension is exempt, and Social Security is exempt in NYS.

If you live in another state, you will have to fill in the tax table for that state.
 

Attachments

  • 2023-2024 Taxes and Roth Conversion Blank.xls
    20.5 KB · Views: 9
I use a spreadsheet from the Mr MoneyMoustache forum. I'm not at my computer but I think it has the work toolbox in it. Best tool to predict taxes that I've found.
 
Last edited:
You must log in or register to reply here.

Similar threads

R
Age 73 Tax Planning (currently 67)
Replies
12
Views
621
RetiredHappy
R
E
Est tax calculator?
Replies
8
Views
505
big-papa
big-papa
J
Tax Stacking Question
Replies
2
Views
393
pb4uski
pb4uski
O
Target date funds and tax strategies
Replies
11
Views
424
mrfeh
M
J
Tax Return- jointly owned home but one deducts everything
Replies
8
Views
637
Montecfo
M

Latest posts

Back
Top Bottom