FREE NEWSLETTER
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.