Author Topic: Case Study Spreadsheet club (like a book club...only with a spreadsheet...)  (Read 9847 times)

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
I love, love, love MDM's case study spreadsheet (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/).  But every time I open it, I am quickly overwhelmed and make no progress on filling it out.

I thought it might be worth seeing if any forum folks would be interested in working through the spreadsheet together.  We can ask questions, suggest answers, provide encouragement, etc.  @MDM even said he would help us! Maybe we can do a section a week and see how it goes.

Any takers?

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
I might be interested!  I've certainly had the same experience with it so far, haha.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
@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.

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?


MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
@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.

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?
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.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
Absolutely!  I'm not all that bright so if I can understand the instructions, anyone can!

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
@Ladychips Sounds great!  Sorry for the delayed reply - just had a brutal 2 days at work.  I'll tackle that first box today or tomorrow and report back!

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
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.


@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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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.


@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.
Good questions!

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.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
@SquashingDebt - yea!  Progress.  Thanks for the motivation - it helped me work through the box this morning!

Our situations are really different.  I think that means we'll learn a lot from our different perspectives. I'm 50+, married, W-2 jobs, 403b/457s, and rental property.  Looking to retire in 2 years (and 2 weeks).

@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!

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
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. 

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!!

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
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.


@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.
Good questions!

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.

Interesting... that's a good reminder that I need to get organized with my HSA.  I just entered the workforce 4 years ago after finishing my PhD and spent the first 3.5 years in student loan repayment mode, so I've been using my HSA as intended - to pay medical bills.  Now that it's around $7k and my student loans are gone, I need to figure out how to invest it and start paying my medical bills out of my cash flow and letting my HSA grow.

Looks like this "spreadsheet club" is already paying off!  (Also I love spreadsheets and am having fun in general, haha.)


@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

  • Senior Mustachian
  • ********
  • Posts: 11477
@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?

Not sure how to give specific advice on W-2 box 14, other than point to https://www.irs.gov/pub/irs-pdf/fw2.pdf:
Quote
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?

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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. ;)

Quote
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.

One needs to know these numbers to file taxes at the end of the year.  Estimating these numbers during the year can help avoid surprises at tax filing time.  Perhaps think of these as your "income budget" (similar to how your "expense budget" might be used when you get to the Monthly Average Expenses in rows 76-129.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
@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.
« Last Edit: June 16, 2019, 11:22:18 AM by MDM »

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
@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 :)

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
@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 :)
Thanks for the suggestion - easy enough to add for whenever a new version is published.

Probably not going to try to cater to those who have never used a spreadsheet at all, but would like to think that it can be accessible for anyone with "some" spreadsheet knowledge.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
@MDM , thanks for your feedback.  Yes, I like the proposed instructions re. the W-2 boxes...every little bit helps.  And 'duh' on the mortgage interest change.

After looking more closely, I think it would have been smarter to just keep going down the page instead of moving over a section.  So, I'm proposing we do Columns A-D, rows 22-41 for next time.

This week's boxes were fairly simple and straightforward.  Yea!

@SquashingDebt, you still with us?? 


MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
^Looks good.  Keep the observations coming!

Cell comment changes have been made in the next version.  Not sure when I'll decide to upload that: it's usually one or more of
- correct a blatant error
- provide a significant enhancement
- accumulated a bunch of little things.

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
@Ladychips thanks for the reminder!  Just did this week's section.  I agree, pretty straightforward.

@MDM - One comment this time - is this the AOC referenced in H30?  https://www.irs.gov/credits-deductions/individuals/aotc

It might be worth switching to AOTC, since it looks like that's the acronym the IRS uses?

When I googled AOC taxes I got a lot of articles about the person AOC (Alexandria Ocasio-Cortez) and her taxes, haha.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
^Hah!  Yes, no political opinions intended or implied there.  Changed cell text to AOTC and spelled out the whole name in the comment.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
@MDM , I'm in no hurry for a new version.  I so appreciate your helping us work through it!

@SquashingDebt, glad you are still with us!!  I'm thrilled I'm not having to do it alone!

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
Just did this week's section!  It was super easy for me because everything was zero except for the taxable interest from my savings account.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
See https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/msg2405462/#msg2405462 if interested in the comment and instruction changes made based on this thread.  Won't make that an "official" version at least until receiving feedback on the GA state tax changes.

DadJokes

  • Handlebar Stache
  • *****
  • Posts: 2360
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.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
I haven't done my section yet!  Internet issues most of the weekend.  Hope to work on it tonight!  @SquashingDebt, do you have a suggestions for this week's work?  Just the next section down?

philli14

  • Bristles
  • ***
  • Posts: 325
