Friday, 24 March 2017

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. 

Wednesday, 8 March 2017

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).

Wednesday, 1 March 2017

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.

Wednesday, 22 February 2017

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.  

Wednesday, 15 February 2017

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.

Wednesday, 8 February 2017

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).

Tuesday, 31 January 2017

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