Share Your Magical Spreadsheet?

[stevemac]

Quote:

Originally Posted by IrishMoss Basic Examples: $40K/year expenses / 4% withdrawal rate = $1,000,000 total needed to cover $40K per year expenses, assuming a withdrawal rate of 4%/per year in year 1 and adjusted for inflation in later years. This also assumes you will maintain an equity allocation of at least 40% over those years. $60K/year expenses / 3% withdrawal rate = $2,000,000 total nest egg needed to cover $60K/year expenses and withdrawing 3% from the nest egg per year, starting year 1 and then adjusting for inflation in subsequent years.

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?

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.

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!

Thanks,

Stephen

[SnowBound]

Quote:

Originally Posted by zinger1457 My spreadsheet has been customized to meet my needs and no effort was made to make it easily understood by others. Probably would need pages of notes for anyone else to make any sense of it.

Same here. What ever spreadsheet you use make sure it has inflation adjustments & SS cost of living adjustments (should be about 1% less than inflation).

HOWEVER, then I figured the best ever and easiest way to figure when you have enough: When your investment income (plus SS if >65) consistently matches your current income you can retire. Wish I figured that out years ago; I'm SO done with spreadsheets.

[SumDay]

Quicken has far more capabilities than I could ever dream of creating in Excel. Tracking your expenses gets you on first base,, and Quicken has helped me do this for over a. decade.

[target2019]

In 2014 I put together what I called drawdown spreadsheet. It included all the usual inputs and outputs. It also had an option to switch between SSA options at the time. I concluded that I had little room for fun. In 2015 I went FT, and will probably build drawdown 2 dot 0 at end of 2019.

To build the S/S I searched internet and had an adviser report to guide me. I think i-orp or flexible retirement planner would give you something to start with.

[Ready-4-ER-at-14]

Ours started simple and has gotten ridiculously complex over the years.
tabs include both retirement and life things

stocks options (place for daily update of all individual stock,option, mutual funds holdings)
quick and dirty (place for recording all daily changes in values and predicting 3 month trends)
yearly dance classes credits used and available
net worth
net worth by ira catagories
roth conversion plans
little book (specific type of investing)
ira contributions by year
ira values per net worth values
estimated taxes 2019
goals/to do list
ira withdrawal
preferred stock
bonds/cd/zeros
real estate
annuity income
annual costs
social security income
retirement income
dashboard
annuity2
muchello (lichello volatility engine using mutual funds and cash)
lichello (rebalancing between stock and cash originally designed for mutual funds)
bond values estimated
Goals
DJU (very old, links mostly broken but retained just to not bother other tab areas)
financials (formal accounting never completed)
options
1-2-3 (red light green light as to should I be in or out of stocks use interest rate changes, SP 500 pe ratio (17 key value),market trend up or down
financial statement
rule of 72
capital accumulation
cost per 100
income
28 laws (from a self help book physical environment, well being, money, relationships, home/comfort, car/vehicle/safety, energy/vitality, opportunity/space/time
retire income (old with mostly broken links)
30 yr treasury yields (graph annually from 1977)
wedding budget
annuity
r-multiples
bond summary chart
probability analysis (probability of one spouse alive or alive alone at certain ages
pr2014 Mortality tables
retirement state rankings
financial planning

Part of the problem is you start drawing data from one area to another and then modify one area and it affects others. Each of us likely gets better at spreadsheets over the years and has old codes left here and there or broken links.

I embed a lot of financial information near crucial calculations.

One function that have been EXTREMELY useful is VLOOKUP()
I have monthly dates in left column (2019_06_01 as example) and can find that value by the date today minus the days of the month today.

[racy]

Quote:

Originally Posted by SoReadyToRetire ... 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]

Quote:

Originally Posted by stevemac "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]

Quote:

Originally Posted by Jerry1 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]

Quote:

Originally Posted by 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.

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]

Quote:

Originally Posted by stevemac 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.

Quote:

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.

Quote:

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]

Quote:

Originally Posted by dirtbiker 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]

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

The paid version offers more bells and whistles.

[dirtbiker]

Quote:

Originally Posted by FI_RElater 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]

Quote:

Originally Posted by KCinBAMA 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.