income tax formula from total taxable income

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
Topic Author
muel87
Posts: 478
Joined: Mon Jan 09, 2023 2:11 pm
Location: Rockville, MD

income tax formula from total taxable income

Post by muel87 »

There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total taxable income? Dont need all the additional taxes (capital gains, net investment, medicare, etc).

Thank you!
Last edited by muel87 on Wed Mar 29, 2023 7:51 pm, edited 2 times in total.
toddthebod
Posts: 5737
Joined: Wed May 18, 2022 12:42 pm

Re: income tax formula from total tax

Post by toddthebod »

muel87 wrote: Sun Mar 26, 2023 7:09 am There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total tax? Dont need all the additional taxes (capital gains, net investment, medicare, etc).

Thank you!
I don't understand what you are asking for. Can you give an example of what the inputs and outputs would be?
Topic Author
muel87
Posts: 478
Joined: Mon Jan 09, 2023 2:11 pm
Location: Rockville, MD

Re: income tax formula from total tax

Post by muel87 »

toddthebod wrote: Sun Mar 26, 2023 7:40 am
muel87 wrote: Sun Mar 26, 2023 7:09 am There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total taxable income? Dont need all the additional taxes (capital gains, net investment, medicare, etc).

Thank you!
I don't understand what you are asking for. Can you give an example of what the inputs and outputs would be?
Oops - meant to say given total taxable income.
jebmke
Posts: 25476
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: income tax formula from total tax

Post by jebmke »

If isn't a simple formula.

Just as an example, my spreadsheet feeds off a tax table (note - this formula will be useless for you)
=IF(G97>$D$8,$G$8+$I$8*(G97-$D$8),IF(G97>$D$7,$G$7+$I$7*(G97-$D$7),IF(G97>$D$6,$G$6+$I$6*(G97-$D$6),IF(G97>$D$5,$G$5+$I$5*(G97-$D$5),IF(G97>$D$4,$G$4+$I$4*(G97-$D$4),G97*$I$3)))))
G97 is the taxable
Column D is the income threshold for the bracket (MFJ)
Column G is the tax on income under the bracket
Column I is the tax rate on income over the threshold.
Don't trust me, look it up. https://www.irs.gov/forms-instructions-and-publications
Topic Author
muel87
Posts: 478
Joined: Mon Jan 09, 2023 2:11 pm
Location: Rockville, MD

Re: income tax formula from total tax

Post by muel87 »

jebmke wrote: Sun Mar 26, 2023 8:20 am If isn't a simple formula.

Just as an example, my spreadsheet feeds off a tax table (note - this formula will be useless for you)
=IF(G97>$D$8,$G$8+$I$8*(G97-$D$8),IF(G97>$D$7,$G$7+$I$7*(G97-$D$7),IF(G97>$D$6,$G$6+$I$6*(G97-$D$6),IF(G97>$D$5,$G$5+$I$5*(G97-$D$5),IF(G97>$D$4,$G$4+$I$4*(G97-$D$4),G97*$I$3)))))
G97 is the taxable
Column D is the income threshold for the bracket (MFJ)
Column G is the tax on income under the bracket
Column I is the tax rate on income over the threshold.
Thanks!
dbr
Posts: 46181
Joined: Sun Mar 04, 2007 8:50 am

Re: income tax formula from total tax

Post by dbr »

I have set up a spreadsheet or two that include some estimation of tax costs. My spreadsheets don't have one simple formula in one cell but rather go through the various steps of how a tax return is filled out and the results computed. It is not all that horrendous but it does take some attention to detail. My spreadsheet work is for me and would not function as an example for someone else.
kardan
Posts: 116
Joined: Sun Apr 28, 2019 9:56 am

Re: income tax formula from total tax

Post by kardan »

Assuming no qualified dividends, NIIT, etc as you specified, here is one way to do it.

Cell A1: Taxable Income
Cell B1: =MATCH(A1,B3:B6,1)

Cells A3 - A6: 10%, 12%, 22%, 24%
Cells B3 - B6 (MFJ, 2023 brackets): 22000, 89450, 190750, 364200
Cells C3 - C6: =A3*(B3-B2),=A4*(B4-B3),=A5*(B5-B4),=A6*(B6-B5)

Cell A8: =INDEX(C3:C6,B1,1)+(A1-INDEX(B3:B6,B1,1))*INDEX(A3:A6,B1+1,1)

