Monday, 8 August 2016

Net Worth Tracking Spreadsheet

Net Worth Tracking v1p0

This spreadsheet is designed to help you keep track of your Net Worth, those you designate as Retirement Assets and compare your assets to forecasts created from the Retirement Forecast spreadsheet.

You can download the blank spreadsheet here. If you want one with some example data already filled in, download that here

The basic features are summarized:
  • Allows tracking of all assets and to categorize those for summary into different classes and accounts (RRSP vs TFSA vs Taxable)
  • Allows tracking for 2 people.
  • Can assign assets as retirement and non-retirement. Non-retirement are included in Net Worth, but not Retirement Assets.
  • Provides a summary of retirement assets within each type of account (TFSA, RRSP, LIRA and Taxable) for input into the Retirement Forecaster spreadsheet.
  • Calculates performance metrics for each account (or asset) and for all investment accounts (assets) together.
My post Financial Basics 13 - Tracking Your Financial Performance gives a good summary of why and how to use the spreadsheet. 

This is a pretty simple spreadsheet. As per usual, only enter data into cells that are shaded in yellow. Do not change any other cells.  

The NetWorth sheet is used to track all your assets (house, car, cash and investment accounts)

In the NetWorth Sheet:
  • Input a start year and interval you want to track your networth. This will change the dates at the top of the sheet.
  • Under the different asset types, input the name of the asset, the type and which person the assets belong to.
  • The type of asset can be non-retirement, TFSA, RRSP, LIRA and Taxable. If it is one of the last 4 it is considered a retirement asset (the asset is used to provide retirement income)
  • Assets such as House and Cars would likely be non-retirement.
  • Liabilites and Cash should be considered a Taxable asset. They will reduce your cash available to fund your retirement.
  • Under the dates, input the value of each asset on that date.
The first section is for Assets, such as house and cars.

The next section if for Cash accounts, then Liabilities such as mortgages, loans and credit card balances.

Then a large section for all your Investment accounts. You may not have that many at a specific time, but over time you may transfer banks or brokerages and this provides you enough space to maintain all that history.

The next section is where you enter the contributions to that investment account during the period ending at the date shown at the top. This information is required to calculate investment performance. 

A Summary of all your assets is given, along with summaries by person and account type. This summary is for input as starting balances in the Retirement Forecaster spreadsheet.

The last section in the NetWorth sheet gives investment account performance by period and by year. You can change the yellow cell to show which account or assets the performance is to be calculated for. Scroll to the bottom of the list in the dropdown box to find "Total Investments".

The Networth_Chart shows total Net Worth and Retirement Assets over time.

The Forecasts sheet is used to input Net Worth forecasts from the Retirement Forecaster spreadsheet. 

To get the correct values from the Retirement Forecaster do the following
  • Change the "Summary and Plots in Current $" setting to 0. 
  • For the asset forecast for Both people, go to the Both sheet and copy the values from the "Accounts Total Balance" column (column Q).
  • For the asset forecast for one person, go to either the Person1 or Person2 sheet and copy the values from the "Accounts Total Balance" column (column AE).
  • Make sure to paste the data as values and make sure to match up the dates correctly. Values in this spreadsheet and the Retirement Forecaster are shown for the start of the year indicated.
The Forecasts_Chart shows your actual Retirement Assets vs the forecasts you have entered.

Disclaimer:  These posts are not fully comprehensive financial advice.  You should seek your own qualified investment, tax and legal advice.

No comments:

Post a Comment