An Awesome (and Free) Investment Tracking Spreadsheet

Awesome Free Investment Tracking SpreadsheetThere are a lot of tools out there to track your investments. One of my favorites is Personal Capital. It offers a free online financial dashboard that automatically tracks your investments and your spending. Today I have another option for you–a free spreadsheet that tracks your investments and tells you when it’s time to rebalance your portfolio.

I have to give a BIG thank you to a reader named Dan. He built this spreadsheet and shared it with me weeks ago. With his permission, I’ve created a copy and am sharing it with you today. Thanks, Dan!

The Spreadsheet

To start, you can see the spreadsheet by clicking here. It is build in Google Sheets. When you go to the spreadsheet, you’ll see a sample portfolio built with Vanguard mutual funds and ETFs. This is not my portfolio (or Dan’s). But it will give you a clear picture on how it works.

You’ll want to make a copy of it so that you can edit it and save your own portfolio. Remember, in order to make a copy, you’ll need a Google account and you’ll need to be signed in. Once that’s taken care of, you can make a copy by selecting ‘File’ from the menu and then ‘Make A Copy.’

The workbook has just two sheets. The first sheet lists all of your holdings (click image to enlarge):

Investment Tracking Spreadsheet

The sections in color you’ll need to fill out. Once you do, the other columns get automatically populated using the Google Finance function and built in formulas. Note that for ETFs you’ll need to input the expense ratio yourself. For reasons unknown, the Google Finance function doesn’t provide it.

The second page provides a summary of your investments by asset class, tells you when it’s time to rebalance, and provides a graph of your investments:

Asset Allocation Spreadsheet

The Rick Ferri and Personal Capital allocations are there just for ideas. You’ll enter your asset allocation in the column labeled “My Target.” Note that the spreadsheet comes with five asset classes listed in rows three through seven. The spreadsheet calculates how much you have in each asset class based on column D of the first page of the workbook. If you want to add or change asset classes, you can. Just remember to add them in column D of page one and then add a row for each new class on this page.

Rebalancing

One of the cool features of this spreadsheet is the rebalancing function. You’ll see in the sheet above columns labeled ‘Actual’, ‘Diff’, and ‘Threshold.’ It’s critical to understand how these work.

Actual: This simply calculates the actual amount of investments you have in each asset class. This data comes from the Holdings page of the spreadsheet.

Diff: This column calculates the difference between your target allocation by class and your actual allocation. Note that some of the cells in this column are in red.

Threshold: This column allows you to set a threshold for each asset class. In other words, how much can your actual investments drift from your target allocation before you need to rebalance. Once your actual allocation drifts from your target by the threshold percentage, the difference in the Diff column turns red. It’s a visual way to see what part of your portfolio might need to be rebalanced.

Leave comments below if you have any questions on how to use the spreadsheet.

Enjoy!

Published or Updated: May 26, 2015
About Rob Berger

Rob founded the Dough Roller in 2007. A litigation attorney in the securities industry, he lives in Northern Virginia with his wife, their two teenagers, and the family mascot, a shih tzu named Sophie.

Comments

  1. Jon says:

    Just wondered if for those with CD’s part of the portfolio (safer than bonds and yet yielding presently more than short/intermediate term bonds), whether the google spreadsheet could be updated for this possibility?

  2. Jon says:

    Or the possibility to add individual bonds?

    • Rob Berger says:

      Jon, you certainly can add individual stocks to the spreadsheet. I do that for my portfolio. I’m not so sure about individual bonds, at least form a Google Finance perspective. You can of course add anything manually.

  3. Grant says:

    Rob, very nice spreadsheet. I live in Canada, and it works here, too, although with 2 issues.

    1. One of the ETFs I own is iShares emerging markets ETF, XEC. When I enter that symbol, Google Finance recognizes that as Cimarex Energy Co. I can manually change the price to that of iShares emerging markets ETF, but would you know how I could correct that?

    2. I also own 3 US domiciled ETFs which are quoted in US $s, so I need to manually change the value to Canadian $s. I guess there is no easy way around that.

    Thanks!

    • Rob Berger says:

      Grant, on #1, that’s a GoogleFinance issue. The only thing I know to do is enter and update it manually.

    • Justin says:

      Regarding currency, Column G’s strings could read “=GOOGLEFINANCE($B3,”price”) * GoogleFinance(“CURRENCY:USDCAD”)”. This assumes investments are held in one’s own currency. Better yet, code the string so that it looks within a user-input cell containing the currency one prefers. That way it is more universal.
      The currency exchange rate function could also be used as a field for each investment if one wants to track impact of exchange rates for foreign investments purchased in native currency.

    • Mihir says:

      You can try using the importhtml function in google sheets to import ducky from Morningstar or yahoo finance. I know few folks on bogleheads do so, although I’ve never tried it.

  4. Mark Church says:

    Great spreadsheet Rob. I added YTD return to mine using google finance feature. Thanks for sharing.

    • Rob Berger says:

      Mark, nice addition. I’ll add it to the spreadsheet as well.

  5. Grant says:

    Rob, I notice that the spreadsheet works fine when run directly off google docs, but when I try to save the file as an excel file to my desk top, some of the fields are filled with “value” with no total displayed. What am I doing wrong? Thanks again

    • Mike says:

      Same problem for me. Also, I tried to add some extra rows to input additonal funds and the formulas didn’t copy. When I went back to copy formulas, data changed to n/a value, etc…..Seems to act a little strange when adding lines, copying formulas, etc.

      • Justin says:

        Both are spreadsheet programs but built on different codes. It’s like trying to play a nintendo game in a playstation.
        Try googling for a VBA macro to use google finance api in excel but you’re better off installing the Yahoo finance add-in.

  6. Brett says:

    Rob,

    My understanding is that the “category” column, which represents the funds asset class, is self-populated, not auto-populated. So, if you have a fund that has multiple asset classes, like many do, what would you say is the best way to manage that? Do you have to enter multiple lines for that fund and divvy out the percentage of shares for each line based on the percentages of each asset class within the fund?

    • Rob Berger says:

      Brett, that’s a interesting issue. As the spreadsheet currently works, you would have to create multiple lines for the same investment. I’ll give some thought to whether a better solution exists for that situation.

  7. Justin says:

    Rob, I wouldn’t worry too much about comments regarding specific issues people are going to bring up. You provided a good template that anyone can modify to their liking. The syntax might differ slightly from the more popular MS Excel but documentation regarding google spreadsheet is robust.

Speak Your Mind

*