Optimizers

If you have made it this far, you are probably thinking "There are so many things I can control: RRSP contributions and withdrawals, when to retire and when take CPP or QPP and OAS - all this for me and my spouse as well. I could spend hours fiddling with the spreadsheet! How can I get the most from my estate?"

Fortunately, Mark's Spreadsheet includes a set of optimizers to help you make those decisions. It is one of the things that sets it apart from other home-grown spreadsheets. These are features that are typically included in expensive software available only to financial planners.

Some real examples are described in Scenarios. Also check out our Scenarios Video Tutorial.

Basic Optimizers

The Basic Optimizers offer a one-click method to optimize your retirement projection depending on the goal you want to achieve.

Estate: maximizes your Net Estate Value in the Final Year by trying different suggested RRSP contributions (if you are still working), different retirement fund withdrawals and different CPP or QPP and OAS starting ages.

Retirement Age(s): maximizes your Net Estate Value then decreases (or increases) the retirement ages of both spouses until your Account Balances approach zero.

Budget: maximizes your Net Estate Value then increases (or decreases) your annual budget until your Account Balances approach zero. Any downsize budgets are also adjusted accordingly.

Donations: maximizes your Net Estate Value then increases (or decreases) your charitable donations until until your Account Balances approach zero.

Stop: stops an optimization while in progress.

If your Net Estate Value is negative after the optimizer has run it means that your retirement is under-funded and you will die with debt. Reduce your Annual Budget and/or Charitable Donations and/or increase your retirement age(s) to fix the problem.

If your Account Balances are negative after the optimizer has run but you have equity in your principal residence, it means that you have gone into debt to remain in your home. Consider downsizing and/or a reverse mortgage to generate additional funds.

Show on Charts: allows you to select whether Age(s) or Years are shown on the horizontal axis of the charts.

Advanced Optimizers offer a higher level of control than the Basic Optimizers. Certain settings must be set in advance and achieving a desired result may require running multiple optimizers.

Settings

Current Year Adjustment: A quick way to see the effect of a one-time tax free cash injection into or extraction from your investments in the current year. This can be used to simulate, for example, an unexpected inheritance or large expense. Use a positive amount for money received and a negative amount for money spent. Set by the Stress Test optimizer.

What to Optimize: Select the value to optimize.

Net Estate Value - depending on the optimizer, attempts to maximize or minimize your Net Estate Value in the Final Year. Use this setting to optimize the value of your estate in the Final Year.

Account Balances - depending on the optimizer, attempts to maximize or minimize the total of your Account Balances. Use this setting when maximizing your Retirement Age(s), Annual Budget and Charitable Donations.

Optimizer Mode: Some of the optimizers are very compute intensive and can take minutes to run on a relatively powerful computer. This setting provides some options. Regardless of the setting, the optimizer will stop if it converges on solutions that are within $1,000 of each other. In an estate worth a million this is less than 1/10th of one percent - more than overwhelmed by the uncertainty of the assumptions used by the spreadsheet.

Faster - Runs the algorithm for fewer iterations resulting in a faster, but potentially less precise solution. Best for older/slower computers.

Balanced - A good compromise between Faster and More Precise.

More Precise - Runs the algorithm for more iterations in an attempt to find a more precise solution. Best for newer/faster computers. Note that it is not worth the time or computing power to search for solutions to a very high precision given the inherent uncertainty in projecting finances many years into the future.

Advanced Optimizers

The advanced optimizers try a variety of inputs (dependent on the optimizer) in an attempt to maximize or minimize Net Estate Value or Total of Account Balances (according to your selection) in the Final Year. Due to the almost infinite number of input combinations, it is impossible to test each one. The optimizers work by finding promising inputs and then refining those inputs until a solution is found. This will be a good solution but may or may not be the best overall solution. Through experimentation and manual tweaking of inputs you will find what works best for your situation. To run an optimizer, select it from the drop down menu and click Go. Use the X button to stop an optimization while in progress.

Optimize RRSP Contributions: tries different suggested RRSP contributions in an attempt to maximize Net Estate Value or Total of Account Balances in the Final Year.

Optimize Pension Ages: tries different CPP or QPP and OAS starting ages in an attempt to maximize Net Estate Value or Total of Account Balances in the Final Year.

Optimize Retirement Fund Withdrawals: tries different suggested retirement fund withdrawals in an attempt to maximize Net Estate Value or Total of Account Balances in the Final Year. Retirement funds include RRSP/RRIFs and LIRA/LIFs as applicable.

Optimize Withdrawals & Pension Ages: combines the previous two optimizers.

Optimize All: combines Optimize RRSP Contributions, Optimize Pension Ages and Optimize Retirement Fund Withdrawals.

Stress Test: finds the Current Year Adjustment that results in a near zero Net Estate Value or Total of Account Balances in the Final Year. A positive amount indicates that the retirement plan is underfunded by that amount. A negative amount indicates that the retirement plan will tolerate a withdrawal at the beginning - a sign of strength. Clear the Current Year Adjustment when you are done.

Retire Early: decreases (or increases) the retirement ages of both spouses until a near zero Net Estate Value or Total of Account Balances is achieved in the Final Year. Shows the earliest age(s) at which retirement can occur without allowing for contingencies.

Maximize Annual Budget: increases (or decreases) the annual budget until a near zero Net Estate Value or Total of Account Balances is achieved in the Final Year. Shows the maximum annual budget that can be spent without allowing for contingencies.

Maximize Donations: increases (or decreases) the charitable donations percentage until a near zero Net Estate Value or Total of Account Balances is achieved in the Final Year. Shows the maximum amount that can be donated without allowing for contingencies.