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.
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. Empower is my favorite, and I use it to compile data from several different brokerage accounts when updating my own spreadsheet.
There are some things Empower 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 Empower.
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.
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.
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.
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“.
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.
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“.
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 .
A cut and paste puts them back in Column C.
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”
Just like before, click the fund name and then the little box and select “Price” or “Previous close”.
Copy and paste the price into place in column E.
That wasnt’ so hard.
You can type VFIAX into Column C, but if you’ve forgotten the ticker symbol, it can be generated from the fund name.
Don’t forget to tell the sheet which account the new fund is in.
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.
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.
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.
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.
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.
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.
7 thoughts on “A Net Worth Tracker That Auto-Updates Daily”
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?
ha. i just put out something similar on the blog without the graphs. just now figuring out these new technologies. good stuff. https://freddysmidlap.com/2022/11/30/make-a-stock-tracker-with-google-sheets/
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?
On further investigation, it appears that STOCKHISTORY function requires a Microsoft 365 subscription, which I don’t have.
Interesting. In that case, I recommend importing the file to Google Sheets and using the =GOOGLEFINANCE(“VTSAX“) function to track your portfolio.
Thanks for the update!
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.
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