Share Your Magical Spreadsheet?

[racy]

... but I'd love to see how we stand using some of other peoples' decision tools. Thanks!

My decision tool was this:
Do I have 33X what I need from my diversified, moderate growth portfolio?
If no, keep working, saving & investing.
If yes, retire if ready emotionally.

 

[racy]

"I'm trying to nail down why (a) $1M today is deemed enough for the $40k example, but (b) only 3% can be taken out in the second one.

It has to do with Sequence of Return Risk. Check this:
https://www.bogleheads.org/wiki/Trinity_study_update

 

[tb001]

OP, I’m glad you asked this. I’ve often thought it would be nice to convert member spreadsheets to google docs and have a folder where people could download them, even just as a starting point. I’ve found some good basic spreadsheets by searching on bogleheads.

Mine is pretty extensive for modeling expenses, but the areas I have a harder time getting my head around are modeling taxes and modeling drawdowns in a tax efficient way.

I’ve tried I-orp, but because we have young kids, our spending changes over time and it doesn’t let us model spend in a front end loaded way. I also think the tax estimates are really high vs what I calculate. I’m not sure if it’s because we have deductions for family and medical spend that aren’t accounted for, if I-orp is making aggressive growth assumptions on the portfolio or I’m making some mistake calculating our taxes. It’s the area where I feel my numbers are most subject to error.

 

[T-Minus]

I am not confident enough in my abilities to be able to create a spreadsheet that rivals tools produced by those who specialize in this field. However, what I've done is create a true breakdown of my available retirement assets that can be input into various tools. I then run six separate tools (FIREcalc, Personal Capital's retirement planning tool, Fidelity's Retirement Planner, ER Now's v2.0 tool, Living off Your Money's planning spreadsheet, and VPW) - each with the most conservative options for a 100% success rate. I then record the values of each, use a minimum function to find the lowest value, then apply a correction factor to (hopefully) mitigate any early retirement Sequence of Returns risk. My formula looks something like this:

=MIN(O23:T23)*(1-(0.05)) for year 1
=MIN(O23:T23)*(1-(0.04)) for year 2
=MIN(O23:T23)*(1-(0.03)) for year 3
...and so on.

I knew I could retire when the allowable spend exceeded our target retirement budget amount. Then, I worked another year to reduce risk (increased investments, life expectancy reduced by 1 year), increase our travel fund, and to buy my dream car. It was then that I pulled the plug. After 1-1/2 years retirement, we're ahead of plan, but this is too short a time to really tell, and we haven't yet been through a big correction.

As a final sanity check, I had a for-fee financial planner review my numbers. He told me I was one of the most financially conservative people he'd ever encountered, and that I could spend a LOT more than I was planning to. I told him that the only safety net I have is that which I can construct for myself, and that there was no second chance at getting things right.

 

[Sunset]

FireCalc and, for me even better, Fidelity Retirement Planner were way better than anything I would have been able to create through a spreadsheet. I had people that worked for me over the years that built massive, complex spreadsheets and I truly admired their abilities, but make one myself? Let’s just say I was good at delegating.

I did however make some crude spreadsheets just to help confirm my understanding of the online tools. Sort of a check and a reasonableness test.

In the end, as has been said, I found that truly understanding my spending was key to becoming comfortable with retiring. I seemed easier to comprehend the potential income and longevity of my investments but expenses needed more. After understanding basic spending, the tricky numbers were (are) healthcare and taxes, which includes RMD’s.

I managed to make a spending spreadsheet, this alone has made me more comfortable with being retired.
I just used firecalc, and a bunch of other online retirement calculators and when they all said we would be fine, I pulled the pin.

 

[Sunset]

OP, I’m glad you asked this. I’ve often thought it would be nice to convert member spreadsheets to google docs and have a folder where people could download them, even just as a starting point. I’ve found some good basic spreadsheets by searching on bogleheads.

Mine is pretty extensive for modeling expenses, but the areas I have a harder time getting my head around are modeling taxes and modeling drawdowns in a tax efficient way.

