Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Share Your Magical Spreadsheet?
Old 06-05-2019, 09:43 AM   #1
Recycles dryer sheets
 
Join Date: Aug 2018
Location: Burlington
Posts: 97
Share Your Magical Spreadsheet?

Hi--

It seems several people on here created their own spreadsheets to help them decide they were ready to retire.

Would any of you be willing to share your spreadsheets? (I mean the empty tool--not one containing all your own data, of course!)

I've used FireCalc and done some of my own calculations, but I'd love to see how we stand using some of other peoples' decision tools.

Thanks!
__________________

SoReadyToRetire is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 06-05-2019, 11:16 AM   #2
Thinks s/he gets paid by the post
zinger1457's Avatar
 
Join Date: Jul 2007
Posts: 2,185
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.
__________________

zinger1457 is offline   Reply With Quote
Old 06-05-2019, 11:28 AM   #3
Recycles dryer sheets
 
Join Date: Mar 2017
Posts: 490
Nothing magical about mine. I just kept track of all my expenses-every penny-on a spread sheet for six months to determine if I could even think about retiring. I had no idea how much I spent a month until then, although I did and still do live frugally.
Had simple headings covering each type of expense-Utilities, Home, Health, Car, Groceries, etc etc. Then I had columns for income that I expected if I was already retired, total expenses, and whether I was in positive or negative territory at the end of the month. You can customize your spread sheet anyway that reflects how you spend your money.
SheitlQueen is offline   Reply With Quote
Old 06-05-2019, 11:28 AM   #4
Thinks s/he gets paid by the post
timo2's Avatar
 