I'm gonna join in with you two - I will try to catch up to where you are and then move forwards with you!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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.

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
@philli14 - Whoohoo!  So glad you are going to join us! 

FireKingGreen

  • 5 O'Clock Shadow
  • *
  • Posts: 12
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)

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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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)

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.
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.

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
Welcome to our new spreadsheet club members!

@Ladychips, for the next section, I vote A-D, 44-73.  It's quite a few cells but not too many values to enter, and it takes us to the end of the income section.  And that should hopefully be manageable for our new members to catch up.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
I've been trying to use OpenOffice, I'm have some issues like massive income and no tax.
 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.
Now add in some tIRA distributions.
B31=$80,000

 At this point my spreadsheet still shows $0 on line I64
It does show $1626 on line B65 Medicare Premium.
 G61 shows 2 and H61 shows 3.
 Does anyone agree with my numbers?

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
I've been trying to use OpenOffice, I'm have some issues like massive income and no tax.
 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.
Looks good.  You have 64 and 60 in G8 and H8?

Quote
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.

There should be nothing at all in B28.  What is the $90,750?

Quote
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.

Quote
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.

Quote
It does show $1626 on line B65 Medicare Premium.
Yes.

Quote
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.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
I've been trying to use OpenOffice, I'm have some issues like massive income and no tax.
 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.
Looks good.  You have 64 and 60 in G8 and H8?

Yes

Quote
B25=$12,000
B28=$90,750
 At this point I64 still shows $0 of tax owed.
Quote
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.
Quote
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.
The $90,750 is $78750 LTCGs plus $12,000 left over from the $24k Standard deduction minus my $12k income.

Quote
Now add in some tIRA distributions.
B31=$80,000
Quote
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.

Quote
At this point my spreadsheet still shows $0 on line I64
Quote
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.

 I now have agreement!

Quote
It does show $1626 on line B65 Medicare Premium.
Quote
Yes.

 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?

Quote
G61 shows 2 and H61 shows 3.
Quote
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 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.

   Thank you for all your patience, and all the work you did on your spreadsheet.
« Last Edit: July 03, 2019, 09:38:11 AM by BTDretire »

Ladychips

  • Handlebar Stache
  • *****
  • Posts: 1448
Welcome @FireKingGreen and @BTDretire!  It's a party now!!  @MDM , thanks for the plug on the other thread.  And more importantly, thanks for all the help here.  So excited!

Now if I could just get internet a little more reliably at home, I'd be doing great!

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
 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.
 Even though that seems low, I've been paying 1% to 4%
when I was working self employed, because I had many deductions, SEP, HSA,
health Insurance, 1/2 of SS, students credits.
 Have I missed anything and should I just bite this bullet and pay the tax.
Opps!!I found something else, please address it.
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.
 

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Quote
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.
Quote
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.
The $90,750 is $78750 LTCGs plus $12,000 left over from the $24k Standard deduction minus my $12k income.
To clarify: inputs go in the green shaded cells, not necessarily in a particular column.

The standard (or itemized) deduction should play no part in the entries for income.  The spreadsheet will apply the appropriate deduction between cells G11 and G16 for you.

Quote
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?.  The spreadsheet calculates the "coming in two years" medicare premium so one can include that effect when considering large Roth conversions, etc.

Quote
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.

Quote
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!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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.
Tables below show what I see with the above inputs:

Paycheck frequency:AnnualAnnual
Paycheck ItemsEarner #1Earner #2Annual
Non-paycheck incomeAnnualAnnualAnnual
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,3492019, MFJ, std.$12,349
Medicare premium$1,6260$1,626
Total income taxes$13,975$13,975

Filing Status21=S, 2=MFJ, 3=HOH
Adult #1Adult #2
Age6460
Full-time student?00
AGI$122,000
Std. Deduct.$24,400
Act. Deduct.$24,400
QBI deduct.$0
Taxable$97,600
1040 Tax$12,349


Quote
Opps!!I found something else, please address it.
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.
Tables below show what I see with the above inputs:

Paycheck frequency:AnnualAnnual
Paycheck ItemsEarner #1Earner #2Annual
Non-paycheck incomeAnnualAnnualAnnual
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,3492019, MFJ, std.$27,349
Medicare premium$3,6340$3,634
Total income taxes$30,983$30,983



Filing Status21=S, 2=MFJ, 3=HOH
Adult #1Adult #2
Age6460
Full-time student?00
AGI$222,000
Std. Deduct.$24,400
Act. Deduct.$24,400
QBI deduct.$0
Taxable$197,600
1040 Tax$27,349

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
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.


Quote
Opps!!I found something else, please address it.
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.
Tables below show what I see with the above inputs:

