income tax formula from total taxable income
income tax formula from total taxable income
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!
Thank you!
Last edited by muel87 on Wed Mar 29, 2023 7:51 pm, edited 2 times in total.
-
- Posts: 5737
- Joined: Wed May 18, 2022 12:42 pm
Re: income tax formula from total tax
I don't understand what you are asking for. Can you give an example of what the inputs and outputs would be?
Re: income tax formula from total tax
Oops - meant to say given total taxable income.toddthebod wrote: ↑Sun Mar 26, 2023 7:40 amI don't understand what you are asking for. Can you give an example of what the inputs and outputs would be?
Re: income tax formula from total tax
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)
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.
Just as an example, my spreadsheet feeds off a tax table (note - this formula will be useless for you)
G97 is the taxable=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)))))
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
Re: income tax formula from total tax
Thanks!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)
G97 is the taxable=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)))))
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.
Re: income tax formula from total tax
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.
Re: income tax formula from total tax
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.
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.
Re: income tax formula from total tax
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.
Re: income tax formula from total tax
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.
Re: income tax formula from total tax
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 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.
Re: income tax formula from total tax
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.
Re: income tax formula from total tax
These tables make it easy to calculate with a single row lookup.
https://turbotax.intuit.com/tax-tips/ir ... /L7Bjs1EAD
Re: income tax formula from total tax
I don't recall that being available back in 2002, but maybe it was and I missed it.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.
Re: income tax formula from total tax
Site has versions as far back as 1996 tax return.dbr wrote: ↑Mon Mar 27, 2023 7:39 amI don't recall that being available back in 2002, but maybe it was and I missed it.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.
Re: income tax formula from total tax
Cool Should be useful for some.Eagle33 wrote: ↑Mon Mar 27, 2023 8:05 pmSite has versions as far back as 1996 tax return.dbr wrote: ↑Mon Mar 27, 2023 7:39 amI don't recall that being available back in 2002, but maybe it was and I missed it.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.
Re: income tax formula from total tax
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.muel87 wrote: ↑Sun Mar 26, 2023 8:10 amOops - meant to say given total taxable income.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?
Meet my pet, Peeve, who loves to convert non-acronyms into acronyms: FED, ROTH, CASH, IVY, ...
-
- Posts: 682
- Joined: Fri Jan 18, 2019 5:47 am
Re: income tax formula from total tax
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.
Re: income tax formula from total tax
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 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.
Re: income tax formula from total taxable income
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.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.
- Darth Xanadu
- Posts: 1108
- Joined: Sat Jan 27, 2018 12:47 am
- Location: MA
Re: income tax formula from total taxable income
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.thor111 wrote: ↑Fri Mar 31, 2023 6:31 amWhat 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.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.
-
- Posts: 2007
- Joined: Mon Mar 02, 2020 4:33 pm
Re: income tax formula from total taxable income
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
Tax = A * Income + B