Monster Retirement Budget Rev 1 - Peer review

[bobebob]

All,
I have updated my retirement spreadsheet using much of the feedback I received from my earlier posting.
I changed some of my assumptions:

• Market return: 8.5% (1.5% below actual market return for past 100 years)
• Inflation: 3.5%
• Since the market return is not inflation-adjusted, I applied inflation to my expenses (after un-padding them) from now until retirement.
• I made a cell for the percentage of Brokerage withdrawals that are taxable. Long Term Capital Gains and Qualified Dividends will likely be taxed at the 15% rate based on my tax bracket for most of my retirement.
  My tax bracket will be 25-33% so say 28% on average. So 15% is a little more than half of 28%, therefore if I count a little more than half of my withdrawals from my brokerage account (say about 55%) as taxable, I would essentially be making the treatment equivalent.

Here are some of the major features I "upgraded":

• Included the Personal exemption I missed.
• Added state taxes into the taxes, duh.
• Calculated and graphed my effective tax rate.
• Automated the use of traditional 401K funds if I retire between 55 and 59.5 (my employer's plan allows it)
• Automatically combined all pre-tax and post tax accounts separately into appropriate IRA accounts at age 59.5 (makes things simpler)
• Automated the calculations and balance transfers to use the 72t rule if I retire prior to 55. (although it doesn't look like I will be able to retire quite that early) The calculation is based off of a desired monthly withdrawal and compares the balance that would be required to produce it using RMD rules with the available pre-tax account balances and gives a "go" - "no go" decision on the planning sheet.
• Automated the calculation of Required Minimum distribution in my Trad IRA and put a "go"-"no go" indication on the Planning sheet if RMD is violated with my plan.

I tried to upload rev 1 to this post, but it now exceeds the 2M limit (at least with the .xls extension). So here is the link to share it from Amazon Cloud:
https://www.amazon.com/clouddrive/share/rf7sJuct26s2mIOmZ4Ewvo2oE0hAJdwvgC581BMMJME

Thanks to everyone for the suggestions and help with pointing out errors. Please let me know if you have any further suggestions or see any more errors.

 

[pb4uski]

For my taxable account I start with the current market value and current cost basis. I assume that 1) dividends are 2% annually and are taken in cash and are qualified and 2) the fair value will grow at the earnings rate (8.5% in your case) less the dividend yield.

Then any sales to fund cash flow needs are assumed to be weighted average cost and fair value is reduced by the proceeds and basis is proportionally reduced. The taxable gain is the proceeds for the year times the unrealized gain % at the beginning of the year* and the tax is the taxable gain times the relevant tax rate.

*(market value at BOY- cost basis at BOY)/market value at BOY)... about 30% for me

So if I have $100 of proceeds and the relevant rate was 15% then the tax would be 4.5 ($100 * 30% * 15%). I think you would get 14 ($100 * 50% * 28%) which still seems quite high unless your proceeds are 93% gain.