Adjusted Cost Base Tracking Spreadsheet

ACB v1p1

This spreadsheet is designed to help you keep track of your adjusted cost basis (ACB) for your stocks, mutual funds or ETFs. You can download version 1.1 here.

The spreadsheet was updated to version 1.1 in May 2017. Ability to enter foreign exchange rates for stocks and funds listed in foreign currency has been added. 

My post Financial Basics 12 - Keeping Track of Adjusted Cost Base gives a good summary of why and how to use the spreadsheet. 

Each sheet within the workbook is to be used to track ACB for one stock, mutual fund or ETF. 

Retirement Planning and Forecasting Spreadsheet 2.0 (for Canada)

Retirement Forecaster 2.7

Updated to version 2.7 on March 24th, 2017. 

In this version I have updated the tax tables to include the 2017 federal and provincial tax brackets and rates. There was nothing in the March 22nd budget that affected the calculations.

Download new version here.  

A complete list of upgrades by version is included in the Instructions sheet in the workbook.

This spreadsheet allows you to make an accurate financial plan for your retirement, starting at any time in your life.  Unlike many retirement tools, this spreadsheet shows a cash flow forecast vs time and identifies which account money is saved into (during employment) and which account or source the retirement income is withdrawn from.  It handles the different account types (RRSP, LIRA, TFSA and a taxable investment account), the limits imposed on them and does income tax calculations for any of the provinces.  

The picture below shows the main page of the spreadsheet which contains all the inputs you need (in yellow) and provides a cash flow forecast and how the savings accounts increase and decline.  

I hope you find the tool useful and I am always willing to hear of suggestions to improve it. 

X-Y Plots (4Y) Spreadsheet - Another Easy Way To Explore Your Data

This Spreadsheet...

• This spreadsheet is similar to the X-Y Plots spreadsheet but with a few differences:
• You can plot up to 4 Y variables on the same chart.
• Each category is a separate plot. 
• You can easily switch between plots (different category).

Monte Carlo - Run Monte Carlo Simulation without expensive add-ins

(Stanislaw Ulam - inventor of Monte Carlo Simulation)

This Spreadsheet...

• This spreadsheet allows you to run Monte Carlo simulation without use of expensive add-ins such as Crystal Ball
• Allows definition of many different input distributions.
• Input distribution may also be input from actual data.
• Shows charts of input and output distributions, along with basic statistics.
• Copies results to Histograms Etc spreadsheet for more detailed results.

Histograms Etc - Easy Histograms, Probability Plots and Statistics

This Spreadsheet...

• This spreadsheet allows you to quickly create professional looking histograms, probability plots and statistics of data.
• Plots histograms and cumulative probability plots for up to 10 distributions of data.  
• Calculates statistics for each data set.
• Calculates best fit normal and lognormal distribution.  

X-Y Plots Spreadsheet - An Easy Way To Explore Your Data

This Spreadsheet...

• This spreadsheet allows you to quickly create professional looking plots of data.
• This spreadsheet is useful for looking for trends and relationships in your data.
• You just paste in the data to the data sheet, name the columns and select which columns you want to plot in the Chart sheet.
• With this spreadsheet it is easy to:
• plot separate series (different colour points) categorized by one of your data fields.
• filter the data you want to plot.
• plot regression lines through the different series.
• plot bin averages for scattered data.
• annotate the data with labels automatically.

Regression Modeling Spreadsheet (automatic stepwise multiple linear regression)

This spreadsheet does:

• Multiple linear regression.
• Can use linear, square and interaction terms for all the variables you enter.
• Stepwise multiple linear regression to find the best model automatically.
• Provides results and many useful diagnostic plots.
• Provides a suggestion of which independent terms may be confounded (useful for regression of experimental design data).

My Philosophy On The Use Of Spreadsheet Tools

Spreadsheets are great tools. As much as computers have transformed the way we work (and play to some extent) the spreadsheet has been as important to transform the way the technical professional works. Many professionals use spreadsheets to do things that were once extremely time consuming. When I went to University in the early 1980s, we did plotting, data analysis, statistics and regression by hand or with the use of calculators. It took a long time and was easy to make mistakes, which then would require the calculation or plot to be re-done. By the time I started working in the mid 1980s, personal computers were making their way into the office and spreadsheets arrived shortly thereafter. First was Lotus 1-2-3, which was ground breaking, but eventually Excel became the standard. 

