For those with retirement spreadsheets...

C

Cbear1883

Dryer sheet wannabe
Joined
Jul 30, 2007
Messages
24
I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated!
 
Most spreadsheets are comprised of basically simple calculations. I've set one up for myself that models what my savings should be at any age in the future (to within a month) given assumptions about current savings rates. It also estimates my Social Security benefit and taxes and estimates a withdrawal rate based on my age at the time to arrive at an estimated retirement income. All these figures are in current dollars, with the expectation that my investments and benefits at least keep up with inflation. Basically just some addition with a bit of multiplication thrown in.

Try your hand at some simpler spreadsheets to start out. Make an amortization table without using Excel's built-in functions for that -- make it using addition and multiplication.

What I've ended up with is something that someone else would have a learning curve to understand. But it's something I've built and therefore understand what the output means -- unlike the average retirement calculator on the web.

If you can do what I regard as basic tasks, which includes entering formulas and then copying them, then you should be able to make your own. Start out with what you can do, use the Help to see if it's possible to do something else you want to do. Never had any spreadsheet training, this method has worked for me. YMMV.

---------------------------------------------------
​
 
Basic tasks is all you need. Like EMF said build yourself some tables. For me I'm accumulating so I have a spreadsheet that predicts my accumulation based on conservative estimates of return for my portfolio and new $ I plan investing monthly. Over time as I learn and better understand what I'm doing I add new features or information. Most recently I was able to determine our annual basic living expense and can calculate out what that will look like over time with inflation and changes that are predictable. I haven't figured out yet the decumulation phase so that is a blank on my spreadsheet - and the way the market is going it's not a priority right now. My next project is to track my ROI.

DD
 
Agree with the other posters. What I do is quite simple:

List all assets
List all liabilities
Estimate how much we can save each year
Assume a normal rate of return (highly speculative) on existing assets
Project what assets/liabilities will be in 1, 2, 3, or 10 and 15 years

(I don't adjust for inflation--instead assuming a lower rate of return, but that's just me. There are different opinions about it)

You can also make budgets based on your actual spending (credit card statement helps), and estimate what your ER needs will be.

Given the 4% SWR, you should be able to estimate what you need to retire on IN TODAY'S DOLLARS. Check to see what year that number will fall on.

Again this method is very crude and speculative. It works for me. :)
 
My version is probably one of the more simplistic here... since I do the "ghetto budget."

I look at how much we typically spend, how much income we bring in, and then set up the difference to go on auto-deposit into combination of 401k and Vanguard Index funds.

Then I have everything go into a single web-based view, and every so often I look and go, "Yup, the numbers are going up." (well other than the last week LOL)
 
I downloaded a budget worksheet from Fidelity and use it to track monthly expenses. For my AL I created my own spreadsheet with separate worksheets for Mutual funds/ 401k/ cash equivalents I update these monthly and each worksheet feeds an allocation worksheet that summarizes +/- $ per asset - telling me what to sell and buy.

This is very simple and meets my need. I also created a worksheet where I insert a cash amount that I want to re-allocate, like a tax refund, and it tells me where to put the $.

Good luck - worst case if your just not into all this allocating is to get a life-style fund(Vanguard) which keeps your allocation intact. Remember you pay for that in two way's, one for the primary fund mgt, and two for not being able to upgrade your funds to Admiral class significantly reducing your fees.
 
Cbear1883 said:
I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated!
Click to expand...

I think you will get more specific replies if you tell us what you're trying to accomplish with your spreadsheet. What are you trying to track? What are you trying to project?
 
Peaceful_Warrior said:
My version is probably one of the more simplistic here...
Click to expand...

My Excel retirement workbook, on the other hand, has fourteen well populated worksheets and is terrifyingly complex. I have had it going since 2000 and I keep adding things as I decide they interest me. I love Excel and I keep track of (enter or compute and project) all of my income, budget, pension, social security, TSP (=401K), Roth, days until I retire, best retirement dates and why, and so on.

Then there are pages with steps needed to move after retirement, hypothetical projections of nestegg left at various ages given various assumptions, possible asset allocation strategy with lists of mutual funds I am interested in, computations of income from various sizes of TSP (MetLife) annuity if I got one when I retire, and amortization on my past mortgage. Now that my mortgage is paid off, this page has morphed into my daily checking and savings account balances along with monthly and yearly projections and goals, and what I actually live on (income minus savings/investments) in comparison with my budget.

I could go on (and on, and on...). Essentially my Excel workbook is as personal as can be. I update it every morning with account information that I get online and elsewhere. I wouldn't wish it on my worst enemy, but keeping tabs on things this closely has worked well for me. Despite the unexpected vagaries of life, I am meeting or exceeding all of the financial goals that I have been creating for myself through the years. In my spreadsheets I can see that in black and white, and that is a GREAT feeling.
 
Thanks for the replies everyone :)

I think you will get more specific replies if you tell us what you're trying to accomplish with your spreadsheet. What are you trying to track? What are you trying to project?
Click to expand...

