Tax planning spreadsheet

B

beachfire

Recycles dryer sheets
Joined
May 5, 2017
Messages
79
Hi, been a member of this site for about 4 years. 56 YO retired with 56 YO DW just over 3 years ago. Absolutely loving life. Still have not lost that happy feeling we get every morning of getting to do whatever we want.

I searched but could not find what I was looking for. I am sure it has been covered a thousand times.

I am going to build out a spreadsheet in excel/numbers to allow me to analyze various tax decisions we will need to make year by year as we head toward 65 (medicare); 67ish (Social Security) and 72 RMDs.

Not sure if this is something people do on these type forums?? Does anyone have a clean version of such a spreadsheet that they would be willing to share?

I want to have data inputs on overall income (broken down into categories such as dividends, capital gains, interest, etc) and then plug in variables to evaluate doing various size roth conversion or selling taxable positions that have gains. Guessing I will have to write in assumptions on current tax rates, ACA income limits, etc.

Sorry for the hyper technical question, but would like to learn from those who have walked this path before I get going.

Thanks
 
I think most people here make their own. Reuse is great, but most people with interest in those kinds of questions and the kind of personality to be here want to (a) understand it completely, (b) have it work the way they want it to, and (c) have it make their particular set of assumptions and not make other assumptions that others might make.

I have one, but I'm 52, single, will take SS at 70, and have heirs, so mine wouldn't be of much use to you other than perhaps some things to think about or ways to do things.

I'd suggest building your own, and coming back here with questions about how to do things in Excel or whatever comes up as you build your own.

Also, building your own helps you understand things at a much deeper level than just taking someone else's spreadsheet and putting your numbers in. That understanding is worth a lot IMHO.
 
I think using someone else's spreadsheet will be difficult. I have mine built in Excel, with different assumptions but since our income is so high now, ACA is no longer even a consideration. We did manage our income in the year after we retired so that we could make use of ACA for just that one year.

I use the spreadsheet mainly to predict how much taxes we will need to pay for each year and as such, withhold appropriate amounts with SS, RMD and annuities.
 
Last edited:
Makes sense. Thanks. I will take a crack at it and see where I end up.
 
beachfire said:
Hi, been a member of this site for about 4 years. 56 YO retired with 56 YO DW just over 3 years ago. Absolutely loving life. Still have not lost that happy feeling we get every morning of getting to do whatever we want.

I searched but could not find what I was looking for. I am sure it has been covered a thousand times.

I am going to build out a spreadsheet in excel/numbers to allow me to analyze various tax decisions we will need to make year by year as we head toward 65 (medicare); 67ish (Social Security) and 72 RMDs.

Not sure if this is something people do on these type forums?? Does anyone have a clean version of such a spreadsheet that they would be willing to share?

I want to have data inputs on overall income (broken down into categories such as dividends, capital gains, interest, etc) and then plug in variables to evaluate doing various size roth conversion or selling taxable positions that have gains. Guessing I will have to write in assumptions on current tax rates, ACA income limits, etc.

Sorry for the hyper technical question, but would like to learn from those who have walked this path before I get going.

Thanks
Click to expand...

There are some available over on the bogleheads forum.
1. Retiree Portfolio Model - it's good but it takes a while to come up to speed on. Very comprehensive for multi-year planning.
2. Personal Finance Toolbox - I use this one quite a bit to help estimate my withholdings throughout the year as RSU's/ESPP/cap gains/dividends roll throughout the year. But it can do a lot more than that.


These and more on this page: https://www.bogleheads.org/wiki/Tools_and_calculators

Cheers.
Big-Papa
 
You might want to check out Maxifi.com. Good program for running different scenarios and comparing the results. Not cheap...$99/1 Yr subscription, but it's the most comprehensive tool I've found for these types of calculations.

Best,
qq
 
I have in the past downloaded other's spreadsheets, but find it's hard to figure out where to put my numbers.
Some of these things are complex, and its pretty overwhelming to me.

So far I've ended up with my own simple spreadsheet.
I just have about 20 categories and each year I put in what the number will be or should be, and then can see how much room we have for RMD's.
I always leave a cushion of a few thousand, as I never know some values for sure, and just use last years final number (for example for dividends) if I don't know the value.
 
big-papa said:
2. Personal Finance Toolbox - I use this one quite a bit to help estimate my withholdings throughout the year as RSU's/ESPP/cap gains/dividends roll throughout the year. But it can do a lot more than that.
Click to expand...

teej1985 said:
I think this is one of the more comprehensive spreadsheets: https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/
Click to expand...

Those will take you to the same tool. We also use it for planning because it can quickly generate charts such as the one in https://www.early-retirement.org/fo...are-premium-surcharge-110082.html#post2634951
 
Thanks everyone for the replies. The enormously detailed Cashflow spreadsheet is an example of what I was hoping to see. I am hoping to build my own custom spread sheet that will have 5% of what the cashflow spread sheet does. But looking at that tool, really helps me better imagine the types of things I want to include in the one I will build.

Thanks again for the responses.
 
beachfire said:
Thanks everyone for the replies. The enormously detailed Cashflow spreadsheet is an example of what I was hoping to see. I am hoping to build my own custom spread sheet that will have 5% of what the cashflow spread sheet does. But looking at that tool, really helps me better imagine the types of things I want to include in the one I will build.

Thanks again for the responses.
Click to expand...

You might find this helpful: https://www.i-orp.com
 
beachfire said:
Thanks everyone for the replies. The enormously detailed Cashflow spreadsheet is an example of what I was hoping to see. I am hoping to build my own custom spread sheet that will have 5% of what the cashflow spread sheet does. But looking at that tool, really helps me better imagine the types of things I want to include in the one I will build.

