Budget Spreadsheet

[Rustic23]

I posted this about a year ago, however, I did not get many comments on it.

My attempt was to model 25 years of spending. The main spreadsheet is locked. However there is no unlock password, so you can unlock it.

The green area is where you enter your assumptions. Cells A12 through 036 give you the results. I tax 85% of social security. In cell’s M68 and M69, you can put in a year to begin inflation or saving rates. Savings rates are based on changes in the DOW. The worksheet ‘Monthly Expenses’ was designed to allow you to track your expenses over a year. The ‘Sinking Fund’ spreadsheet is an attempt to come up with a monthly amount necessary to fix thing that go bump in the night.

There are four income columns. Two use inflation rates by year, and the center two use a fixed rate you can input at the bottom of the column.

I make no guarantee that the formulas used in this spreadsheet are right. In fact, it seems to be more pessimistic than FIRECALC. One thing if your Savings or Checking goes negative, you will have to take a bigger percentage out of your Savings, as you can see in the last four years in the sample.

Also I included a ‘Print’ macro, so this is what Excel is warning you about when it starts. Oh, and I know the age is off when one would start Social Security. It is sample data.

 

[EngineeringMyFinances]

Saw a few problems

You reference cell K63 as an inflation factor, but the formulas for Q74:T74 and below reference K68. Neither of these cells seem to be for entering an inflation factor. Maybe you meant cell Q68. But if you have a variable inflation factor, wouldn't you want to adjust tax brackets each year accordingly?

And in any case, the government is not indexing the $32,000 exclusion amount for taxing SS income for a married couple. Also, 1/2 of SS income is added to other income for testing against the exclusion amount. While the first part over the amount is taxed at 50% before being taxed at 85%, the reality is that given the figures you show you probably will have 85% of your SS taxed.

The standard deduction for 65 and older is higher than for younger folks.

 

[Rustic23]

I am not sure what you are refering to in cell k63? Cells M68 and M69 are for entering a 25 year range for dow growth and inflation. You will notice Col M and O change when you enter a date in these.

While it has been a while since I fiddled with the SS calculations, my intent was to use the 85% taxed figure. I figure it was worse case and by the time I got there I would be in that range with everybody else.

Thanks for taking to time to look it over. I was not aware of a higher standard deduction for over 65. Every little bit will help.

 

[EngineeringMyFinances]

The text in cell K71 describes an inflation adjustment in cell K63. But K63 is in the middle of your expense range of cells.

The calculation in for example cell Q74 example (as well as others nearby) refers to cell $k$68. Which seems to be an inflation adjustment in the form of the calculation. But cell k68 is ... not sure.

I also use an 85% factor for taxing SS when projecting my retirement income. But probably it will be raised to 100% by the time I get there.

 

[Rustic23]

Got it.
Cell q74 should use the inflation rate in 043 and the other in that column 044,045 and so on. This is also true for s74 and t74. In a previous version I did not use a historical inflation rate but a fixed rate that I stored in K63. I think this should fix this. I'll see if I can upload a changed spreadsheet.

Thanks for the catch.

Here is a new spreadsheet.

 

[EngineeringMyFinances]

I have a somewhat similar spreadsheet. For estimating taxable SS, I use a formula that is the maximum of
(1) 85% of other income and
(2) 85% of SS income.
I don't calculate the "SS Offset" (which BTW is not indexed for inflation). I hope that a smaller amount is actually taxed, but I'm planning conservatively.

I'm still in the accumulation phase, so what my spreadsheet estimates is the income I would get when retiring at a particular age based on an estimated SS Primary Insurance Amount and the reduction factor for the age. My retirement savings balances are estimated based on current balance and savings rate, with no gains or losses estimated. Withdrawals are planned to be based on living to the age of 100, again with no gains or losses.

Am I being overly conservative? I very, very much hope so. But I'm around a decade away from retiring. When I get closer, I'll look at my portfolio and start studying withdrawal strategies in greater detail using tools like FireCalc. I hope that I'll be in a position to either retire earlier or withdraw at a higher rate.

A couple of other differences between our spreadsheets: I don't adjust for inflation, but do assume that my savings will keep up. (So I do plan on some gains, just not any exceeding inflation.) By leaving inflation estimates out of the calculations, when I look at the income estimates I don't have to keep backing out the accumulation of inflation when deciding whether it'll be enough -- I simply think in terms of 2007 dollars. Also, since Excel has plenty of rows, I have it calculate an estimate in increments of 1 month.

Our spreadsheets are not something that someone else is likely to be able to use, or even want to. But then they don't have to be user-friendly or versatile to handle other situations. They just have to accomplish the task for ourselves that we designed them to do. Or what we redesign them to do when our situation changes.