Good old Lotus 1-2-3

CPP Benefit Calculator Spreadsheet

CPP Calculation v1p2

This spreadsheet will estimate your CPP Benefit based on past pensionable earning and future earnings.  You can vary the age you retire and the age you elect to take CPP benefits.  Download the spreadsheet in excel format here and in Google Sheet format here.

Updated to v1.2 on December 11th, 2016

• Added the over 65 dropout provision.
• Updated the government YMPE values for 2016 and 2017.

Updated to v1.1 on April 23rd, 2015

• The spreadsheet has been updated to fix some bugs and update some values that have changed.  

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. 

Experimental Design for Reservoir Simulation

ED4Sim v2p2

ED4Sim is a spreadsheet tool designed to assess uncertainty in petroleum reservoirs using reservoir simulation.  It is a very powerful spreadsheet, that has state of the art capabilities in an easy to use flexible package.

This spreadsheet was developed mainly while working at Nexen, and along with the RSMPlots spreadsheet, the company has allowed it to be released publicly. 

Update June 7th, 2016

The link to the Essential regression add-in is broken and I am unable to find this tool elsewhere. This is surprising as I consider it the best free regression tool and as good or better than commercial (not free) tools. I have provided links below to my copies of this tool.

Portfolio Live - Investment Portfolio Rebalancing Spreadsheet

Portfolio Live v1p3

The objective of this spreadsheet is to allow you to re-balance your investment portfolio while reducing purchasing costs and maximize tax efficiency. It does not spread the funds (or stocks) across all accounts, but tries to keep all of each fund in just 1 or 2 accounts. 

Updated to version 1.3 on October 15th, 2015 to fix a minor bug. Updated to version 1.2 on August 28th, 2015 to add new functionality to allow you to balance the portfolio based on Asset Classes instead of just by each Asset (Fund or Stock). In the spreadsheet there are two separate sheets, one to do the calculation by Asset and the other by Asset Class. 

Version 1.1 added new functionality to allow you to force funds not to be sold from any of the accounts. This feature can be used to allow you to balance the portfolio as best as possible while not selling funds with unrealized capital gains in the Taxable account. 

Here's a link to the spreadsheet on Google Sheets. After you open this please make a copy to your own Google Drive.  

Here's a link to an Excel version. Note that in this version you need to enter stock, ETF and mutual fund prices by hand, or use this Google Sheet - Portfolio Prices - to pull prices from Google Finance and then paste them into the Excel spreadsheet. 

Retirement Planning and Forecasting

Retirement Planning and Forecast Spreadsheet (for Canada)

Note that this version of the spreadsheet is now out of date. Go to my post on the new version here.

Updated to version 1.12 on May 7th, 2015 for update to TFSA limit and RRIF minimum withdrawal percentages. Download new version here.  See bottom of post for a list of enhancements and fixes. 

This spreadsheet allows you to make an accurate financial plan for your retirement, starting at any time in your life.  Unlike many retirement tools, this spreadsheet shows a cash flow forecasts vs time and identifies which account money is saved into (during employment) and which account or source the retirement income is withdrawn from.  It handles the different account types (RRSP, LIRA, TFSA and non-tax free account), the limits imposed on them and does income tax calculations for any of the provinces.  

The picture below shows the main page of the spreadsheet which contains all the inputs you need (in yellow) and provides a cash flow forecast and how the savings accounts increase and decline.  

I hope you find the tool useful and I am always willing to hear of suggestions to improve it. 

Reservoir Simulation Plots Spreadsheet

RSMPLOTS v5p1

RSMPlots is a very powerful spreadsheet tool for plotting results from reservoir simulation results files (RSM files). It allows you to quickly scan through many wells, compare many runs and compare results to history, all very quickly.  

This spreadsheet was developed mainly while working at Nexen, and along with the ED4Sim spreadsheet, the company has allowed it to be released publicly.