Join Date: Jul 2011
Location: Bernalillo
Posts: 2,046
My spreadsheet merely consisted of rows of income streams on the vertical (Y) and amount of money remaining in each stream per year from present to age 90 on the horizontal (X). By changing the yearly gross income per year out to the end, I could determine when various streams ran out and which ones to not tap until later. Since I was shooting for $100K gross per year, the spreadsheet told me when I could retire and maintain that gross (in today's dollars). That spreadsheet was also the source of the data I fed into 'firecalc'. Tools I used included various calculators from Bankrate.com , including the 'compound interest calculator' and 'Savings withdrawal' calculator. The calculators provided by the various keepers of our money worked OK also.

I then jazzed it up with colorful charts for showing to DW. (that backfired a little because DW saw the data, and within 2 years she had early retired, causing me to work longer which was OK with her
__________________
"We live the lives we lead because of the thoughts we think" Michael O’Neill

"We can cannot compel others to do our will" Norman Goldman
timo2 is offline   Reply With Quote
Old 06-05-2019, 11:39 AM   #5
Recycles dryer sheets
 
Join Date: Aug 2018
Posts: 230
This is not my spreadsheet, but you you might find this interesting from the Early Retirement Now blog:
https://earlyretirementnow.com/2018/...eries-part-28/
Lewis Clark is offline   Reply With Quote
Old 06-05-2019, 11:44 AM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
easysurfer's Avatar
 
Join Date: Jun 2008
Posts: 9,868
The best retirement planning spreadsheet I created was a time until spreadsheet.

While I was still w*rking, I think the spreadsheet calculated the years, days, months and hours until the day I chose to say bye bye .
__________________
Have you ever seen a headstone with these words
"If only I had spent more time at work" ... from "Busy Man" sung by Billy Ray Cyrus
easysurfer is online now   Reply With Quote
Old 06-05-2019, 11:52 AM   #7
Thinks s/he gets paid by the post
 
Join Date: Mar 2004
Posts: 2,917
Quote:
Originally Posted by easysurfer View Post
The best retirement planning spreadsheet I created was a time until spreadsheet.

While I was still w*rking, I think the spreadsheet calculated the years, days, months and hours until the day I chose to say bye bye .

Similarly...I kept a countdown calendar on my page-a-day paper desk calendar (remember those?). Started at 1000 workdays....and smiled every day I turned a page and watched the number drop.

omni
omni550 is online now   Reply With Quote
Old 06-05-2019, 12:04 PM   #8
Thinks s/he gets paid by the post
 
Join Date: Jun 2003
Location: Historic Florida
Posts: 3,703
Quote:
Originally Posted by SoReadyToRetire View Post
Would any of you be willing to share your spreadsheets? (I mean the empty tool--not one containing all your own data, of course!)
I think you have your answer...…. so far at least No.
__________________
"Arguing with an Engineer is like rolling in the mud with a pig. Just remember that the pig likes it."
ShokWaveRider is offline   Reply With Quote
Old 06-05-2019, 12:37 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
easysurfer's Avatar
 
Join Date: Jun 2008
Posts: 9,868
Quote:
Originally Posted by omni550 View Post
Similarly...I kept a countdown calendar on my page-a-day paper desk calendar (remember those?). Started at 1000 workdays....and smiled every day I turned a page and watched the number drop.

omni

During the very rough days, I'd refer to my countdown spreadsheet as a light at the end of the tunnel .
__________________
Have you ever seen a headstone with these words
"If only I had spent more time at work" ... from "Busy Man" sung by Billy Ray Cyrus
easysurfer is online now   Reply With Quote
Old 06-05-2019, 01:12 PM   #10
Thinks s/he gets paid by the post
Cobra9777's Avatar
 
Join Date: Jul 2012
Location: Texas
Posts: 1,982
Quote:
Originally Posted by zinger1457 View Post
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.
+1

When deciding when to retire, I was really just shooting for 25X first-year expenses, not covered by DW's and my pension. That was our "number" and we ended up exceeding it by a comfortable margin. This intentionally ignored SS, which I had never counted during the accumulation phase. This was a very simple spreadsheet exercise, although you obviously need to have a good grasp of your expenses and portfolio value.

When I was within a year of pulling the trigger, the spreadsheet started getting considerably more detailed. On the expense side, it tracks many years of actuals by 19 categories and projects all this to age 100, using a variety of assumptions and cost drivers for each category, including individual inflation rates. On the income side, it tracks and projects (by year): our 2 pensions (one COLA, one not), small amount of rental income, his-and-her SS (under various claiming strategies and with some "haircut" scenarios), and then the remaining amount to be withdrawn from investments, starting with taxable dividends, then sale of taxable shares, and ultimately RMDs, HSA, and Roth.

The spreadsheet also calculates federal taxes by year, Roth conversions, and some other what-if scenarios like a possible inheritance, downsizing the main home, sale of the rental property, and possible LTC. There's also a sheet that tracks the portfolio by holding, including AA, performance by year, yield, ER, and total net worth.

In the end, I think having a solid grasp of the concepts and assumptions is more important than the spreadsheet. The spreadsheet is just the mechanism for documenting your assumptions and helping you to understand the concepts and interrelationships. So I think this needs to be a DIY affair. More importantly, all of this data should be run through historical and Monte Carlo tools like FIRECalc and Fidelity RIP for a more real-world stress-test of your data. Home-built spreadsheets are fine for tracking data and documenting assumptions, but real-world markets can do crazy things at crazy times, so you want to get a sense of success rates and worst case scenarios, etc.
__________________
Retired at 52 in July 2013. On to better things...
AA: 55% stock, 15% real estate, 27% bonds, 3% cash
WR: 2.7% SI: 2 pensions, some rental income, SS later
Cobra9777 is offline   Reply With Quote
Old 06-05-2019, 01:24 PM   #11
Full time employment: Posting here.
 
Join Date: Nov 2014
Location: Austin
Posts: 772
Mine definitely isn't written for reuse in mind, but I actually do a few things.
1. What are my expenses?
2. What is the NPV of future SS payments if I retired today? With and without a 25% haircut?
3. What is the size of my overall portfolio?
4. Add the amounts of 2 and 3 together. Then I use my own form of a PMT based calculation (similar to VPW from bogleheads but doesn't assume fixed stock and bond returns).
5. First check: Is the amount from the calculation in step #4 greater than my current expenses? If no, then stop here. Not ready to retire. If yes, proceed to step 6.
6. Second check that is more involved: Does a backtest that includes a retirement starting in the late 60's have withdrawals (including SS), at their lowest point, that are still greater than my expenses? If no, then I'm not ready. If yes, then financially, I'm ready, but no guarantee I'm mentally prepared.
big-papa is offline   Reply With Quote
Old 06-05-2019, 02:34 PM   #12
Recycles dryer sheets
 
Join Date: Aug 2015
Posts: 85
The big thing is figuring out a good/accurate average annual expenses amount because that is the crux of determining when you have reached F.I. Include estimate for healthcare costs. I've used Mint (dot) com since 2011 and that has been instrumental in helping me understand my actual spending to come up with a fairly accurate, sustainable spending amount.

Once you have that number, divide by the yearly withdrawal rate you intend and that will give you the total nest egg amount you need to sustain that amount of spending each year for about 30 yrs.

Adjust upwards if you want to ensure you have more cushion in case of unexpected expenses.

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.
IrishMoss is offline   Reply With Quote
Old 06-05-2019, 02:55 PM   #13
Recycles dryer sheets
dirtbiker's Avatar
 
Join Date: Apr 2019
Posts: 368
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:



dirtbiker is offline   Reply With Quote
Old 06-05-2019, 03:21 PM   #14
Thinks s/he gets paid by the post
 
Join Date: Aug 2013
Location: North
Posts: 2,360
Oi.

A few tabs,
1. Budget/BalanceSheet
2. Daily Balance and benchmarks
3. FireCalc (my own version)
4. Progress showing some graphs countdowns etc.
5, 6, 7 etc. SSA, Pension Calcs, Mortgage Amortization, Earnings history

I check my models against FireCalc success rate to see how much I vary, Either way, i know what I need to do to make it to my goal, it 'aint gonna be easy, will involve some sacrifices and hopefully a little luck.

I haven't added many new "features" or "bug fixes" lately so this tells me its fairly future proof and stable.

I like running what-ifs on things like 1. Return% 2. Early debt payoff 3. SWR% 4. Roth Conversions

My advice...start small, with a budget...mine includes are FICA and all the calculations from our W2 paychecks etc...
__________________
AA (Stock/Bond/Cash ): 96.5/0/3.5% MIX (Small/Mid/Large): 25/25/50% BLEND(US/Foreign): 100/0%, REIT (Real Estate Equity): ~50% of Assets

FIRE in 2031 @ 50yrs old (+/- 2yrs) w/ a hypothetical $2.5mil portfolio, 3 appreciated homes worth $1.0mil and rental income to fund my gap years until RMD. Assets will go to an inherited IRA where I plan on watching the investments grow until I die or the trust gets executed.
kgtest is offline   Reply With Quote
Old 06-05-2019, 05:42 PM   #15
Thinks s/he gets paid by the post
timo2's Avatar
 
Join Date: Jul 2011
Location: Bernalillo
Posts: 2,046
Quote:
Originally Posted by kgtest View Post
Oi.

A few tabs,
1. Budget/BalanceSheet
2. Daily Balance and benchmarks
3. FireCalc (my own version)
4. Progress showing some graphs countdowns etc.
5, 6, 7 etc. SSA, Pension Calcs, Mortgage Amortization, Earnings history

..
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)].
__________________
"We live the lives we lead because of the thoughts we think" Michael O’Neill

"We can cannot compel others to do our will" Norman Goldman
timo2 is offline   Reply With Quote
Old 06-05-2019, 05:45 PM   #16
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Feb 2013
Posts: 6,541
My main master spreadsheet is really customized just for us. I have fields like inflation and real interest rates set as parameters so we can model different futures scenarios. I have it set by year with columns for pension income, SS income, other income, taxes, RMDs, home value, home equity, net worth, inflation adjusted net worth, property taxes, health care costs, other expenses, mortgage payments, etc.

The mortgage payments are fixed, property taxes usually only go up 2% due to Prop 13, other expenses I have increase with inflation, one pension is inflation adjusted but with a maximum cap, so there is a lot of customization just for our situation. Also we follow a matching strategy for investments, so we know most years we will more or less be making inflation + X%.

I use the Fidelity retirement planner for a reasonableness check on my numbers. Their results come pretty close, though my spreadsheet is even more conservative. There's not good odds of us getting tech jobs at age 80, so we'd rather plan to have extra left at "end of plan" time than not enough.
__________________
Even clouds seem bright and breezy, 'Cause the livin' is free and easy, See the rat race in a new way, Like you're wakin' up to a new day (Dr. Tarr and Professor Fether lyrics, Alan Parsons Project, based on an EA Poe story)
daylatedollarshort is offline   Reply With Quote
Old 06-05-2019, 06:01 PM   #17
Full time employment: Posting here.
 
Join Date: Dec 2015
Posts: 609
Same here - I developed ours over the course of a couple of years, and didn't do it with re-use/sharing in mind..

Key things I'd recommend if you're going to build your own would include:

- A sheet (tab) for projected income and expenses, with a column for every year until you're 90 (or 100, or whatever you're comfortable assuming in terms of longevity). This is because you need to know where the funds are going to come from to live off of - whether it's passive income streams or portfolio withdrawals. My income and expenses tab has things like:
  • Salary, Bonus, RSU vesting (3 separate rows for each of us), interest, dividends, tax exempt dividends, taxable IRA/401K withdrawals, social security (one row for each), RMDs (ditto), ST & LT Cap Gain distributions, ST & LT gains from taxable investment sales, etc.
  • Expenses across major categories - "core" (housing, food, insurance, etc), HealthCare (premiums, deductibles & OOP - a row for each), taxes, vacation and "lumpy spending" - things like a new roof or other unexpected, major purchases.
  • Net income (obviously, income less expenses)