I’ve tried I-orp, but because we have young kids, our spending changes over time and it doesn’t let us model spend in a front end loaded way. I also think the tax estimates are really high vs what I calculate. I’m not sure if it’s because we have deductions for family and medical spend that aren’t accounted for, if I-orp is making aggressive growth assumptions on the portfolio or I’m making some mistake calculating our taxes. It’s the area where I feel my numbers are most subject to error.

I have gotten a boogle head spreadsheet, I finally gave up on it, as it's so complex, even with the instructions it was confusing.

 

[IrishMoss]

I notice that the $60k/year example does not include the phrase concluding the $40k/year example of "this assumes you will maintain an equity allocation..." Is this purposeful and important?

No I just got tired and didn't type it out. These estimates always assume an equity allocation will be maintained of somewhere between 40% to 60%. Higher is fine. Lower is too low.

I mention this because comparing the two examples, the withdrawal rate is reduced 25% from the 40k to 60k examples (4% down to 3%), while the amount taken out is increased 50%, but the "total nest egg" for $60k/year is 100% higher than for $40k/year.

I gave 2 examples. The second example is a more conservative estimate since the withdrawal % per year is only going to be 3%. Most people do fine with 4%/year, which is usually conservative enough, but there are folks who want to be extra cautious and that shows how much more of a nest egg would be needed before retiring if such a person only wanted to withdraw 3% a year from their portfolio.

Now, $40k/year for 30 years = $1.2M, and $60k/year for 30 years is only $1.8M, so I'm trying to nail down why (a) $1M today is deemed enough for the $40k example, but (b) only 3% can be taken out in the second one. I have an "bad feel about this" (thank you Mr. Solo) that one of the first responses will result in a "slap the forehead" moment, but I'm willing to take that risk!

It's just the math.

In example 1 (think: person A) I assumed the yearly expense total was $40K and the standard 4% safe withdrawal rate (SWR) was used. To cover $40K yearly expenses on average and planning to withdraw 4% from the portfolio in year #1 retirement and then adjusting for inflation in subsequent years, requires a minimum portfolio of $1MM.

In example 2 (think: person B) I assumed the yearly expense total was $60K and a much more conservative withdrawal rate was wanted by person B. I picked 3% as an example withdrawal rate to use. That is a very conservative withdrawal rate, IMO. But some people are just that conservative. You should notice that when the withdrawal rate is lower (than 4%) the required nest egg amount is larger.

Again, it's math.

 

[scrabbler1]

My main ER spreadsheet included several sections in an effort to simulate my ER cash inflows and expenses on a monthly basis. The income side included the number of shares from each bond and stock fund after retiring and an estimated cents per share per month for each bond fund. The "big" bond fund is the one which mattered the most, of course.


The expense side, meanwhile, used my current expenses with the following main adjustments: paying more for my health insurance than I had been paying for COBRA (I had already lined up an individual HI policy; this was for 209, pre-ACA); the elimination of FICA taxes, and the elimination of commutation expenses. I had already stopped contributing to my 401k.


I also made some smaller adjustments for income taxes and day-to-day cash expenses


One I estimated my annual expenses and annual investment income, I moved to the next section of the spreadsheet. This included annual totals by year for expenses and each income source (number of shares x dividends per share) along with separate inflation factors for medical and non-medical expenses. As each year goes by, I replace my estimated expenses and income with actual ones.


This section of my spreadsheet goes up to age 65 although I am mainly concerned with the data through age 60. Through age 60, I can have unfettered access only to my taxable accounts, not my "reinforcements" such as my frozen company pension, my rollover IRA, and Social Security.


For the longer term, I have used Fidelity's RIP program (I am a Fido client) which shows only a vastly improving financial picture once those 3 income sources begin materializing.


Since then, I have created a more detailed income tax spreadsheet. Before the recent tax law change, it did my income taxes on a 2-year basis to help me figure out if I should "bunch" my deductions to reduce my tax bill.

 

[FI_RElater]

My spreadsheet is set up as follows
1. list of assets
2. list of liabilities
3. current net worth
4. current annual retirement contributions
5. current salaries
6. defined-benefit projections
7. investment-projections

