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

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

Cascading Asset Allocation

Asset allocation visualized

  • Asset allocation – Spreadsheets are available to show you everything about your portfolio; from a basic tabular format to real-time tracking and everywhere in-between.
  • Rate of return – This one is important. If you have a list of contributions and withdrawals, and want to know how your portfolio is doing, be sure you understand Internal Rate of Return. Easily done in a spreadsheet, this is the best (and only) way to analyze portfolio performance .
  • Tax lot tracking – Keep track of your investments for tax purposes.
  • Morningstar Instant X-Ray is a website tool which will breakdown your fund into asset classes. Use the results to configure your fund’s asset class composition.
How to solve every financial problem

No, this not about winning the lottery. Just about every financial question can be answered by drawing a cash flow diagram and setting up the 5 financial variables.

Comparing investments - effect of interest rates“What will my mortgage payment be? How much interest am I paying on this loan? Which is the better investment choice?”

These questions and much more are answered in Comparing investments – an introduction to the Time Value of Money and is well worth learning.

Building on the basics

Comparing CDs builds on these concepts to help  you compare investment choices for a Certificate of deposit (CD), such as deciding when to break (redeem) a CD early.

Compounded interest is why we stress “invest early and often” as the best way to reach your retirement goals. The math also works in the opposite direction. Effect of expenses on a portfolio shows how an expense ratio reduces your portfolio’s value over time. Costs matter.

Why does a bond’s interest rate move in the opposite direction of its price? The answer is in Bond pricing. Bond yield expands on this concept to explain Yield to Maturity.

Retirement planning

You’ve saved for retirement. Or, you’re already in retirement and have to make the money last. Now what? We’ve got you covered:

Compare case earnings

Retiree portfolio model

  • Retiree Portfolio Model – A comprehensive retirement forecasting model for portfolio accounts, retirement income, expenses and taxes over a selectable period of up to 40 years. It answers questions such as “Should I do a Roth Conversion?,” “What if I delay taking Social Security?,” “What if the tax rates jump when I’m 80?”, or “How much can we afford to spend if our portfolio returns tank?”
Backtesting table withdrawals

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

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.

Simba's portfolio backtester

Simba’s portfolio backtester

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.


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.


Apache OpenOffice Calc is another free spreadsheet tool; available for Microsoft Windows, OS X, and Linux.

(Revised Sep 11, 2014)

Tagged with: , , , , ,
Posted in Investing, Investment Theory, Personal finance, Retirement
September 2014
« Aug   Oct »
Follow Financial Page on
%d bloggers like this: