Some tax estimation custom functions for Google Sheets

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Some tax estimation custom functions for Google Sheets

Post by kaesler »

I've written some custom functions that you can call from a Google Sheet for estimating US Federal income tax in current and future years, calculating RMDs, calculating the tax due on Social Security benefits, and a few other related operations.

A description of the functions can be found here:https://docs.google.com/document/d/1WMn ... sp=sharing

If anyone would like to try them out please send a private message and I can provide a way to share them.

I've tested them as best I can but it is possible they still have bugs. I'll try to be responsive to bug reports and improvement requests.
n00b
Posts: 198
Joined: Sun Feb 03, 2013 7:36 pm

Re: Some tax estimation custom functions for Google Sheets

Post by n00b »

Interesting. Thank you for sharing your work.

The link was dead when I looked yesterday so I'm glad I tried again today.

I'll send you a PM regarding access.
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

Here is an example of the functions being used to plan a Roth conversion. The colored cells are populated by calls to the custom functions.

Image
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

And here is an example sheet where the functions are used to estimate taxes for the future.

Image
User avatar
retired@50
Posts: 12821
Joined: Tue Oct 01, 2019 2:36 pm
Location: Living in the U.S.A.

Re: Some tax estimation custom functions for Google Sheets

Post by retired@50 »

kaesler wrote: Sat Aug 20, 2022 7:33 am And here is an example sheet where the functions are used to estimate taxes for the future.
I'd recommend replacing Trump with TCJA or something that removes any reference to a particular politician - since this is a "no politics" forum.

TCJA = Tax Cuts and Jobs Act

Regards,
If liberty means anything at all it means the right to tell people what they do not want to hear. -George Orwell
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

Thanks, yes I think I will do that. It had occurred to me.
RyeBourbon
Posts: 1219
Joined: Tue Sep 01, 2020 12:20 pm
Location: Delaware/Philly

Re: Some tax estimation custom functions for Google Sheets

Post by RyeBourbon »

How do you know future tax brackets and inflation?

Wouldn't it be easier just to do everything in real terms? Assume the tax brackets keep pace with the CPI?
Retired June 2023. AA = 55/35/10
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

RyeBourbon wrote: Sat Aug 20, 2022 5:42 pm How do you know future tax brackets and inflation?

Wouldn't it be easier just to do everything in real terms? Assume the tax brackets keep pace with the CPI?
Yep that is not such a bad approach. I think this can be slightly more accurate since it captures the fact that brackets and standard deductions are adjusted for inflation, but the taxable portion of Social Security is not.

To be clear, you have to estimate future inflation to use the code for future years.
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

The other possible advantage of these functions is that they allow you to model a possible future reversion of the tax code to pre-TCJA arrangements (different tax brackets, smaller standard deductions, etc).
RyeBourbon
Posts: 1219
Joined: Tue Sep 01, 2020 12:20 pm
Location: Delaware/Philly

Re: Some tax estimation custom functions for Google Sheets

Post by RyeBourbon »

kaesler wrote: Sat Aug 20, 2022 5:59 pm The other possible advantage of these functions is that they allow you to model a possible future reversion of the tax code to pre-TCJA arrangements (different tax brackets, smaller standard deductions, etc).
I realize that we don't consider legislation that isn't passed, but in my spreadsheet, I make the assumption that TCJA rates/brackets/std deduction etc. will be extended beyond 2025. It may or may not happen, but I think the assumption that everything reverts to pre-TCJA without modification is also suspect.

Thanks for doing this. I probably won't use your functions, but it is an inspiration to write my own. I'm using Excel, but considering Google Sheets.
Retired June 2023. AA = 55/35/10
Topic Author
kaesler
Posts: 243
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Some tax estimation custom functions for Google Sheets

Post by kaesler »

Yes I think TCJA is likely to be extended too.

My code only did real dollar modelling too for quite a while and that was quite useful to me. But I had some time on my hands and it wasn’t very much work to add the future years estimation and to model the reversion to pre-TCJA.

And of course you can use this code for real dollar estimates by just setting the year to 2022. No need then to estimate inflation or guess about TCJA reversion.

The main usefulness of this is that it plugs into a Google sheet. That’s what i wanted.

(I was also satisfying my curiosity about whether one can write Google sheet custom functions in Purescript. Happy to report you can. It compiles to JS and that JS is loaded into the sheet. It would not have been possible for me to get this code working correctly in JavaScript. Purescript is a sharper tool.)
Post Reply