- A sheet (tab) for money flow across all assets & liabilities - also by year, across your expected remaining lifetime. Mine includes very conservative return estimates, broken out by category - tax-deferred me, tax-deferred DW, taxable, cash, portfolio impact of RMDs, company stock sales, etc.

- The two tabs feed each other..for example, if I need X to cover expenses beyond expected income flow in a given year, that has to come from somewhere on the assets tab..similarly, if I have something left over (like this year, as DW is still working), it needs to go somewhere - into one or more of the accounts on the assets tab.

As others have suggested, the KEY thing is to have a very good handle on your expected expenses. We do, as I've tracked in Quicken (and other tools before that) forever. There will always be unexpected things - hence my "lumpy spend" row on the income/expense sheet. But, if you can get reasonable projections of core, healthcare, taxes and travel, you'll know what you need to cover. And then, either the income covers it - or you determine and map (column by column across years) what you're going to pull from on the assets & liabilities tab.

My sheet has a ton of other things including interest and dividend projections, stock sales, taxes, etc - and I wrote a pile of VBA code to automate much of it (eg: federal tax calculation based off income, LT Cap Gains/Qual Dividends) and more..

Hope that helps..as long as you get the key things of income/expense and assets BY YEAR (and don't forget to include inflation assumptions that increase your expenses each year), I'd say you're well on your way..
24601NoMore is offline   Reply With Quote
Old 06-05-2019, 06:11 PM   #18
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 23,450
My spreadsheet is more focused on early retirement tax planning so I only go from 63 to 79... but realistically after 70 there probably are not any arrows left in the quiver. My model does include a condensed federal tax calculation. The tax brackets start with announced 2019 and then inflate each year. All amounts are nominal and include an assumption for inflation.

My rows are Year/Age. Here are my columns:

Year
Age
Int
Div
Pension
Spending
tIRA withdrawals
Std Dedn
SS – DW
SS – Self
TI Subtotal
Roth conv
RMD factor
RMD
TI
Ordinary Income
Pref income
Effective federal tax rate
10% bracket
12% bracket
22% bracket
Ordinary tax
Div/CG Tax
Federal tax
Taxable acct balance
Tax basis
tIRA balance
Roth balances
Total
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.

Retired Jan 2012 at age 56...60/35/5 AA
pb4uski is online now   Reply With Quote
Share Your Magical Spreadsheet?
Old 06-05-2019, 06:28 PM   #19
Thinks s/he gets paid by the post
steelyman's Avatar
 
Join Date: Feb 2011
Location: NC Triangle
Posts: 4,337
Share Your Magical Spreadsheet?

My magical spreadsheet while still working was pretty basic (naive, really). It was all about calculating when I’d reach the “magic number”. I’d read many articles that said to plan to replace 70-80% of your working income, adjusted annually for inflation, to maintain your current lifestyle in retirement. I decided to use 85%, so that was my target income.

The HR people provided accurate numbers for expected pension so that provided a base. The calculation involved how/where the difference between the target and base would come from. That was where retirement investments came in along with other (e.g., self-employment) sources.

The “magic number” was calculated using assumptions about growth rates and at least gave me a concrete number to shoot for. Having that number really helped understand that ER could (and did) happen.

That magic number spreadsheet is long abandoned and I have a much more extensive one post-retirement that’s grown more complex over time. It’s still income-focused because I use Quicken for expenses, budgeting and so on.
__________________

steelyman is offline   Reply With Quote
Old 06-05-2019, 06:42 PM   #20
Moderator
Jerry1's Avatar
 
Join Date: Nov 2014
Posts: 3,031
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.
__________________

__________________
Every day when I open my eyes now it feels like a Saturday - David Gray
Jerry1 is offline   Reply With Quote
Reply

Tags
spreadsheet


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tracking basis with spreadsheet, how to structure the spreadsheet? bamsphd FIRE and Money 13 08-03-2009 10:31 AM
Does your spouse share your financial goals? JustCurious FIRE and Money 41 02-17-2007 07:20 PM
Magical '40 binders' now on-line intercst FIRE and Money 5 02-21-2005 10:19 AM

» Quick Links

 
All times are GMT -6. The time now is 11:51 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
×