A Net Worth Tracker That Auto-Updates Daily

Over the years, I’ve developed and fine-tuned a portfolio and net worth tracker that I’ve found quite helpful as do-it-yourself investor.

The latest version of the spreadsheet is the best yet. It can be set up to automatically update the value of your stocks and funds using their ticker symbols. A previous version had this capability with the addition of an extension, but I’ve since discovered a way to use Excel’s built-in tools.

Furthermore, the sheet can be exported to Google Sheets, and with a few tweaks, it will work well there, as well. I don’t own any iDevices, so I can’t speak for Apple’s Numbers software, but you can obviously use this sheet with Excel or Google Sheets on an Apple device.

The file is available for download below, but before you grab your copy, have a look at the instructions that will make it easy for you to tailor this sheet for your own portfolio.

 

net-worth-tracker

 

Benefits of a Personalized Net Worth Tracker

 

Using a spreadsheet of your own isn’t the only way to track net worth. Their are free online solutions, as well. Personal Capital is my favorite, and I use it to compile data from several different brokerage accounts when updating my own spreadsheet.

There are some things Personal Capital does automatically that my spreadsheet doesn’t do, like automatically calculate a detailed breakdown of your allocation to large caps, mid caps, small caps, growth, value, REITs, etc… It also lets you compare returns graphically, has a retirement planner, and other cool stuff. You can read more with my top tips and tricks in this review of Personal Capital.

There are some things online apps don’t have, and that’s the virtually unlimited customization available in a spreadsheet. You can make it as detailed as you want it to be, add your own notes or formulas, and play around with what-if scenarios easily.

 

Fortunately for you, I’ve done a lot of the leg work, and with the simple tutorial that follows, you’ll be able to track your portfolio and any assets you wish to add without much hassle.

The sheet I’ve designed will automatically update the net asset value of any asset with a ticker symbol that Excel recognizes, which numbers in the thousands. Your job is to enter your assets, their symbols, and how many shares you own.

Updating the sheet doesn’t take much effort. Once you’ve got it set up, you only need to update your shares owned when you buy, sell, or reinvest dividends.

 

If you like what you’ve heard, download the sheet and read on to learn how to add your investment portfolio and other assets to the sheet.

 

 

Net Worth Tracker Overview

 

Here’s what the template will look like when you first open it.

 

Portfolio_Tracker_17

 

There are enough lines for 35 assets. You can add more if needed or delete a bunch if you’ve got a simpler portfolio.

Each line has a dropdown box that lets you assign an account type to it. When you do so, the sheet will add up the assets that you have in each account and give you the percentage that each account contributes to your overall portfolio.

To the right of the account list is a desired asset allocation that you can adjust in the chart at the bottom.

Available options are US Stock, International Stock, Bonds, Alternatives, and Cash.  The next donut over is your actual allocation at the moment.

For each asset that you include in the main list, you’ll assign a percentage of each asset class. Many will be 100% in one category, but a balanced fund or actively managed fund may have a mix of US stocks, international stocks, and bonds, for example. You can easily find the breakdown at Morningstar and other finance sites.

Finally, you’ll get a chart showing the same asset allocation data in numerical format by percentage and a similar chart showing how far off you are in dollars and cents from your desired balance in each category.

 

Customizing Your Net Worth Tracker

 

I’ll show you how I added the ticker-tracking ability to my old spreadsheet. You can follow the same steps when you add your own assets to the spreasheet.

First, I entered the ticker symbols in the C column. you can see them selected below.

The next step is to copy and paste them into open space on the right. I chose column Q. Once you have them there, go to the Data tab in Excel and select Stocks as the Data Type (highlighted in green).

If you do not see the Stocks option, it may be that you’re not signed in. You must have either a free Microsoft account or a paid Microsoft 365 subscription to access this function.

 

net worth tracker

 

When you hit that Stocks button, every ticker symbol will change into the full name of the asset. You can do a bunch at once if you’d like; there’s no need to do them one at a time.

 

Portfolio_Tracker_02

 

Now that you’ve got a list of assets with the little building icon, click on one of them. A little box with a green plus will show up next to it. Click on that little box to get a list of data points that you can use in your spreadsheet.

I chose “Previous close” since the assets in the template are mutual funds that update once a day. If you have stocks and ETFs whose prices fluctuate throughout the day, you may instead choose “Price“.

 

Portfolio_Tracker_03

 

After selecting “Previous close,” I see the most recent closing price in Column R, but I want them in column E. A simple cut and paste will get them to where you want them.

 

Portfolio_Tracker_04

 

