Optimizers

If you have made it this far, you are probably thinking "There are so many things I can control: retirement fund 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.

Optimizers

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

For the best performance, have only one spreadsheet open at a time and keep Microsoft Excel running in the foreground when running an optimizer. Do not edit the spreadsheet while an optimizer is running. 

Estate: determines the maximum amount you can give to heirs or charity through your estate.

Retirement Age(s): determines how early you (and your spouse) can retire without running out of money.

Budget: determines how much you can spend without running out of money.

Donations: determines how much you can give to charity while alive.

Test: determines how much your projection is over or under-funded in year one - how much can be removed, or must be added, so that your account balances approach zero at death. It is wise to plan for some over-funding to maintain flexibility in your projection. The result of the test is shown in the status area. Always obtain independent financial advice.

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.

Account Balances Target: By default, the Retirement Age(s), Budget and Donations optimizers aim drain your accounts by the Final Year. To keep a residual amount in your accounts, enter the amount here and re-run the optimizer.

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

Account Balances Target: This amount is entered in the Basic Optimizers section. By default, the Retirement Age(s), Budget and Donations optimizers aim drain your accounts by the Final Year. To keep a residual amount in your accounts, enter an amount and re-run the optimizer.

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.

Hide Charts when Optimizing: Previous versions of the spreadsheet showed updating versions of the charts while the optimizers were running. This does not work on Microsoft 365 and updating the charts slows the optimization so now this option is off by default.

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 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 Pension Ages: tries different CPP/QPP and OAS starting ages in an attempt to maximize Net Estate Value or Account Balances in the Final Year.

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

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

Optimize Contributions & Withdrawals: combines the Optimize Contributions and Optimize Withdrawals optimizers.

Optimize Withdrawals & Pension Ages: combines the Optimize Withdrawals and Optimize Pension Ages optimizers.

Optimize All: combines the Optimize Contributions, Optimize Pension Ages and Optimize Withdrawals optimizers.

Stress Test: finds the Current Year Adjustment that results in a near zero Net Estate Value or 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 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 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 Account Balances is achieved in the Final Year. Shows the maximum amount that can be donated without allowing for contingencies.