@SquashingDebt - yea! Glad to have you. I was hoping we would have more interested, but I'm happy to proceed just the two of us. People can join in as we go if they want.As you go through this, your thoughts on how to improve the Instructions tab would be appreciated. Or, in general, how to "improve usability for new users." For better or worse, I've lost the perspective of a new user, so if you can help me help others, that would be great.
Here's my thinking, but I'm open to suggestions. I'd like to do my reading/studying/practicing on the weekend (likely Sunday) since I'm still a working stiff. And then post my questions/thoughts/etc. early in the week. We'd take on a section a week (or so as we see how it goes).
How about we start with the second tab (calculations) - columns A-D, rows 1-20 (essentially the first box)? Those look easy enough for me to start off feeling like I can be successful! Does that work for you?
Ok, I filled in the first section! Some other context to share as we go along: single, 32, with fairly straightforward finances - W2 earnings only, 401k, Roth IRA, HSA, no debt (renter), maybe my first taxable investments later this year if I finish my new car savings.Good questions!
@MDM, I have my first comment/question for you:
The only thing in this first section that slowed me down was how to handle my employer-sponsored HSA situation.
I contribute $75 per biweekly paycheck (deducted from my gross salary) and my employer contributes $125 per month ($1500 per year), for a total of $3450. I put that whole $3450 in cell B7. But then I figured I should add the $1500 from my employer contribution to my gross salary, right?
And the personal HSA (B45) is for if I contribute directly to my HSA, not through my employer (and thus not getting the deduction from my FICA-eligible wages)? So I'll put that at zero.
Ok, I filled in the first section! Some other context to share as we go along: single, 32, with fairly straightforward finances - W2 earnings only, 401k, Roth IRA, HSA, no debt (renter), maybe my first taxable investments later this year if I finish my new car savings.Good questions!
@MDM, I have my first comment/question for you:
The only thing in this first section that slowed me down was how to handle my employer-sponsored HSA situation.
I contribute $75 per biweekly paycheck (deducted from my gross salary) and my employer contributes $125 per month ($1500 per year), for a total of $3450. I put that whole $3450 in cell B7. But then I figured I should add the $1500 from my employer contribution to my gross salary, right?
And the personal HSA (B45) is for if I contribute directly to my HSA, not through my employer (and thus not getting the deduction from my FICA-eligible wages)? So I'll put that at zero.
Yes, B45=0 for your situation is correct.
As for how employer HSA contributions go, there are a couple of options and either can work. You could either
- Put your $1950 in B7 and add $1500 in B19 to whatever 401k match you get, or
- Put all $3450 in B7 and add $1500 to gross salary/wages, as you figured.
I think "employer match" is more straightforward, instead of adding the HSA match to "gross salary/wages," but it's a matter of taste.
Doing it the latter way might be slightly more accurate in terms of "time to FI" calculations (because the HSA balance isn't taxed on withdrawal while the 401k balance is), but the uncertainty in those calculations is so large anyway that I'd stick with the simpler approach. But, again, there is nothing "wrong" either way.
@MDM - I'm thinking the first box is the easiest box...and it took me some time before I could get the numbers to come out right! I notice in other boxes you have a comments in the cells for instructions. Is it too much to put comments in this section that points people to the right W-2 boxes for this section. My husband has info in 14 (other). Took me a while to figure out where that was supposed to go!Perhaps something such as "W-2 box 5 plus all amounts in rows 4-8 on this sheet" for cells B3 and C3?
Box 14. Employers may use this box to report information such as state disability insurance taxes withheld, union dues, uniform payments, health insurance premiums deducted, nontaxable income, educational assistance payments, or a member of the clergy’s parsonage allowance and utilities. Railroad employers use this box to report railroad retirement (RRTA) compensation, Tier 1 tax, Tier 2 tax, Medicare tax, and Additional Medicare Tax. Include tips reported by the employee to the employer in railroad retirement (RRTA) compensation.Thoughts?
How about we do columns G-I, rows 2-40 for next time? @MDM, what's the deal with Mort (Int) - H34. I understand the directions (put in 1000000) but I don't know why.It's due to a recent tax law change. The mortgage interest deduction is limited to the interest on a balance of $750,000 if the mortgage started in 2018 or later, but can be taken on a balance of $1,000,000 otherwise. For most people it won't matter. ;)
Also, I don't yet understand WHERE and in WHAT ORDER information goes. For example, in the non paycheck income box (week after next I'm guessing), am I figuring those numbers on another tab, pulling from my taxes, figuring outside the spreadsheet and adding? I appreciate your patience!!However works best for you. The Non-paycheck income and Subtractions for AGI sections are structured similarly to the way form 1040 used to look: https://www.irs.gov/pub/irs-prior/f1040--2017.pdf.
@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document." I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.
@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document." I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.
I could change the text in A51 to "SL int. (calc'd from entries in rows 133-137)" - would that suffice?
I believe all blank white boxes actually stay blank - unless one removes protection and types something in them - but did you find some that work otherwise?
ETA: A few "blank" boxes will display an error message in some circumstances. E.g., see H19 and H20.
Thanks for the suggestion - easy enough to add for whenever a new version is published.@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document." I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.
I could change the text in A51 to "SL int. (calc'd from entries in rows 133-137)" - would that suffice?
I believe all blank white boxes actually stay blank - unless one removes protection and types something in them - but did you find some that work otherwise?
ETA: A few "blank" boxes will display an error message in some circumstances. E.g., see H19 and H20.
I think in that particular instance I was a little thrown off because the rest of the section has values for person 1 and person 2, but that line only had the total. I don't know that A51 needs any extra info - that was just one example. I was just thinking in the instructions tab there could be a reminder that everything not green will auto-populate so people don't wonder/worry about it. Something like "User entries go in the cells with green shaded backgrounds. All other cells will auto-populate based on user entries." I know that's implied already, but it might help ease people into things if they don't have a lot of experience with spreadsheets. If you work straight down for example, there are values that won't be updated until you get to later sections of the spreadsheet, which can be a little bit confusing if you don't realize how it works. I guess it all depends on which level of spreadsheet user you're targeting :)
Data overload is a definite problem with that spreadsheet. As I went section by section, I found that a great deal did not apply to us. I actually have my own spreadsheet that is simpler and works for our situation. Of course, it leaves out things that don't apply to us, so it wouldn't work for others.Yep, the target audience is pretty much in between those who dislike spreadsheets altogether on one end, and those who already have their own custom one that handles all their income, credits, etc., on the other.
I need to play catch up too, but I’m a newbie that would like to join in if possible. (I’ve actually been in a standoff with the captcha for several days now. Lol)Unfortunately google docs, LibreOffice, and probably others, just don't have all the functionality of Excel. In other words, if you have a choice, download into Excel.
I had originally entered a substantial portion of my data on the first tab in a google docs version and somehow goofed up one of the cells and now need to begin again, almost from scratch.
I've been trying to use OpenOffice, I'm have some issues like massive income and no tax.Looks good. You have 64 and 60 in G8 and H8?
I had a working spreadsheet last with OpenOffice, but I get Excel if needed.
I don't know that it is because of OpenOffice, but could someone just poke in a few numbers
and verify for me.
G2=2 MFJ no kids, we are ages 64 and 60.
B25=$12,000Did you enter the 12000 in cell D25? Remember that entries go in the green shaded cells only. Putting 12000 in D25 should also cause 12000 to appear in B25.
B28=$90,750
At this point I64 still shows $0 of tax owed.
Now add in some tIRA distributions.Did you enter the 80000 in cell D31? Remember that entries go in the green shaded cells only. Putting 80000 in D31 should also cause 80000 to appear in B31.
B31=$80,000
At this point my spreadsheet still shows $0 on line I64As it should, unless you have entered something for state and local tax in H35 and/or H36. Federal tax in D63, based on $12K QD and $80K tIRA distribution, MFJ ages 60 & 64, should be $6,284.
It does show $1626 on line B65 Medicare Premium.Yes.
G61 shows 2 and H61 shows 3.Yes, those are defaults but, being in green cells, can be changed at will if one wants to determine W-4 withholding allowances for W-2 income.
I've been trying to use OpenOffice, I'm have some issues like massive income and no tax.Looks good. You have 64 and 60 in G8 and H8?
I had a working spreadsheet last with OpenOffice, but I get Excel if needed.
I don't know that it is because of OpenOffice, but could someone just poke in a few numbers
and verify for me.
G2=2 MFJ no kids, we are ages 64 and 60.
B25=$12,000
B28=$90,750
At this point I64 still shows $0 of tax owed.
Did you enter the 12000 in cell D25? Remember that entries go in the green shaded cells only. Putting 12000 in D25 should also cause 12000 to appear in B25.Ahhh! No, I only put in column B. So know, I put everything into column D.
There should be nothing at all in B28. What is the $90,750?Sorry, that should have said B27, LTCG. But now I know to put it in D27.
Now add in some tIRA distributions.
B31=$80,000
Did you enter the 80000 in cell D31? Remember that entries go in the green shaded cells only. Putting 80000 in D31 should also cause 80000 to appear in B31.Nope, everything into Column D, I think I got that.
At this point my spreadsheet still shows $0 on line I64
As it should, unless you have entered something for state and local tax in H35 and/or H36. Federal tax in D63, based on $12K QD and $80K tIRA distribution, MFJ ages 60 & 64, should be $6,284.
It does show $1626 on line B65 Medicare Premium.
Yes.
G61 shows 2 and H61 shows 3.
Yes, those are defaults but, being in green cells, can be changed at will if one wants to determine W-4 withholding allowances for W-2 income.
To clarify: inputs go in the green shaded cells, not necessarily in a particular column.QuoteDid you enter the 12000 in cell D25? Remember that entries go in the green shaded cells only. Putting 12000 in D25 should also cause 12000 to appear in B25.Ahhh! No, I only put in column B. So know, I put everything into column D.QuoteThere should be nothing at all in B28. What is the $90,750?Sorry, that should have said B27, LTCG. But now I know to put it in D27.
The $90,750 is $78750 LTCGs plus $12,000 left over from the $24k Standard deduction minus my $12k income.
Something I didn't know about, I'm 64, I don't get medicare, That starts next year. Do I still owe that? Is this a percentage of some cell, or a fixed fee?This one is a bit tricky. Yes, you won't owe medicare until turning 65, so it's not strictly correct to include a medicare premium in your cash flow for prior years. However, the medicare premiums you will pay in any year are based on your income two years prior. See What is IRMAA? (https://65medicare.org/what-is-irmaa/). The spreadsheet calculates the "coming in two years" medicare premium so one can include that effect when considering large Roth conversions, etc.
I have no withholding. I do pay quarterlies, should I enter those. Actually, I'm just trying to figure out how to maximize tax free money for the future, so just looking at the bottom line, What is the minimum tax due on the maximum income into a future lower tax situation.What you are trying to figure is really what the spreadsheet is designed to facilitate. No need to enter quarterly (or any) estimated tax payments - the assumption is that you will pay the tax due on an annual basis, and that you will avoid underpayment penalties by making timely payments.
Thank you for all your patience, and all the work you did on your spreadsheet.You're welcome. All good questions - keep 'em coming and I hope the end result will be useful!
I have made very simple entries on the spreadsheet.Tables below show what I see with the above inputs:
D25=$12,000 Qualified Dividends
D31=$110,000 tIRA distributions
G2=2 Filing Status MFJ
G8=64 Age
H8=60 Age
The tIRA will be put into a Roth as a Roth Conversion.
I see the tax due is $10,549, this is 9.59% tax.
Paycheck frequency: | Annual | Annual | |
Paycheck Items | Earner #1 | Earner #2 | Annual |
Non-paycheck income | Annual | Annual | Annual |
Qualified dividends | $12,000 | $12,000 | |
tIRA distribution | $110,000 | $110,000 | |
1040 Total Income | $122,000 | $0 | $122,000 |
1040 AGI | $122,000 | ||
Income Taxes | |||
Federal tax | $12,349 | 2019, MFJ, std. | $12,349 |
Medicare premium | $1,626 | 0 | $1,626 |
Total income taxes | $13,975 | $13,975 | |
Filing Status | 2 | 1=S, 2=MFJ, 3=HOH | |
Adult #1 | Adult #2 | ||
Age | 64 | 60 | |
Full-time student? | 0 | 0 | |
AGI | $122,000 | ||
Std. Deduct. | $24,400 | ||
Act. Deduct. | $24,400 | ||
QBI deduct. | $0 | ||
Taxable | $97,600 | ||
1040 Tax | $12,349 |
Opps!!I found something else, please address it.Tables below show what I see with the above inputs:
I added one cell in the above setup,
D27 = $100,000 LTCGs
This caused the tax due to drop to $1,028 vs the $10,549 I had before I added $100,000 of LTCGs.
Paycheck frequency: | Annual | Annual | |
Paycheck Items | Earner #1 | Earner #2 | Annual |
Non-paycheck income | Annual | Annual | Annual |
Qualified dividends | $12,000 | $12,000 | |
Long term capital gains (LTCG) | $100,000 | $100,000 | |
tIRA distribution | $110,000 | $110,000 | |
1040 Total Income | $222,000 | $0 | $222,000 |
1040 AGI | $222,000 | ||
Income Taxes | |||
Federal tax | $27,349 | 2019, MFJ, std. | $27,349 |
Medicare premium | $3,634 | 0 | $3,634 |
Total income taxes | $30,983 | $30,983 | |
Filing Status | 2 | 1=S, 2=MFJ, 3=HOH | |
Adult #1 | Adult #2 | ||
Age | 64 | 60 | |
Full-time student? | 0 | 0 | |
AGI | $222,000 | ||
Std. Deduct. | $24,400 | ||
Act. Deduct. | $24,400 | ||
QBI deduct. | $0 | ||
Taxable | $197,600 | ||
1040 Tax | $27,349 |
I have made very simple entries on the spreadsheet.
D25=$12,000 Qualified Dividends
D31=$110,000 tIRA distributions
G2=2 Filing Status MFJ
G8=64 Age
H8=60 Age
The tIRA will be put into a Roth as a Roth Conversion.
I see the tax due is $10,549, this is 9.59% tax.QuoteOpps!!I found something else, please address it.Tables below show what I see with the above inputs:
I added one cell in the above setup,
D27 = $100,000 LTCGs
This caused the tax due to drop to $1,028 vs the $10,549 I had before I added $100,000 of LTCGs.
Paycheck frequency: Annual Annual Paycheck Items Earner #1 Earner #2 Annual Non-paycheck income Annual Annual Annual Qualified dividends $12,000 $12,000 Long term capital gains (LTCG) $100,000 $100,000 tIRA distribution $110,000 $110,000 1040 Total Income$222,000 $0 $222,000 1040 AGI$222,000 Income Taxes Federal tax $27,349 2019, MFJ, std. $27,349 Medicare premium $3,634 0 $3,634 Total income taxes $30,983 $30,983
Filing Status 2 1=S, 2=MFJ, 3=HOH Adult #1 Adult #2 Age 64 60 Full-time student? 0 0 AGI $222,000 Std. Deduct. $24,400 Act. Deduct. $24,400 QBI deduct. $0 Taxable $197,600 1040 Tax $27,349
I'm going to assume OpenOffice is the culprit and find a copy of Excel.That seems reasonable.
Well, ahem, I found a copy of Microsoft office 2000 in my pile of discs.It should, and does for me.
So now I get the same answer as you with, ( it also autofills the B column, OpenOffice didn't)
D25= $12,000
D31= $110,000
$12,349 tax due (same as you)
But when I add LTCGs,
D27=$100,000
The tax due is reduced to $2,828.
Cell B42 is $222,000 and Cell D42 is $122,000.
I would think D42 should increment to $222,000.
I'll be happy to find a later edition of Excel, but want to know if I'm missing somethingCan't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27.
that prevents Cell D27 (LTCGs) from add to Cell D42.
QuoteI'll be happy to find a later edition of Excel, but want to know if I'm missing somethingCan't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27.
that prevents Cell D27 (LTCGs) from add to Cell D42.
Is the workbook set to automatically calculate? E.g., does hitting the F9 key change things?
I have a current Office 365 version, but addition, subtraction, and if-then-else logic should be the same.QuoteI'll be happy to find a later edition of Excel, but want to know if I'm missing somethingCan't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27.
that prevents Cell D27 (LTCGs) from add to Cell D42.
Is the workbook set to automatically calculate? E.g., does hitting the F9 key change things?
I went through Cell D23 through D39 and put in $100,000 one at a time, each one did as I thought it should except D26 and D27, they didn't change anything.
Yes it does auto calculate.
What year Excel do you have?
Thanks again for helping.
I went through Cell D23 through D39 and put in $100,000 one at a time, each one did as I thought it should except D26 and D27, they didn't change anything.What is the content of cell D28? Is it the formula shown below?
I went through Cell D23 through D39 and put in $100,000 one at a time, each one did as I thought it should except D26 and D27, they didn't change anything.What is the content of cell D28? Is it the formula shown below?
=IF(M59>=0,M59,M64)
If not, it should be. Removing protection, then inadvertently overwriting the formula in that cell, would be a plausible scenario....
Everything is correct now. D26 and D27 work as they should.Great! All's well that ends well!
It looks like I created most of my problems when D28 autofilled after I put in some LTCGs, I corrected that and well ya, I should have left that the way it was.
My question now is, for tax planning do I want income until an additional $100 costs me $15 or do I just want to pay 12% of my total income. (I'm doing Roth Conversions for most of the income.)Excellent question.
Before going on - does that work?
Now that is probably an Excel version issue, if you are using Excel 2000. See the post for Version 11.00 (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496).
Before going on - does that work?
Arrgh! No, I used the dropdown changed Cell G107 to D31, then went to L115 and found,
Update Chart and clicked. Chart heading did not change, nor did the chart.
My husband and I have regular jobs (w-2s) and a rental property. However, I am also part of an LLC (with a partner) that owns a few rental properties. On lines 33-36, do I just put our personal rental or do I add the personal and my half of the LLC properties here? If only personal, where does the LLC info go?You tell me. :)
As a side note, was it your understanding that we also had to do columns E-L, line76 so that it would fill in B76?Yes. Per the Instructions tab: " - For mortgage and personal loans, enter the original principal, length, and interest rate in cols. E, F, and I in the appropriate rows. Excel will calculate the payment."
As I said earlier, I have several rental properties (with mortgages). I’d put my half of the mortgage in this section, right? Also, I am doing my spreadsheet for 2018. I had a small loan in 2018 that is now paid off. But I need to include it here anyway? And I’m assuming I should do the loans at what they would be in December of 2018, not January? Are my questions making sense?If you are doing this for tax estimation purposes, and you want to look specifically at 2018 taxes, use the 2018 version linked at Case Study Spreadsheet updates (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/). Otherwise, using the most recent version is usually best.
All right!
Thanks @MDM for the pointer.
@SquashingDebt , I really appreciate your patience. I’ve completed columns A-D, through line 130. Is that where you are? As a side note, was it your understanding that we also had to do columns E-L, line76 so that it would fill in B76? I THINK I’m starting to understand MDM’s madness!
I think lines 133-137 are next. It looks like we’ll have to do columns E-L in addition to A-D. I do have some questions… As I said earlier, I have several rental properties (with mortgages). I’d put my half of the mortgage in this section, right? Also, I am doing my spreadsheet for 2018. I had a small loan in 2018 that is now paid off. But I need to include it here anyway? And I’m assuming I should do the loans at what they would be in December of 2018, not January? Are my questions making sense?
@philli14 and @FireKingGreen – are you still joining us? Have you completed A-D, rows 1-130? Do you need any assistance? Do you want us to wait or are you ready to proceed?
@MDM - what do you think about changing "beauty shop" to "salon" or something like that? Beauty shop, at least in my experience, isn't really a common term these days and feels unnecessarily gendered. (Men get manicures too!) It stuck out to me as I read down the list.Sure, no strong feeling about "beauty shop." Most of those categories were copy&pasted from various budgets people published in their case studies before this spreadsheet was available.
One question before moving on. Child support doesn’t count as income, but it is a line item in my budget and I did not see a row listed. Thoughts on where to include or am I completely missing it?Perhaps "Other untaxed income", cell B72, would work?
@MDM - what do you think about changing "beauty shop" to "salon" or something like that? Beauty shop, at least in my experience, isn't really a common term these days and feels unnecessarily gendered. (Men get manicures too!) It stuck out to me as I read down the list.Sure, no strong feeling about "beauty shop." Most of those categories were copy&pasted from various budgets people published in their case studies before this spreadsheet was available.
Because this is a spreadsheet, and changing rows can require documentation changes (and sometimes real reference changes), how about something like "Beautician/personal grooming" instead? That would keep the same alphabetical order for expenses that come after the housing ones.
Actually, any name that keeps the replacement for "Beauty Shop" between
"Home/Rent Insurance"
and
"Charitable contributions" would work.
Suggestions?
One question before moving on. Child support doesn’t count as income, but it is a line item in my budget and I did not see a row listed. Thoughts on where to include or am I completely missing it?Perhaps "Other untaxed income", cell B72, would work?
MDM have you ever considered adding a formatted section to the top of this workbook to show the main items that change as information is entered into cells? I use a watch window but newer excel users might want a section that's always visible at the top of the sheet with a freeze pane added in.I've given it some thought, but not much, mostly because what thoughts I did have bounced among various things one might want to look at. I use View>Split sometimes, mostly to look at how the marginal rate chart changes with some cell content.
Thanks so much for this sheet. I love that when it tells me something I'm not expected, I can actually go through step by step to see what I've been forgetting in my own planning exercises!You're welcome! I wish there was some Artificial Intelligence add-on that would automate the explanation of odd-looking marginal rates, but that seems too much work.... ;)