There’s a spreadsheet for that

Taking charge of your finances is a life-long skill well worth learning. From keeping within a budget to managing your investments and loans; understanding a few basic concepts will put you in the driver’s seat. You need a tool to use those concepts. In the world of personal finance, spreadsheets are the tool of choice. Although Microsoft Excel is the industry standard, there’s a free tool available that works in Microsoft Windows, Mac OS X, and Linux: LibreOffice Calc


If you’ve never used a spreadsheet before, don’t be intimidated. Start with something simple and work towards more complicated topics. Take your time. The spreadsheets below were developed by members of the Bogleheads community.

Household budget

Living below your means is essential to free up the money you need for retirement. Create a Household budget spreadsheet.

Tracking your investments
Cascading Asset Allocation

Using a spreadsheet to maintain a portfolio is the heart of what investors would consider “spreadsheet territory.” Topics include:

Variable Percentage Withdrawal
  • Variable percentage withdrawal – A mix of the Constant-Dollar and Constant-Percentage withdrawal methods, this spreadsheet will draw-down your portfolio using an increasing percentage each year.
  • Backtest your portfolio (see below). Both US and Canadian historical securities data are included.
What might have been
Simba’s Backtesting Spreadsheet

Investors spend a lot of time debating portfolio performance.
For example, someone claims their portfolio has the best performance because it did well from 1990 – 2007. The claim is immediately challenged, showing it performed poorly from 2007 – 2010. An all-out battle ensues proving how well or poor the portfolio performed; selecting periods from 1863 – 2013 and everywhere in-between. Each side claims victory.
The process of comparing your portfolio against historical performance is called backtesting. No one can predict the future. However, you can get some measure of confidence in what your portfolio might do had it existed at that time. To say another way, this is what might have been your portfolio’s performance if you could predict the future.

Use Simba’s backtesting spreadsheet to analyze your portfolio’s performance for both good and bad time periods.

Back to the future

Investing enthusiasts who like to deviate from the total market approach should look at Fama-French three-factor model analysis; a deep dive into the world of regression analysis (using past performance to predict the future). Normally performed with more complicated tools, i.e. the R programming language, Bill Bernstein shows how to do regression analysis with a spreadsheet (Rolling Your Own: Three Factor Analysis). For those interested in going beyond Excel, see Using open source software for portfolio analysis.

Summary

Having the right tool for the job, and knowing how to use it, will allow you to stay in control of your financial situation. A spreadsheet should be a permanent part of your personal finance toolkit.

Notes

Apache OpenOffice Calc is another free spreadsheet tool; available for Microsoft Windows, OS X, and Linux.
(Revised Sep 11, 2014)

Tagged with: , , , , ,