In this example, I did a cut and paste from Column C to Column Q and lost my ticker symbols in C. That could have been easily avoided by doing a copy and paste, but I can easily get the ticker symbols back by clicking on that little box and selecting “Ticker Symbol“.

 

 

Portfolio_Tracker_05

 

A cut and paste puts them back in Column C.

 

Portfolio_Tracker_06

 

Let’s say you buy some S&P 500 in your taxable account and want to add it to the sheet.

Start in Column Q and enter the ticker symbol. In this case, it’s VFIAX. Hit that Stocks button from the data tab and it will become “Vanguard 500 Index Fund;Admiral

 

Portfolio_Tracker_08

 

Just like before, click the fund name and then the little box and select “Price” or “Previous close”.

 

Portfolio_Tracker_09

 

Copy and paste the price into place in column E.

 

Portfolio_Tracker_10

 

That wasnt’ so hard.

 

Portfolio_Tracker_11

 

You can type VFIAX into Column C, but if you’ve forgotten the ticker symbol, it can be generated from the fund name.

 

Portfolio_Tracker_12

 

Don’t forget to tell the sheet which account the new fund is in.

 

Portfolio_Tracker_13

 

Finally, enter “100” in Column I to indicate that it’s 100% US Stocks, and enter the expense ratio of 0.04 under Column N (labeled E.R.). You can skip this step, but I like to see the total cost of the funds I own, and this sheet will do that for you.

 

Start receiving paid survey opportunities in your area of expertise to your email inbox by joining the All Global Circle community of Physicians and Healthcare Professionals.

Use our link to Join and receive a bonus of up to $50 .

 

Don’t have Excel? Fear not. Google Sheets is free to everyone with a free Google account, and you can import the file into Google Sheets.

You’ll have a bit of work to do once you do, but it’s not too cumbersome.

Portfolio_Tracker_14

 

 

Portfolio_Tracker_15

 

Initally, we have a number of red triangles and errors. We’ll address them one at a time.

First, delete the errors and enter your ticker symbols (VTSAX, etc…) in Column C. Delete the errors in the Price column (E) also.

 

Portfolio_Tracker_16

 

Next, in the Price column (E), enter =GOOGLEFINANCE(“VTSAX“), changing the ticker symbol for each asset you’ve got.

For assets with no ticker symbol, like cash and privately held real estate, just enter 1 for the price and enter the value of your holding in the Shares column (F).

Now your sheet should look a lot cleaner.

 

Portfolio_Tracker_19

 

The donut colors aren’t preserved from the original sheet, so you can play with those if you like. Everything else transfers over nicely, though.

As an aside, when I first put this sheet together in May of 2022, it was a million-dollar portfolio. Seven months later, it’s up 0.23%, just $2,317 above break-even.

 

Portfolio_Tracker_20

 

Download the Net Worth Tracking Spreadsheet

 

Enter your email and you’ll be able to download a copy. If you don’t yet subscribe to Physician on FIRE, you’ll join the list, and you can opt out by unsubscribing anytime, including right after you download the sheet, but I recommend sticking around for a while.

 

Capital One Venture X Rewards Credit Card

75,000 point bonus after spending $4,000 in the first 3 mo.
PoF Summary

2 points per dollar spent (2% cash back equivalent). Up to $300 credit each year for travel booked on Capital One Travel, 10,000 bonus miles each account anniversary ($100 value). Unlimited Priority Pass Lounge Access, $100 Global Entry or TSA Pre✓ credit. $395 fee can be more than offset with travel credit & annual point bonus



 

7 thoughts on “A Net Worth Tracker That Auto-Updates Daily”

  1. This is super cool! Thanks so much for sharing. Is there a simple way to adjust the asset allocation categories on the right side, and on the chart below?

    Reply
  2. Subscribe to get more great content like this, an awesome spreadsheet, and more!
  3. Thanks PoF for the use of your spreadsheet. My version of Excel doesn’t seem to have the “data type” on the data tab ribbon from which to select stocks. Is there another way to get to it?

    Reply
    • On further investigation, it appears that STOCKHISTORY function requires a Microsoft 365 subscription, which I don’t have.

      Reply
      • I did a little digging, and according to excel, a free Microsoft account should be all you need.

        From the Help section in Excel: “Note: The Stocks data type is only available to Microsoft 365 accounts or those with a free Microsoft Account.”

        I hope this helps.

        Reply
        • Thank you for this. So nice to see it set up like this. Mine also didn’t have the stock portion. I had a 2019 version but upgraded it and it still didn’t work. Had to contact support to get it figured out. Had to sign into my Microsoft account in two different places and then restart the program. Now how to play around with the colors on the graph. :p

Leave a Comment