Using Visual Basic for Financial Modelling

I learned a fair amount of programming when I studied mechanical engineering, and it is always fun when I get to apply those skills to problems that I see in finance.

I was recently asked to determine if a particular client would be better suited to deposit money into an RRSP or to leave it in non-registered investments.

The problems I had to solve revolved around the following issues:

  • Minimizing taxes
  • Minimizing OAS clawback
  • Maximizing net income

But which strategy would produce superior cash flows based on these parameters?  I structured my model as I would structure a discounted cash flow analysis for a company, selecting my inputs to match an individual.  Gross income became a proxy for revenue, and then I added minimum RRIF payments after age 71, and CPP and OAS benefits at age 65.  I treated taxes and OAS clawback like cash outflows .  I calculated taxes based on marginal tax rates, and OAS clawback based on 15% of any income over $70,954 in any year OAS is received.

I wrote functions in visual basic to find the appropriate tax bracket for a given income, to find the minimum RRIF payment based on age and RRIF amount, and to find the amount of OAS clawback.  The calculations in the model lead to two numbers: the present value of the free cash flow in each scenario.  I was able to link the difference between these two numbers to a sensitivity analysis; using scenario 1 (no RRSP contribution) minus scenario 2 ($31,000 RRSP contribution) shows that when the result is negative (red) it makes more sense to make an RRSP contribution, and positive means it is better to forego RRSPs.

The value of writing these programs did not come in creating the original spreadsheet; I could have realistically input all of that data by hand.  The value in coding a fully linked model is that it allowed me to perform the sensitivity analysis for varying growth rates, inflation rates, and levels of CPP income.

I had hoped that there would be a conclusive answer to the initial question, but the result shows that it all depends on what the market does.  If market performance is strong, the RRIF becomes so large that taxes and OAS clawback are overpowered, but with lower market returns, the tax savings make avoiding the massive RRIF accumulation a better option.

For some context, the client is 47 today and will live to 100.  They are making a one time $31,000 RRSP contribution.  The RRSPs value today before any new contribution is $100,000.  Earned income is $110,000.

My very boring and simple code is linked here.