THE SUMMER AFTER MY sophomore year at Virginia Tech, I had an internship with Frito-Lay, working in its computer applications department at the company’s research headquarters in Irving, Texas. One of the programs I had to learn was VisiCalc, the first spreadsheet program for personal computers. This was my introduction to spreadsheets, and I’ve been hooked ever since.
Sometimes, I joke with my family that I live the data-driven life—not to be confused with Rick Warren’s purpose-driven life. I have Excel spreadsheet workbooks for just about everything.
Here’s just some of my workbook titles: books, budget, car maintenance, children, Christmas cards, fitness and nutrition, giving, home projects, letters to the editor, meal ideas, ministry management, people, retirement, vacations and Wordscapes. My most recent workbook is entitled HumbleDollar.
In my career, I’ve used spreadsheets to help manage all of my major work projects. Some of the workbooks associated with larger projects contain 30 or more spreadsheets, with each sheet dedicated to a different aspect of the project. But I’m guessing that HumbleDollar readers wouldn’t be terribly interested in a detailed technical explanation of how I manage nuclear power plant projects using these workbooks.
At home, my most sophisticated workbook is called finances. It’s evolved over the past quarter-century, with each annual edition incorporating incremental improvements. It contains 20 separate spreadsheets, each with a different purpose. The most important spreadsheet is entitled financial summary. It lists all our assets by account and fund, totaling them up to provide a net worth. Home equity is included in the net worth calculation, but there’s also a sub-calculation that includes only financial accounts. The value of my pension isn’t included in either the net worth or financial account calculations, but it is included in a total asset calculation.
A spreadsheet called portfolio analysis pulls data from the financial summary workbook. In that spreadsheet, financial assets are binned across various categories, such as large-cap U.S. stocks, international stocks, Series I savings bonds and money market funds. A percentage is calculated for each category. This allows me to see a detailed breakdown of the overall allocation of our entire portfolio.
Other data calculated on the portfolio analysis spreadsheet include such things as year-to-date portfolio percentage change, stock and bond allocation for the entire portfolio, stock and bond allocation for retirement accounts only, and year-to-date percentage change for our combined Roth IRAs.
I use the chart feature as well. One of the spreadsheets is labelled total financial resources. Using data going back to 2002, it includes a chart that displays the growth over time of our Roth IRAs, our miscellaneous fund portfolio, my 401(k) and our total portfolio.
I also use the chart feature in a spreadsheet called 401(k) growth. You can see what it looks like in the accompanying chart. In this spreadsheet, I track a performance indicator I call “percent contributions.” It’s a simple calculation of what percentage of my total 401(k) balance can be attributed solely to contributions I’ve made. Lower is better.
In 1992, the first year for which I have data, contributions stood at 67.3% of the balance. As of year-end 2022, it was down to 31.9%. It’s been as low as 26.5%. Had I made significant 401(k) contributions early in my career, the percentage would be even lower.
A number of years ago, it dawned on me that my spreadsheet data are among my most valuable possessions. Losing this information would be catastrophic to me, so once or twice a year I back up all of my workbooks to a thumb drive, which I then place in our safe deposit box. I’d rather not discover what the data-deprived life is like.
I too rely heavily on spreadsheets (and Quicken) and would hate to lose any data. That said, a thumb drive backup twice a year seems risky. I recommend an automated cloud based backup service (I use iDrive but there are several). For <$100/year per year I get piece of mind that my family’s data is always backed up including precious photos, documents, and my all important spreadsheets. (No I don’t work for a backup company). Being a belt and suspenders kind of guy, I also take a backup disk to my safe deposit box every quarter. An added benefit of these services is the ability to restore an older version of a worksheet which I’ve had to do on occasion.
Just heard another free planning app from Bogleheads podcast
at this URL. Free covers –
You can pay for more features.
Just curious, would you be willing to share your MS Excel workbook/spreadsheet of course without the existing data? I have created something, but your spreadsheet sounds much more robust than I can create.
FYI – There is a planning tool on Fidelity site (Planning & Advice at the top – Get Started green button in the middle of page) that I thought is free to everyone not just Fid customers. It is a google/excel sheet on steroid with lots of what-if capabilities. Compared to the assumptions I make on my own sheet, such as investment return, inflation, etc., it draws from other available data and mixes with simulations. A good tool to use for cross-checking. https://www.fidelity.com/calculators-tools/retirement-calculator/overview#:~:text=Fidelity's%20retirement%20calculators%20can%20help,assess%20your%20financial%20health%20%7C%20Fidelity
So Cool! Thank YOU.
Yes, it’s nice to do things with sheets for ourselves and use them, modify and improve over time. Long ago I made one to calc my pension from the last job I had.
I knew the sheet had a value to others so I put it on a shop computer and told one person about it. In about 2 months, I checked and it was all jacked up because, well people will try things and then save the changes. I learned once again, when publishing something for the use of others, it has to be protected. Version 2 with locked cells was produced. Also, the pension changed over the years so there were two plans, which demanded two sheets for calculations using the different plans. Hmmmmm…wait…. there has to be a way to have one sheet, and with the input of the user to select which plan, would give figures for either plan…. WOW.. I just thought of that while typing… 😉
Anyway, it was ever so very satisfying to have my first pension check and the gross had a difference by less than 3$ from the calculation.
Good enough for me.
I still remember the first client who told me he wrote letters in Lots 1-2-3, which sold a huge number of IBM PCs, by the way.
While I still use my spreadsheets, I also like the online portfolio performance tools like Schwab’s, which can give me portfolio performance results while factoring out deposits and withdrawals.
I like the way various responders have found tracking solutions that work for them and the way data driven engineers have remained data driven even when engineering is no longer an income-driven 9-5.
I’m married to one who stopped her 9-5 when we had our second set of twins, but she still lives the quantified life more than 30 years later!
I have always been a wannabe doppleganger. Maybe you can teach us advanced Excel skills?
Also, why do you use Excel over a database program? I always thought of a spreadsheet as something to use for quantifiable things. For instance, “letters to the editors” is not something mathematically quantifiable. Can you link Excel files to each other, as you can with a database?
Although I’m a reasonably proficient user, I’m not an expert on Excel. There are lots of features available that I don’t make use of. I don’t have a database program on my computer. The Letters to the Editor spreadsheet was a quick, simple way to organize information (title, publication date, responses) on letters I wrote to the Lancaster newspapers, back in the old days when many people actually read newsprint editions and could discuss issues civilly.
Loved the article!, a fellow Engineer with Finance background. Started my career with Lotus 1-2-3, quickly switching to Excel. I too manage my entire life on spreadsheets, which I strive hard to keep simple while adding more complexity, an ongoing part of my retirement journey!.
Thanks for reading and commenting, Raj. Your statement about keeping the sheets simple while adding more complexity may seem paradoxical to some non-spreadsheet users, but it makes a lot of sense to me!
Thanks Ken for the article. Though not an engineer, I too am a fan of spreadsheets and use them extensively for nearly every aspect of my life. Maybe one of the more unusual was to track my RA symptoms and activity levels about 10 years ago when in 2012 I embarked on an unconventional treatment plan. I wanted to quantify my progress, if any. At my quarterly visits with my rheumatologist I would review with him charts showing my daily pain level and activity level. He was skeptical about the efficacy of the new treatment plan that I had convinced him to prescribe for me. Over time my activity levels increased and my pain levels diminished. The charts not only helped me in my communications with my doctor but also gave me evidence of progress which was encouraging. In 2013 my labs showed improvement as well. In 2014 my rheumatologist declared I was in remission and I have remained so. My point is: Excel is a tool that can be helpful in a wide variety of applications as Ken and others have suggested.
Jerry, this is an excellent example of the non-financial uses of spreadsheets. I have a number of sheets tracking various aspects of exercise routines and health-related considerations. Tracking my gym activities increases my motivation and build in self-accountability. The data I have accumulated over years in these areas are just as valuable as the financial stuff. Thanks for reading and commenting.
I am a computer novice so I rely on the portfolio X-ray feature on my my Morningstar premium membership ($250 per annum). It provides me with a multitude of financial data about my portfolio. As a bonus I get to read articles on a weekly basis from a stable of financial experts.
I calculate my next worth on a quarterly basis as we are relying solely on our portfolio for paying bills until I am 70. This is possible due to my children’s ability to design a spreadsheet for me.
Thanks for sharing this, Ken. A fellow engineer here, so I may be a bit biased here: I think spreadsheet is one of the coolest tools we have, both at work and at home. It helps us be more organized, which in term save us time and be more efficient.
Looks like my article is bringing out the HD engineer contingent, perhaps to Dick Quinn’s chagrin. Glad you enjoyed the article, thanks for commenting.
Ken, I too love a good spreadsheet and have many years of financial data captured in these treasured documents.
I like your back up idea and will be implementing this by year end! Thank you for sharing.
Thanks, Stanley. It’s good to hear you found a practical take-away from the article.
Fun article; I enjoyed it a lot; thanks! Yes, I love spreadsheets too. In fact I have one I keep in Google sheets in the cloud. That allowed me to share it with my son. I shared it with him and gave him update permission so he and I (on occasion) can both be in it concurrently updating. In our case that was useful as we were also on the phone chatting and doing some “what if’s” in real time in that same spreadsheet. Of course, we both had to have google accounts but that wasn’t a problem as we both use gmail. Yeah, how nerdy is that; father and son spending some quality time playing with numbers in a spreadsheet together 🙂
LUV this! Pass on the wisdom and see your prodigy benefit. 😉
OITG you’ve taken it to another level, doing collaborations with your son. I like it.
Geez, I think I’m organized and then I read this. Kudos to you, I’m envious. I have a few spreadsheets but I haven’t looked at them for a few years.
Ha ha, thanks Mike!
Ken, I enjoyed this article quite a bit. As a fellow engineer I have been using spreadsheets for decades. I started writing custom code in Basic and Fortran and moved up ti a number of spreadsheet programs.
I for one (and probably the only one) would love to see how you manage nuclear projects.
Thanks for the support, fellow engineer. I’m not expecting this article to break any records for ‘likes’ as some people won’t “get it”.
With regard to spreadsheets, I once had an engineer turn over a highly complex project to me (Neutron Monitoring System replacement) using a very detailed Excel workbook. It was the best turnover I ever had…good thing because it was quite a challenging project. More fun was being able to create my own workbook at the start of a project.
Power Range Neutron Monitoring System upgrade via GE NUMAC here. Great memories.
Yup, did that one. Also Wide Range Neutron Monitoring with NUMAC. 4 years of my life.
I take it you and your dopplegangers enjoy this? I have managed to reach a comfortable retirement without a single spreadsheet. I made my SS decision on the basis of the biggest base payout to counteract a pension with no COLA. If I want to investigate my portfolio I find Vanguard’s tools adequate, and all my investments are there. If I want to know where the money is going, I look at my Quicken reports. Every few years I have a fee-only financial planner run some simulations for me. Tell me again why I need a bunch of spreadsheets?
CPA here. I too have a bunch of spreadsheets and, most recently, replaced Quicken with a spreadsheet that better meets my needs with pivot tables for reporting net worth and spending.
My long term spreadsheet allows me to look behind the curtain – I know what assumptions are being made because I made them. I’ve never been comfortable not knowing the assumptions Vanguard, Schwab or Fidelity use in their projections. I can easily change spending, return and inflation percentages to see how long my money will last. My model can reflect decreased spending as I age until it increases drastically when I may need long term care. Excel is magical.
I absolutely do enjoy this. Believe it or not, managing the spreadsheets does not really take up much of my time. Do you enjoy maintaining your travel blog? I hope you do, but that’s not for me. And if spreadsheets aren’t for you, that’s fine and I would never tell you otherwise.
I figured you enjoyed it. But I am wondering what exactly I would gain from using spreadsheets instead of what I’m doing. (And I’m not a techno-Luddite, I used to be a techie.)
Kathy, you don’t need them. But us doppelgängers enjoy it. Some folks play video games and watch lots of reality TV. I’d rather work on a spreadsheet while watching an Eagles or Phillies games. I understand its not for everyone, and you can live a very happy life without a single spreadsheet. But for a few of us, it helps us understand the world a little better.
I can’t resist a gentle retort: Am I hearing somebody who tracks her finances in Quicken disparaging those who track their finances using spreadsheets?
That’s one report a year, with Quicken pulling in the data, although I do do a quick check at the end of the month. Rather different to 20+ spreadsheets.
Hi Ken,
Wow! Who knew I had a doppelgänger who writes for Humble Dollar?!? I read your article out loud to my wife this morning, and she was cracking up! As I read your list of spreadsheets, my wife was saying “check, check, check”.
I will have to admit to having a bit of an edge on you though. My “financial” SS has 23 tabs, including several related to Social Security with various claiming time frames and related longevity pay-back points. I’ll gladly join your Excel Addicts Group once formulated!
Take care,
Roger
Roger, your comment made my day. I think I am going to have to figure out another 3 spreadsheets to add to my Finances workbook to catch up to you. Well, maybe I should talk about that in the next support group meeting. See you there!
I am sure that there is a lot of your wisdom and rationale for all that you do wrapped up in your spreadsheets. I use Quicken to do many of the tasks that you mention. Finances, including taxes, RMDs, estate planning and the rest are complicated. You mention the potential loss of your data, and your backup methods so that you don’t lose it. What you didn’t mention is how you have set things up so that if something happens to you a successor manager could easily step into your shoes and understand your thoughts and plans and how your spreadsheets interact.
My Quicken files tells what has happened, and the current disposition of our assets. It does not include the how and why and what my plans are to handle the myriad decisions that must be made each year. Because my spouse has no interest in finances, it will be my children who will have to step in. To handle this I have begun to write lengthy memos to describe what I am doing and why. I keep these in Dropbox which I have shared with my children who will have to deal with my world. These can include such things as why we have different Part D drug plans, what is happening with our car insurance, how I plan to fund income taxes, when property taxes are due, and many other things.
My busy children, who either don’t need to consider the issues we face, or who hire someone, for example, to do their taxes would find the finances of the retired to be far different from their own. Even with my memos, I am sure they will wonder why I did some particular thing. Having handled affairs for my own parents and my in-laws, I know it will be difficult. Few accounts, good records, and extensive notes should help.
These are some great thoughts. While I have done some successor planning, including an ‘after I die’ letter for my wife, it would not be a smooth transition. I have some work to do over the next couple of years.
Ah, VisiCalc — now that makes me recall grad school days of a tiny computer lab, a shared and much used Apple II, and 5.5″ floppies! Thanks, Ken, for the memory trip.
Like for you, spreadsheets are integral to my financial planning. Mine have only really been two: the one I started early on for saving to retirement and the other for retirement itself. And I’m struck by how different the two are.
My savings spreadsheet was a single page, with assumptions of average returns, inflation, and my ability to save over time. I aimed for returns 2% above inflation and to retire early with the same (though inflation-adjusted) income in retirement as I then had working. No expectation of future promotions or financial set-backs and with little thought to taxes. I’d revisit the spreadsheet every year or two to update it and to maintain my savings momentum. Simple and easy. It worked.
In contrast, my multi-page retirement workbook maps out various scenarios and what-ifs over ten years. It projects expenses, taxes (using current tax code), and IRMAA, and assumes average returns. The what-ifs are the timing and amounts of IRA and other withdrawals, annuitization (or not), charitable donations, and Social Security. Way more complicated than my first spreadsheet but in retirement I have more time. Though I use it now to guide my withdrawals, the most useful part has been in the learning. I suppose a financial planner could have made projections for me, but that would have taken the fun away! 😉
Jo, it sounds as if your retirement workbook is much more complicated than mine. But I think your last sentence sums the situation up very nicely. We still have room in our Excel Addicts support group if you’re interested.
Several years ago I assembled a complex (for me) spreadsheet to compute my cumulative Social Security income based on multiple starting dates and included estimated COLA increases. While the pure numbers told me one thing, there were a number of subjective factors that I considered, too: my health is good and I vigorously exercise every day, I’m happily married, my wife is younger than me and waiting to take Social Security will benefit her assuming I croak before she does, I am involved in several continuous volunteer and social activities, my Father lived to be 91 and really didn’t take good care of himself, my brother died of alcoholism at 58, my Mother died at 84 due to an accumulation of maladies that in combination proved fatal, my financial picture is comfortable or better.
It should be obvious that my point is that we need to address decision-making both objectively and subjectively. Sometimes, in spite of all the hard data, we need to consider the environment surrounding us and ultimately go with our gut instincts.
Jeff, good thoughts. HumbleDollar is all about factoring the human side into our financial lives. But hopefully respect for the data will keep us from making an unfortunate emotional decision.
I had VisiCalc installed on my $3K Kaypro 64 (that’s 64 K of RAM) that had two floppy disks. You could watch the spreadsheet recalculate on the green screen and for your “large” spreadsheets you could swap out the disks to accommodate the lack of RAM.
Later to be able to use Lotus 1-2-3 I had to upgrade to a Compaq 256 with a 10K hardcard for storage. It was expensive but I knew that 1-2-3 was the ulitimate spreadsheet and the Compaq was top of the line so I would never need to replace them. Security was not a problem because right next to the disk drive was where you could lock the computer with the physical key.
I heard that this internet thing might require some additional RAM. Thanks for making me smile this morning.
Ah, the good old days. And no worries about cyber security either…. Thanks for your comments.
Spoken like a true engineer, Ken. I recall when engineers used to show up in my office with spreadsheets analyzing the best health insurance options. Used to drive me nuts. A few kept detailed calculations to project their pension – it was all online and available to them on our benefits site.
I have to admit I do the same as you with investments, but mine is called Fidelity Investments website, even I can use that, spreadsheets not so much. 🤑
I’ve had the same experience with engineers. My favorite was the guy who was convinced we shorted him a little less than $19 on some stock options.
Spreadsheets are great, and I’m rather advanced in them, but I really enjoy my time in retirement. I don’t need memories of work.
Yeah, those engineers….
Dick, I admit that I created a spreadsheet to compare my health insurance options. It came in quite handy and pointed me to the clearly superior option. And yup, I have kept a very detailed pension spreadsheet for over a decade. I should be able to “retire” that one in January. Even though I am not a Fidelity client, I have been able to use their website as a guest. It is quite magnificent.
Please don’t tell me you have your own spreadsheet to calculate SS.
OK, I’ll keep you guessing on that one.
Nuke Ken – see my comment above. I created my own Social Security spreadsheet.
Such a good article – it is awesome to know that I am not the only one doing this! #excel@life
Kevin, glad you liked the article. Maybe we should start an Excel addicts support group. #excelfunocd