Paycheck frequency:AnnualAnnual
Paycheck ItemsEarner #1Earner #2Annual
Non-paycheck incomeAnnualAnnualAnnual
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,3492019, MFJ, std.$27,349
Medicare premium$3,6340$3,634
Total income taxes$30,983$30,983



Filing Status21=S, 2=MFJ, 3=HOH
Adult #1Adult #2
Age6460
Full-time student?00
AGI$222,000
Std. Deduct.$24,400
Act. Deduct.$24,400
QBI deduct.$0
Taxable$197,600
1040 Tax$27,349

 My 1040 Total income (line 42)  does not add in D25 and D27, all it sees is D31 tIRA distribution.
Unless you see something I missed, I'm going to assume OpenOffice is the culprit and find a copy of Excel.
 If anyone has a copy of OpenOffice and can run this scenario, and either rebuke or confirm, I
would appreciate it.

   

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
I'm going to assume OpenOffice is the culprit and find a copy of Excel.
That seems reasonable.

If you start using Excel, do read the posts for Version 11.00 and Version 11.17 regarding warnings Excel might display.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Well, ahem, I found a copy of Microsoft office 2000 in my pile of discs.
 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 something
that prevents Cell D27 (LTCGs) from add to Cell D42.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Well, ahem, I found a copy of Microsoft office 2000 in my pile of discs.
 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.
It should, and does for me.

Quote
I'll be happy to find a later edition of Excel, but want to know if I'm missing something
that prevents Cell D27 (LTCGs) from add to Cell D42.
Can't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27. 

Is the workbook set to automatically calculate?  E.g., does hitting the F9 key change things?

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074


Quote
I'll be happy to find a later edition of Excel, but want to know if I'm missing something
that prevents Cell D27 (LTCGs) from add to Cell D42.
Can't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27. 

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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477


Quote
I'll be happy to find a later edition of Excel, but want to know if I'm missing something
that prevents Cell D27 (LTCGs) from add to Cell D42.
Can't think of anything that would cause B42 to calculate but not D42, when a number is entered in D27. 

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 have a current Office 365 version, but addition, subtraction, and if-then-else logic should be the same.

Here's one approach (see link for details) to troubleshooting.  When you enter a number in D26 or D27, the sum of D26 and D27 should appear in D28 and be added to AGI, unless you have capital loss carryovers entered in M55 or M57.


MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
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....

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
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....

 No, I didn't have =IF(M59>=0,M59,M64) inD28, I had 0.
So I closed the spreadsheet and opened a new sheet.
 Everything is correct now. D26 and D27 work as they should.
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.
 Finally happily testing scenarios, but finding, I really don't have many choices,
other than, how many dollars do I want to pay!

 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.)

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Everything is correct now. D26 and D27 work as they should.
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.
Great!  All's well that ends well!

Quote
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.

You should partition the income into "fixed" and "optional" buckets.  In the "fixed" bucket goes any "unavoidable" (e.g., dividends on your current investments) income and, if that is not enough, other income you need for living expenses now.  That other income may be non-taxable (e.g., withdrawals from bank accounts), or partially taxable (e.g., the capital gain amount of investment liquidation).  Whatever tax you pay on the "fixed" bucket is, well, fixed.

The issue is the tax rate on optional income (your "marginal tax rate") now, vs. what it would be if you choose (or are forced, via RMDs) to take it later.  To answer your question in short, it's "...until an additional $100 costs" now more than whatever rate you expect later.  See Marginal Vs Effective Tax Rates And When To Use Each.

Due to the way taxation of Social Security benefits works, the marginal rates after SS benefits start may be higher than one expects.  You might generate some "what if?" charts that include your expected SS benefit amounts.

Speaking of charts, now that you have Excel working, change cell G107 from B11 to D31 and click the "Update chart" button near cell L115.  The chart title should change to "Total Tax Rate vs. tIRA withdrawal".  You can change the number in P83 to show larger or smaller amounts on the x-axis.

Before going on - does that work?

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074

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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477

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.
Now that is probably an Excel version issue, if you are using Excel 2000.  See the post for Version 11.00.

Prior to v11.00, one had to change things manually.  Sorry but I don't recall the exact menu structure in older Excel versions.  In general:
 - The Calculations tab must be Unprotected.
 - After selecting cells P83:R584, open the Data Table menu item and enter the cell you wish to vary in "Column input cell:".

SquashingDebt

  • Bristles
  • ***
  • Posts: 441
Did the next section (A-D, 44-73) with no issues.  Once again it was mostly zeros for me, except in this case the Roth IRA box.

@Ladychips, you caught up?  Or still having internet issues?