Well to be honest I'm not sure...something that I can use to keep track of what I have now, and what I'm going to have as the years drag on. I'd also like to be able to use it as a guide for what I owe for my mortgage, car loans, upcoming large purchases, etc. (I just finished paying off what was left of my younger years that I paid for in plastic, so the cc's are gone!). My goal is to pay everything off by 2013, including the mortgage we took out 3 years ago. We get no tax advantages, as the mortgage is under 70k, and at 6.375, it's not the BEST rate in the world, so I'd rather just pay it off. I also don't have all that much faith in where the market is headed in the next few years. Once our debt is paid off, we'll be able to put some serious cash into our retirement funds.

Something similar to what Want2retirehas described sounds great, yet maybe not "quite" so detailed (only because we're just starting and don't have much detail to imput). I did a search on the forum and found a few that I could probably use for comparison, but you're all so savvy, I thought you would have some good suggestions, and I was right. :) Sometimes I feel like I'm so far from retirement, that I shouldn't even bother yet with detailed planning. I'm still 28 years from our "anticipated" early retirement date but I guess I figure that the more work I put into it, the earlier that date can be.


Thanks all for your ideas, they'll be put to good use :)
 
If you need help figuring out how much you need to pay each month to pay off the mortgage quickly, and you don't know how to do that in Excel, use an on-line calculator, like this one

Paying Down a Mortgage Calculator - Fiscal Agents Financial Tools

or go to this website that has lots of caculators that can do it for you:

Hugh's Mortgage and Financial Calculators

then all you need to do is chart your monthly expenses (how much you pay/save) and you can get started with a very simple spread sheet that only uses + and -
 
Sandy said:
If you need help figuring out how much you need to pay each month to pay off the mortgage quickly, and you don't know how to do that in Excel, use an on-line calculator, like this one

Paying Down a Mortgage Calculator - Fiscal Agents Financial Tools

or go to this website that has lots of caculators that can do it for you:

Hugh's Mortgage and Financial Calculators

then all you need to do is chart your monthly expenses (how much you pay/save) and you can get started with a very simple spread sheet that only uses + and -
Click to expand...

Absolutely! A lot of the stuff that I have on my spreadsheet, such as social security based on my past and projected income, and how much a TSP annuity would pay, and my amortization page are all available as calculators online. I find the method used by the calculators, program the method into Excel and check in comparison with the calculators. I only do this because it is fun for me (yes, I'm weird). This is yet another reason why I wouldn't wish my worksheet on anybody else! It is very customized.

Well, I suppose that another reason why I include these computations into my Excel spreadsheet would be that once I understand the computations I can try out different scenarios that some calculators can't handle. Plus, in the case of amortization it keeps a record...for example, I sent in an extra $13,200 on May 1, 2003, $16,700 on July 1, 2004, $5000 on December 1, 2004, and so on. Fun for reminiscing and for computing how much I lived on other than the house payments.

I always check the output with what the calculators say, every year or two just to make sure I didn't drift away from the basic computations or that the computational factors used by the online calculators haven't been updated (as in the social security multipliers, etc).
 
W2R:
My Excel retirement workbook, on the other hand, has fourteen well populated worksheets and is terrifyingly complex
Click to expand...
Cbear:
Well to be honest I'm not sure...something that I can use to keep track of what I have now
Click to expand...

W2R's description of her spreadsheet sounds about like mine. Rome wasn't built in a day, and neither were our spreadsheets -- at least not mine. I started off simply with a worksheet that kept track of savings goals and what my mortgage balance was doing as I made additional payments.

Then I added worksheets to keep track of my Social Security, retirement projections, per year tax withholdings so I wouldn't get hit with a penalty, etc.
 
Cbear1883 said:
Thanks for the replies everyone :)



Well to be honest I'm not sure...something that I can use to keep track of what I have now, and what I'm going to have as the years drag on. I'd also like to be able to use it as a guide for what I owe for my mortgage, car loans, upcoming large purchases, etc. (I just finished paying off what was left of my younger years that I paid for in plastic, so the cc's are gone!).
Click to expand...


Why not use a money management software package? That will get you started without the complexity of a spreadsheet. Once you know what you want, you could start creating your own spreadsheet. I use Quicken and am very happy with it. I have a friend that uses the Opensource GnuCash and swears by it (its also free).

I use a spreadsheet for things that Quicken doesn't do well.
 
EngineeringMyFinances said:
W2R's description of her spreadsheet sounds about like mine. Rome wasn't built in a day, and neither were our spreadsheets -- at least not mine. I started off simply with a worksheet that kept track of savings goals and what my mortgage balance was doing as I made additional payments.

Then I added worksheets to keep track of my Social Security, retirement projections, per year tax withholdings so I wouldn't get hit with a penalty, etc.
Click to expand...

The same is true for me. My spreadsheet has evolved over 7-8 years. I think I started it when I wanted to calculate how much my federal pension would be. That was the first question I had.