Thanks again for the responses.
Click to expand...
I don't see a cashflow spreadsheet, but I wish you success with your project.

A similar related question about Roth conversion and taxes comes up frequently. If you need enormous detail, this isn't helpful. But my take on a summary is in this post.

https://www.early-retirement.org/fo...ut-taxes-what-to-do-109933-3.html#post2630827
 
I'm trying to do about the same thing... but spreadsheets are a foreign idea to me...
 
I've built my own, based on Fed & State tax tables, Fed qualified dividends and capital gains worksheet, and Fed & State estimated tax worksheets. I enter the trans date and source. And across the top it summarizes quarterly - dividends, tax exempt income, LTCG, STCG, interest, Roth conversions, and estimated payments. It also has some alerts built in such as IRMAA thresholds, how much of my top bracket I'm using for Roth conversions, etc.

Sorry I'm not comfortable sharing my XLS, and I wouldn't be comfortable using someone else's as I know what calcs and assumptions mine is based on. IMO it's a very useful exercise to build your own anyway, you learn a lot about the tax code and how all the moving parts interplay.

Another decent tax planning tool in years where the tax code doesn't change drastically is whatever tax software you may use, e.g. TurboTax. After you complete your 2020 return, just create another return based on the 2020 return and enter your 2021 data as it comes in.
 
Last edited:
Midpack said:
I've built my own, based on Fed & State tax tables, Fed qualified dividends and capital gains worksheet, and Fed & State estimated tax worksheets. I enter the trans date and source. And across the top it summarizes quarterly - dividends, tax exempt income, LTCG, STCG, interest, Roth conversions, and estimated payments. It also has some alerts built in such as IRMAA thresholds, how much of my top bracket I'm using for Roth conversions, etc.

Sorry I'm not comfortable sharing my XLS, and I wouldn't be comfortable using someone else's as I know what calcs and assumptions mine is based on. IMO it's a very useful exercise to build your own anyway, you learn a lot about the tax code and how all the moving parts interplay.

Another decent tax planning tool in years where the tax code doesn't change drastically is whatever tax software you may use, e.g. TurboTax. After you complete your 2020 return, just create another return based on the 2020 return and enter your 2021 data as it comes in.
Click to expand...

One can also download last year's excel1040.com template.
 
We review our tax situation twice a year. In November we review tax planning for the year and make decisions of what needs to be done by YE.

In April we review our tax returns and submit same. Then we review them again to determine if we made the right decisions in the previous November plus anything else.
 
I also built and maintain my own spreadsheet, one column per year and one row for each different income stream, basically.

My retirement income (and age!) is high enough that I don't have to worry about SS humps or ACA. My main focus lately has been to figure my Roth conversion amount to get my AGI up close to, but not over, the next higher IRMAA tier.

At start of retirement, I had a negligible taxable account, but it has been growing nicely the past few years to where I should really add another income stream row for taxable dividends.

Also, I don't try to model or project income taxes in my spreadsheet, just AGI. Lots simpler that way.

Now once I get to age 72 and start RMDs next year, I don't plan to do (significant) Roth conversions anymore.
So my wonderful spreadsheet will have limited utility and my AGI will be somewhat out of my control.

But not entirely. The decision on how much of a $50k new car purchase to withdraw from my taxable account and how much from my Roth IRA will still depend on my baseline AGI projection for the year...
 
Last edited:
great help. all the links have provided me with great examples to look at as I work to build a simple tool for my situation. thanks for all the narratives on what each of you does as well, helps guide me as I move forward.

I will report back as we get close to year end, what I have done and if I found it helpful to have a custom spreadsheet vs using one of the many that are out there.

Thanks again.
 
The tax spreadsheets I built for myself I use for only the current year and at most the following year. The key parts of them are the tax booklet worksheets which do the QD and LTCG tax calculation and the form (8962) for the ACA premium subsidy.

At the start of the year, I enter various estimates for distributions such as the monthly bond fund dividends and more erratic ones such as stock fund distributions and all cap gain distributions. The spreadsheet also lets me figure out how much in estimated taxes I should pay, or if I am in a reasonably safe harbor to avoid penalties.

Before the recent tax law change in 2018, a frequent issue for me was whether to "bunch" some itemized deductions into a single calendar year or not, so I had to look at a 2-year tax impact of that decision. That's not an issue any more.

A big year for which this spreadsheet was very useful was in 2008 when I cashed out my company stock and had to expand the spreadsheet to include additional forms such as the AMT, as well as some little tax booklet worksheets such as the itemized deduction limitation. The spreadsheet was useful in helping me determine in which calendar year to pay 4th quarter estimated taxes - in December of the year I earned the income or a month later, in January of the year after I earned the income.

I'm not sure any generic spreadsheet will be very useful because everyone's situation is different and can easily vary from year to year, as mine has been.
 
I get the idea that a spreadsheet you build yourself is one you understand how to use, but I'm not sure I have the knowledge to know my results would be right and trust them. Maybe after a year or two of seeing the real taxes come out the same as the prediction? Then they'd change the tax law on me :)

I'll look through the suggestions in this thread as a place to get started myself.
 
You must log in or register to reply here.

Similar threads

S
Calling all spreadsheet geeks. Spend down plan.
2
Replies
27
Views
2K
Surewhitey
S
T
ROTH conversion vs capital gains harvesting flowchart
Replies
4
Views
814
mrfeh
M
J
Capital Gains Impact on Marginal Tax Rate
Replies
14
Views
849
SecondCor521
SecondCor521
S
Paying taxes on Roth conversions
2
Replies
38
Views
4K
Koolau
Koolau
FiveDriver
Oh, those Short Term Capital Gains
Replies
2
Views
332
audreyh1
audreyh1

Latest posts

Back
Top Bottom