So, basically, the top half is what I currently have and am saving, and is pretty self-explanatory. The bottom is a wish list for the future. I have a few different scenarios based on how much I'm currently saving, how much I will in the future, and if I add additional contributions. Here are some screen shots of this:

wow, dirtbiker, you start at 6% growth? {mine was much lower and included possibility of negative values}

when I was putting together mine, I had already put together five years of spending.... then used avg plus 25% ( to get a value approximately 1+ sigma above and give enough room for error) for required income
(that's one tab)
then had separate tabs for each of our income (with no bonus or increase/ with varying levels of increases and varying bonuses (none/usual expected/full expected bonus).... including amounts from each to max out retirement plans (HCE or not)

tabs for current 401 k, with amounts to be added, along with conservative growth amounts (2%,4%,6%, and of course zero and -2%, as well) (never modeled above 6%.... but used 3%-5% as worse case inflation. Note: 5% inflation is very hard to overcome, fortunately the Fed targets only 2%)

{likewise for taxable accounts, pension, Roth's, and SS (early, at FRA, and at 70)}

Then, like Pb4uski, I had a separate sheet drawing from each of these earlier tabs, with similar headings and had crude estimates and likely spendable income. ( I didn't model taxes .... good thing I didn't as it would have been wrong and a real waste of time. ) I only went out to 80.... if it failed before then I knew retirement would be too risky. Only later did I find the other calculators (Firecalc, etc) to plug the final numbers in as a test.... mine was conservative enough that it always came back at 100%, even with the 25% increase I baked in (some I considered as part of the surrogate for taxes).

[in our initial years, we've pulled less than 2 1/2%, even with major outlays (HVAC replacement/surgeries/ new vehicle)....
we use a max of 3.5% for WD, until SS start for me , (likely at FRA but could wait until 70) and other SS would be more gravy when it starts]

 

[Sand101]

Yep - I found that what I wanted really didn't exist. So I created my own. Mine takes into account:

Account types - IRA, Roth, Taxable, Inherited IRA, HSA, Pensions
Debts including one timers, mortgages, etc.
Taxation including federal and state (at least a decent whack at this)
Roth conversions
Four types of withdrawal methods - fixed, variable, variations thereof.
SS calculation from salary history with bend points, expected payout, etc.
Both fixed and historical growth scenarios.

 

[sengsational]

Similar to other posters, for the all-years plan, I depend upon various web-based tools. And I've tried ER Planner (PC software). And I've tried that bogleheads spreadsheet, but I never was comfortable with it. I did make a spreadsheet that replicated i-orp calculations, but i-orp has moved on since then.


A long time ago I did share an asset allocation sheet on google docs. Nobody ever said anything to me about it. Doesn't make me want to invest time to "sanitize" a spreadsheet and write instructions for it if nobody uses it or uses it without saying anything.

 

[KCinBAMA]

Pralana Bronze

Windows/Excel Pralana Bronze is free and good enough for my purposes. https://pralanaretirementcalculator.com

The paid version offers more bells and whistles.

 

[dirtbiker]

wow, dirtbiker, you start at 6% growth? {mine was much lower and included possibility of negative values}

when I was putting together mine, I had already put together five years of spending.... then used avg plus 25% ( to get a value approximately 1+ sigma above and give enough room for error) for required income
(that's one tab)
then had separate tabs for each of our income (with no bonus or increase/ with varying levels of increases and varying bonuses (none/usual expected/full expected bonus).... including amounts from each to max out retirement plans (HCE or not)

tabs for current 401 k, with amounts to be added, along with conservative growth amounts (2%,4%,6%, and of course zero and -2%, as well) (never modeled above 6%.... but used 3%-5% as worse case inflation. Note: 5% inflation is very hard to overcome, fortunately the Fed targets only 2%)

{likewise for taxable accounts, pension, Roth's, and SS (early, at FRA, and at 70)}

Then, like Pb4uski, I had a separate sheet drawing from each of these earlier tabs, with similar headings and had crude estimates and likely spendable income. ( I didn't model taxes .... good thing I didn't as it would have been wrong and a real waste of time. ) I only went out to 80.... if it failed before then I knew retirement would be too risky. Only later did I find the other calculators (Firecalc, etc) to plug the final numbers in as a test.... mine was conservative enough that it always came back at 100%, even with the 25% increase I baked in (some I considered as part of the surrogate for taxes).

[in our initial years, we've pulled less than 2 1/2%, even with major outlays (HVAC replacement/surgeries/ new vehicle)....
we use a max of 3.5% for WD, until SS start for me , (likely at FRA but could wait until 70) and other SS would be more gravy when it starts]

I'm invested in 100% equities for the longterm, doing a high percentage index funds. Looking at the historical average of the stock market, I don't expect to earn an average of under 6%. If I do, I'll readjust and keep on going. The worst 20 year rolling period ever for the S&P 500 was 6.4%. I'm 25 years from retirement. I may start changing investments to more conservative and lower earning investments as I get nearer to retirement. We'll see. If so, I'll change my estimates.

 

[target2019]

Windows/Excel Pralana Bronze is free and good enough for my purposes. https://pralanaretirementcalculator.com

The paid version offers more bells and whistles.

The bronze sheet is very similar to one I saw many moons ago, at a financial advisor office. The way bronze works is similar (but better) to my own effort in 2014.

A result is quickly provided, and the Tabular Projection tab is the "money sheet." Of course all should be verified and common sense applied.

Thanks for posting.

 

[gayl]

I lived on my pension for 6 months to figure out if I could retire.

 

[IrishMoss]

Windows/Excel Pralana Bronze is free and good enough for my purposes. https://pralanaretirementcalculator.com

The paid version offers more bells and whistles.

I downloaded and tried out the Bronze version. I noticed it's overstating the level at which I can safely withdraw with 100% safety by at least $10K/year as compared to other tools I've tried out (FIREcalc, I-orp, and a couple others). I even lowered the expected ROI to pretty low levels, increased the tax % by quite a bit and it's still overstating on the Monte Carlo simulation. I 'refreshed' the Monte Carlo calculation each time just to make sure. I don't trust it as it's not "pessimistic" enough to be realistic.

 

[kgtest]

My tabs are [yearly summary (a graph comparing gross income vs expenses & taxes, projected)]; [Income (from present to 90 YO]; [survivor (what DW would get )]; Investment allocation (pie charts)]; [Expenses (current & projected)]; [PIA calculations (since SS only calculates if you are still working)]; Life Insurance payouts (to feed survivor chart)].

You got me beat on survivor lol! I guess I have DW blended into all my sheets. We have a saying at our house...

"What's his is mine and what's mine is mine!"

 

[gooddog]

I have a spreadsheet on WHERE to retire, that allows for direct comparisons of key factors that are weighted for importance to you & spouse. If anyone wants that one, just send a message. A couple of people have used it, and seemed to like it.

 

[NW-Bound]

Spreadsheet? What spreadsheet?

I just used Quicken and let it download my expenses for a couple of years, and also track my stash value.

When I looked at the expenses and saw that they were about 4% of portfolio value, I looked closer and asked myself if any of those expenses would be reduced in future years.

When things looked good enough, I just stopped work. Oh, I also ran FIRECalc to double check.

 

[Doc0]

I track expenses in Mint. I only need to track 1 cc and 1 bank account everything gets paid out of these 2 accounts. Mint allows a CSV file to be downloaded of expenditures, so I simply made a sheet of each month for a year and cut and paste the month's data from the CSV into its proper column. The data includes whether some transaction is a credit or a debit. I manually set credits to a negative of its value, so a $100 credit becomes a -$100 debit Then I autosum the expenditures. Takes 5 minutes and the results are accurate. A years expenditures is simply the 12 monthly sums and I come away with a record of all my expenses. My budget is a little irregular with some months more expensive than others, but it turns out the expensiveness of a one year's May for example closely matches the expensiveness the following year's May so I can understand when to move a little more money into the system. I track taxes separately, so my tax entry in the expenditure column gets set to 0 and the tax amount is stored to the right in a column called "notes".

The portfolio visualizer (PV) Monte Carlo module does calculations which closely matches Personal Capitals Monte Carlo engine. The PV calc has a line item in the report that gives you the perpetual withdrawal value for a given portfolio risk/return, typically in the WR = 3% ballpark or a little less. If you know your expenses and you know your perpetual WR you can easily understand "your number" based on data. You can "what if" inflation or bad SOR and get some idea how the portfolio will fare in those instances. My experience is accumulation is all about return since you have a W2 job and benefits to cover the risk in your life. When you loose the W2 and begin portfolio deflation risk management and tax efficiency becomes the prime objective. Less risk = smoother ride and a higher probability of success.

I don't pay much attention to FIREcalc since it's largely a calculator based on history .' PV Monte Carlo generates 10,000 likely futures and stratifies them on a Normal distribution giving you a range of possible futures. Since retirement is about not running out of money before you run out of life I think it's a good tool to understand your portfolio's limitations.

 

[jaywell]

https://drive.google.com/file/d/1GNjNrPj5i-iqz_nNEYLyhSlIeMp3yRCI/view?usp=sharing
hope you can get to this excel

 

[Mizterz]

Expense Categories

I feel that one of the most important steps is to understand your estimated expenses in retirement. I have attached a detailed expense sheet. I have shared it with many people that tell me they can retire on 30k per year. If you can, great. But it is well worth going through the exercise of reviewing all of your potential expenses.

Personally, I use this completed sheet as input to my retirement model.

 

[IrishMoss]

I also use Mint as well as Personal Capital. Mint tracks my expenditures the best as well as my monthly budget. P.C. is better for seeing my consolidated investments and metrics for that. Since I've used Mint for 8 years, it's provided a very accurate view of my spending over lots of months so I was able to determine a realistic yearly expense amount that I'd need to support/maintain to retire.

That is *the key* to determining the answer to "Are we there yet?"

You're *there* when your nest egg + any pension + expected social security benefits, added together and multiplied by 4% = your yearly expense amount or greater.

I use FIREcalc, which nicely answers the question: considering every market cycle in the past since the start of the stock market, based on my current nest egg, anticipated social security, and expected spending per year, will my nest egg support my spending and to what %? (100% is the best). Anything over 90% is considered a good plan.

The other thing FIREcalc answers for me is: based on the variables I input including my current nest egg and future income from social security, how much can I spend each year on average so my money will last for the entire span of time I entered, to a confidence level of <whatever % input>? (you could put 100% if you wanted to make sure there was no doubt your money would last).

 

[tb001]

I also use Mint as well as Personal Capital. Mint tracks my expenditures the best as well as my monthly budget. P.C. is better for seeing my consolidated investments and metrics for that. Since I've used Mint for 8 years, it's provided a very accurate view of my spending over lots of months so I was able to determine a realistic yearly expense amount that I'd need to support/maintain to retire.

That is *the key* to determining the answer to "Are we there yet?"

You're *there* when your nest egg + any pension + expected social security benefits, added together and multiplied by 4% = your yearly expense amount or greater.

Except if you're looking at a time horizon longer than 30 yrs, that 4% might be more like 3-3.5%.

Curious if you've compared quicken vs mint. I'm currently using quicken, but the budgeting tool is driving me nuts.

 

[IrishMoss]

Except if you're looking at a time horizon longer than 30 yrs, that 4% might be more like 3-3.5%.

Curious if you've compared quicken vs mint. I'm currently using quicken, but the budgeting tool is driving me nuts.

My planning horizon when I started retirement not quite yet 2 years ago was 38 years, and 4% works out just fine according to FIREcalc, with my expected SS benefits included in the income calculation, which does make my spending less than 4%, in essence.

I first used Quicken back in the late 80's and early 90's but manually entering data was too tedious and I haven't looked at it since. What I like about the Mint tool is it's free, it did and does what I needed it to do, works in a way that makes sense to me, consolidates transactions from most all my accounts and I've customized some categories along the way, which then auto-update when the transactions come in and it all works for my budgeting. Because it's cloud-based I didn't have to worry about software updates.