Cell A8 will be the tax. If you want the tax for single, use the single brackets in Cells B3 - B6. I only went to the 24% bracket because with your specifications, taxable income would most likely not be above the 24% bracket.
capran
Posts: 1091
Joined: Thu Feb 18, 2016 9:45 am

Re: income tax formula from total tax

Post by capran »

muel87 wrote: Sun Mar 26, 2023 7:09 am There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total taxable income? Dont need all the additional taxes (capital gains, net investment, medicare, etc).

Thank you!
I'm not savvy enough to know how to import the data off my excel Tax Planning sheet that I maintain. But I all of my income sources feed into the Total Taxable Income. So as income, or dividends, or pensions or SS change, those changes auto fill. For example:

50,000 Pensions 1040 line 5b
23,000 Taxable Social Security 1040 line 6b
125,000 IRA distributions 1040 line 4b
500 Taxable Interest 1040 line 2b
8,000 Taxable Dividends 1040 line 3b
206,500 AGI (1040SR line 11)
-30,700 MFJ standard deduction (2 over 65)
175,800 Taxable Income Line 15
then, from the tax computation worksheet page (2022 it's on page 75) I will update that for 2023 when draft comes out in August)
175,800*22%=38,676 -8766=29910

I try to keep it simple and make it directly applicable to the 1040 that will ultimately reflect the numbers. If I get a surprise Captial Gain there's a cell for that that feeds the cell for AGI. If a dividend gets reported, that feeds the dividend cell, and so on.
thor111
Posts: 90
Joined: Sun Jun 06, 2021 6:53 pm

Re: income tax formula from total tax

Post by thor111 »

Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
capran
Posts: 1091
Joined: Thu Feb 18, 2016 9:45 am

Re: income tax formula from total tax

Post by capran »

thor111 wrote: Sun Mar 26, 2023 6:17 pm Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
Too bad I didn't know about this one when I created my own in 2020. Although updating and maintaining since then has been very simple. As income including interest and dividends and SS and pensions changes, I just plug it in.
thor111
Posts: 90
Joined: Sun Jun 06, 2021 6:53 pm

Re: income tax formula from total tax

Post by thor111 »

Yeah....I've been using it for the last 3 years. It's pretty amazing. I'm sure there isn't a ton of modifications required year to year unless the tax laws change. I find it really useful towards the end of the year when I'm trying to plan Roth conversions.
erp
Posts: 565
Joined: Wed Feb 01, 2023 4:19 pm

Re: income tax formula from total tax

Post by erp »

muel87 wrote: Sun Mar 26, 2023 7:09 am There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total taxable income? Dont need all the additional taxes (capital gains, net investment, medicare, etc).
These tables make it easy to calculate with a single row lookup.
https://turbotax.intuit.com/tax-tips/ir ... /L7Bjs1EAD
dbr
Posts: 46181
Joined: Sun Mar 04, 2007 8:50 am

Re: income tax formula from total tax

Post by dbr »

thor111 wrote: Sun Mar 26, 2023 6:17 pm Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
I don't recall that being available back in 2002, but maybe it was and I missed it.
User avatar
Eagle33
Posts: 2392
Joined: Wed Aug 30, 2017 3:20 pm

Re: income tax formula from total tax

Post by Eagle33 »

dbr wrote: Mon Mar 27, 2023 7:39 am
thor111 wrote: Sun Mar 26, 2023 6:17 pm Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
I don't recall that being available back in 2002, but maybe it was and I missed it.
Site has versions as far back as 1996 tax return.
dbr
Posts: 46181
Joined: Sun Mar 04, 2007 8:50 am

Re: income tax formula from total tax

Post by dbr »

Eagle33 wrote: Mon Mar 27, 2023 8:05 pm
dbr wrote: Mon Mar 27, 2023 7:39 am
thor111 wrote: Sun Mar 26, 2023 6:17 pm Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
I don't recall that being available back in 2002, but maybe it was and I missed it.
Site has versions as far back as 1996 tax return.
Cool Should be useful for some.
User avatar
22twain
Posts: 4032
Joined: Thu May 10, 2012 5:42 pm

Re: income tax formula from total tax

Post by 22twain »

muel87 wrote: Sun Mar 26, 2023 8:10 am
toddthebod wrote: Sun Mar 26, 2023 7:40 am I don't understand what you are asking for. Can you give an example of what the inputs and outputs would be?
Oops - meant to say given total taxable income.
You can correct the thread title by clicking on the pencil icon on your original post, then editing the title. This will update the thread title as shown in the thread list, and (I think) on any future posts in this thread, although maybe not on posts already made.
Meet my pet, Peeve, who loves to convert non-acronyms into acronyms: FED, ROTH, CASH, IVY, ...
boomer_techie
Posts: 682
Joined: Fri Jan 18, 2019 5:47 am

Re: income tax formula from total tax

Post by boomer_techie »

muel87 wrote: Sun Mar 26, 2023 7:09 am There are a lot of tax calculators out there. Does anyone have an excel formula I could use for federal income tax, given total taxable income?
My version which picks between tax table or tax rate as needed:

CellB376: total taxable income
CellB377: =IF(B376<100000,IF(B376<3000,IF(B376<25,FLOOR(B376+5,10),FLOOR(B376,25)+12.5),FLOOR(B376,50)+25),B376)
CellB378: =ROUND((B377−HLOOKUP(B377,$H378:$N380,1,close-match))×HLOOKUP(B377,$H378:$N380,2,close-match)+HLOOKUP(B377,$H378:$N380,3,close-match),IF(B377<100000,0,2))

Note: H378:N380 contains the tax rate table (for my filing status) as published by the IRS.
Note2: This could be done with just two HLOOKUPs with a slightly modified table.
Topic Author
muel87
Posts: 478
Joined: Mon Jan 09, 2023 2:11 pm
Location: Rockville, MD

Re: income tax formula from total tax

Post by muel87 »

thor111 wrote: Sun Mar 26, 2023 6:17 pm Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
Wow. And I was proud of my simple little google sheet... I hope he got a lot of donations. I built something that only has my relevant info. Didnt take too long, and I learned a lot about taxes in the process.
thor111
Posts: 90
Joined: Sun Jun 06, 2021 6:53 pm

Re: income tax formula from total taxable income

Post by thor111 »

thor111 wrote: ↑Sun Mar 26, 2023 11:17 pm
Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
Wow. And I was proud of my simple little google sheet... I hope he got a lot of donations. I built something that only has my relevant info. Didnt take too long, and I learned a lot about taxes in the process.
What I usually do is get it to look like my return from the previous year, and then just tweak the inputs that I know are changing for the current year. I agree that It's a great tool, and hope he continues to refresh it each year. It's also great for double checking at tax time. I get TurboTax and this form to agree, and I'm happy I haven't made any glaring mistakes.
User avatar
Darth Xanadu
Posts: 1108
Joined: Sat Jan 27, 2018 12:47 am
Location: MA

Re: income tax formula from total taxable income

Post by Darth Xanadu »

thor111 wrote: Fri Mar 31, 2023 6:31 am
thor111 wrote: ↑Sun Mar 26, 2023 11:17 pm
Why not just use https://sites.google.com/view/incometaxspreadsheet/home ? It looks much more complicated than it is. If you pull in the form for 2023, you can just populate it with info from 2022 returns to get a quick estimate on what your taxes will be. All that is needed after that is to modify the w-2 info, the 1099 info, and any other user specific forms. You could then hide any tabs that don't pertain to you to make it look less forbidding. This spreadsheet has been very accurate in past years. I don't know why anyone would want to create their own. Just my opinion.
Wow. And I was proud of my simple little google sheet... I hope he got a lot of donations. I built something that only has my relevant info. Didnt take too long, and I learned a lot about taxes in the process.
What I usually do is get it to look like my return from the previous year, and then just tweak the inputs that I know are changing for the current year. I agree that It's a great tool, and hope he continues to refresh it each year. It's also great for double checking at tax time. I get TurboTax and this form to agree, and I'm happy I haven't made any glaring mistakes.
This seems like a good approach, and I may adopt it. I've only started looking at this tool this week, and it seems pretty daunting, but I suppose that once you go through it once or twice, it becomes pretty easy to tweak year-to-year as the end user.
Hyperchicken
Posts: 2007
Joined: Mon Mar 02, 2020 4:33 pm

Re: income tax formula from total taxable income

Post by Hyperchicken »

If you are firmly in one tax bracket and know you are not crossing over into a neighboring one, the formula can be simplified to a linear polynomial:

Tax = A * Income + B
Post Reply