My Updated Net Worth (and more) Tracking Spreadsheet

I got an email from a reader today:

First off, I’ve been reading your blog for a couple years now and greatly enjoy your monthly ramblings of all things ‘useful’.

Maybe an odd ask, Do you mind sharing a copy of your net worth worksheet (10-Q?…10-“M”?) you share every month? A blank/bare bones version is fine.

I’ve tracked my own finances in a homemade excel file for a couple of years but have been inspired recently to clean up my financial house and find your business line item reporting easy to follow.

I’ve posted my net worth tracking spreadsheet before, but I update it periodically so here is the latest iteration.

=======> You can download it HERE <=======

My spreadsheet is organized as follows:

• Sheet 1: Financial Statements
  • Columns B through G is what I post every month.
  • I realize that the sizing of the text is unconventional, but it produces a higher resolution image when I paste into MS Paint for the monthly updates.
  • Each month I select cells E2:BQ137 and paste one column to the right. All charts and equations should auto-update with the addition of a new month.
• Sheet 2: Backup calcs & data import pages
  • This is where I integrate Personal Capital data into a usable format for my spreadsheet. Once configured, it’s a simple copy paste from Sheet 2 to Sheet 1.
    • Cells L14:L64 from Sheet 2 are pasted into Cell E19 of Sheet 1. Similarly, Cells R15:R24 of Sheet 2 are pasted into Cell E90 of Sheet 1.
• Sheet 3: Cash flow projections
  • This is where I project cash flows about 4 months out to see how broke I’m going to be in the future. This tool is what enables me to invest aggressively earlier. I strive to hold no more than $1k in cash at any point in time (related blog post).
• Sheet 4: CC Status
  • Because BoA doesn’t let you monitor the $2.5k/quarter spend per card for their 5.25% cash back cards, I download transaction data from Personal Capital and create a pivot table to monitor.
• Sheet 5: Fidelity Rebalancing Calc
  • Here is where I calculate the rebalancing calcs within my retirement accounts to get my global portfolio holdings to my desired asset allocation. It is costless taxwise to rebalance within tax sheltered accounts, which allows for my taxable accounts to be 100% VTSAX, for example.
    • I have a post about this here: https://frugalprofessor.com/asset-location/

 

 

A few screenshots:

For those new to this blog, the above is the output of my spreadsheet — a monthly comprehensive snapshot of all aspects of one personal finances, inspired by the financial reporting of firms to investors.

 

On Sheet 1, there are a bunch of rows that I normally hide from view (114-140) because they don’t provide much value to the monthly report. However, they are necessary for the plots that follow. Oddly, plots cannot refer to “hidden” rows in Excel, so I simply shrink the relevant rows to a pixel or two.

 

Here is where I copy the Personal Capital stuff into sheet 2. The balance sheet is pasted into cell U7. Expenses are pasted into cell X7. Interest is manually added in cell Z9 (or pasted from Personal Capital). Investment holdings are pasted into cell AB7.

 

 

This is what I copy and paste into cell U7 in sheet 2.

 

This is what I copy and paste into cell X7 in sheet 2. Importantly, if you have merchandise returns on a credit card, it should already be netted out of these expenditure numbers.

 

This is what I copy and paste into cell AB7 in sheet 2.

 

 

Sheet 3 (Cash flow projections): Here’s what I use to forecast our future cash flows several months out. I find it to be pretty handy.

 

 

Sheet 4 (CC Status): Here’s my pivot table to compute quarterly BoA spend by card.

 

 

Sheet 5 (Fidelity rebalancing calc): Here’s where I compute the rebalance I need to perform at my Fidelity workplace plans to achieve my desired asset allocation.

 

 

A few comments:

• Is it worth doing this nonsense?
  • I’d recommend it, but I’m biased. I think it imposes incredible discipline on one’s finances.
    • Importantly, I think it absurd to spend ~2,000 hours/year at work trying to earn money and ~0 hours/year thinking about where this money is going.
  • My spreadsheet is the most comprehensive financial tool that I’ve seen for households. It mimics the fidelity of data you’d see in the accounting statements of firms. However, accounting is universal and there is nothing preventing these same techniques from being applied to personal finances as well.
• What specifically is the value?
  • It provides the most comprehensive view of one’s finances I can think of. Unlike most software that I’m aware of (e.g. Personal Capital, Mint, YNAB, etc), my spreadsheet appropriately tracks every penny of taxes that flows through the financial statements. It also appropriately tracks 401k matching as an integral part of one’s compensation.
  • It produces high-fidelity and useful charts:
    • The “Decomposition of income” chart powerfully conveys rolling 12-month savings rates over time.
    • I love the rolling 12-month spending chart, which appropriately smooths spending over 12 month intervals, helping to smooth out the noise induced by volatile monthly spending.
  • The reconciliation of the statement of cash flows with the income statement and balance sheet (e.g. Row 87 of the “Financial Statement” sheet) proves that you’ve successfully tracked every penny of transactions that has flowed through your accounts: 401k match, taxes, every penny of spending, etc. It is pretty rewarding to get this to zero.
    • For the first several years of producing this spreadsheet, I systematically failed to match my financial sheet by small amounts. Finally, I isolated the source of these errors to how our checking account handles pending transactions. Pending transactions at this checking account do not show up under “budgeting”, but are already incorporated into the balance. Since discovering this, I ensure that pending transactions are appropriately accounted for in “budgeting” and I’ve managed to track our finances to the penny for 7 months in a row.
      • At most other institutions (e.g. credit card companies), I’ve found that the balances aren’t updated until the transaction ceases to be “pending.”
  • It tracks progress to FI better than anything I’ve seen, appropriately accounting for actual spending.
  • The first few rows of the Financial Statements sheet help me to track YTD savings goals/priorities.
  • I love the cash flow projection sheet, which helps me to aggressively manage cash holdings.
• What if you don’t use Personal Capital?
  • There is nothing preventing you from tracking your finances manually using only the first sheet.
• Any hints?
  • I track our finances throughout the month using the spreadsheet. I find that doing several checkups throughout the month makes it a lot easier to balance the financial statement than a single effort at the end of the month. There are so many weird transactions that I have to account for that I find it easier to deal with them as they come.
  • While I can easily look up historical spending information in Personal Capital, I cannot readily view high-fidelity time series balance information. Consequently, it is important to “close the books” in a timely manner each month (or quarter).
• I think that Personal Capital is an evil asset management company that preys on the ignorance of the uninformed, but I happily use their free financial tool. I would unambiguously advise against using their asset advisory services. Before switching to PC, I’d used Mint/Yodlee in the past. Both worked fine.
  • Please use my referral link to sign up for Personal Capital. Just joking. For some reason they’ve determined that I’m not a good candidate for their blogging referral program; I can’t figure out why!
• Tracking your finances in this detailed manner may give you OCD, though it may also make you wealthier. Consider yourself forewarned.