I added sheets gradually over the years, and I modify it as new questions arise. That is pretty much every day. For example, recently I added projections of what my TSP (401K) will be if the market continues to yield what it has this year, only indefinitely, and compare with my prior projections. The market probably won't continue to tank, but it will be cheery if/when those numbers begin to rise. :D
 
Why not use a money management software package? That will get you started without the complexity of a spreadsheet.
Click to expand...
I've tried out both Money and Quicken, and didn't find them to my liking. Maybe I didn't give them enough of a chance.

When I was younger, I tracked all spending in detail. But after awhile of doing that, good spending habits were developed, or at least good enough spending habits that I don't need to track spending in detail. I set general savings targets to make sure I don't stray too far out of line. May not work for most, but works for me.

With respect to web calculators for retirement planning, most of them don't let you know their assumptions. Same thing with financial software. With my own spreadsheet, I know what the assumptions are.

Maybe FIREcalc's a bit different, and through discussion on this forum I could understand better how it works. For now, I'm using a very conservative estimate for withdrawal. Perhaps with a bit more study I'll feel more comfortable with an earlier retirement as I get closer. But since I'm in the accumulation phase, it doesn't matter.
 
Cbear1883 said:
I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated!
Click to expand...

I have one workbook which has close to 20 sheets of various calculations.

page 1 is a list of all accounts, web addresses, login ids and password codes (not the actual passwords, but a code to help me remember it).

page 2 is a list of tax tables (from fairmark) and how to calculate income taxes in USA.

page 3 is an estimate of what it takes to retire based on given investment returns (average) over long term.

Input income
Input current age
Input retirement age

output is age to retire with a given return %. There is a column for 12% returns, 11%, 10%, 9%, 8%, 7% and 6%.

I have this printed and look at it often. When others post "here is what I have saved and this is my age", I look at this chart to see where they are. This chart is the reason why I know I need to be aggressive for retirement (9% returns barely achieves goal right now). 10% achieves goal with room to spare.

I have another "draw down" spread sheet for how the amount saved on previous sheet would get drawn down. An ammortization schedule of draw down.

I have a sheet for the ammortization schedule on our current mortgage.
I have a sheet for the ammortization schedule on our second mortgage.
I have a sheet on CDs, when they mature and their amounts
I have a sheet on compound interest calculations

and I am sure I created more, and just did not remember what they were for (I rarely delete anything).
 
I keep 2 excel spreadsheets. Both fairly simple. The first is a balance sheet style that lists assets, liabilities and ultimately net worth. The second simply lists my investment account's total for each month and overlays that number with the DJIA on the same graph.
Over time I like the way my portfolio is less riveted to the DOW.

I say keep it simple. Make up your own sheet. You'll understand it better.
 
My version of quicken premier 2007 has a great retirement calculator built right into it. And it is really easy to work with because as long as you keep everything up to date in Quicken it simply pulls that data and runs the calculations.
 
I like excel since I can play with it while at work and doesnt cost anything...;)

I mostly have end of year projections based on a rate of return and another column what I actually hit in my various portfolios....I started with my taxable port (since I need to track dividends and costs)...and worked in several worksheets around...including budget (before and after FIRE), taxes, a mortgage schedule, etc.
 
I use a 4 workbook system that meets my needs as a 24 year old with no reason to estimate a precise retirement age or portfolio draw down ability at this time. Each workbook is fairly complex in its own right, but as others have said, it was developed by me so I know exactly what is going on. I think the key is to find something that is simple to update going forward and provides you with useful information for decision making - while I spend countless hours analyzing and tweaking my spreadsheets, the actual "required" time to update each is about 1 hour at the end of each quarter.

1) Net Worth Spreadsheet (my "scoreboard") - this is updated quarterly with the balances of all asset and liability accounts and tracks changes to NW quarterly and yearly

2) Asset Allocation Spreadsheet ("benchmarking") - this gets into the detail of all my holdings, from the checking account, to funds within the 401(K). Within this workbook I have my holdings dissected in pretty much every way imaginable: Taxable vs. Tax-Favored Holdings; Total Asset Allocation across and within accounts; Large vs. Mid vs. Small; US vs Int'l, etc, etc. I also update this spreadsheet quarterly.

3) Cash Flow Statement - Here I track my money coming in and my money going out (to expenses and savings). This allows be to forecast how I will handle different scenarios (e.g. raises, debt-paydown, increases to savings, etc.).

4) Budget - pretty standard here, I try to track all expenses by category by month. I am not a huge fan of tracking every single penny, but the DW likes to and I figure in our early years it will give us a good baseline for estimates going forward.
 
You must log in or register to reply here.

Similar threads

R
Minimum Dignity Floor - Spreadsheet?
2
Replies
41
Views
3K
LateToFIRE
L
D
How to deal with taxable holdings with large gains
Replies
20
Views
1K
Texas Proud
T
D
Guidance with withdrawal plan
Replies
15
Views
729
mathjak107
M
R
Final Approach Planning for Retirement
Replies
15
Views
1K
Route246
R
F
Budget spreadsheets/Apps??
2
Replies
38
Views
3K
Jeffman52
J

Latest posts

Back
Top Bottom