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.

Optimize & Test

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. 

The Optimize & Test section is where you refine your projection.

Optimize: experiments with different CPP and OAS starting ages, RRSP contributions and Registered Retirement Fund (RRSP/RRIF/LIF) withdrawals in an attempt to maximize your Estate at the end of your projection. Your Estate is the amount that is left to heirs or charity when the last person dies. The optimizer uses the contribution and withdrawal strategies you have selected. In most cases these should be set to Hybrid.

Optimize also calculates three options that can be applied to your projection. Press the arrow button next to the option to apply it to your plan. Applying any of the options will reduce the robustness (safety factor) of your projection.

    1. Retirement Age(s): determines how early you (and your spouse) can retire.

    2. Budget: determines how much you can spend. The downsize/upsize budgets are also adjusted proportionally if you select this option.

    3. Donations: determines how much you can give to charitable causes while you are alive.

Test: runs four stress tests against the projection in an attempt to determine the robustness (safety factor) of your projection.

    1. Account Withdrawal Test: determines how much money can be removed from your accounts (or must be added) in year one.

    2. Total Return Test: determines the maximum total return reduction in all accounts for all years.

    3. Inflation Test: determines the maximum sustained inflation rate over all years.

    4. Longevity Test: determines if the projection supports either and both spouses living to 100 years of age.

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

Settings

Current Year Cash 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 Account Withdrawal Test.

Total Return Adjustment: Applies a percentage adjustment to the total returns of all your accounts. This can be used to model an extended period of decreased or increased returns. For example a -1.0% adjustment will change a 5% total return to 4%. Savings accounts will not go below 0% interest. Set by the Total Return Test.

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.

Accounts Target: This amount is entered in the Projection Summary. 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.

Advanced Optimizers

Unlike the basic Optimizer, the Advanced Optimizers allow you to set RRSP contribution strategies, retirement fund withdrawal strategies and what you are optimizing (Net Estate Value or Account Balances). It may be necessary to run multiple optimizers to achieve your desired result. The 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 (depending on 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. Through experimentation and manual tweaking of inputs you will find what works best for your situation.

To run an Advanced Optimizer select it from the drop down menu and click the "Go" button. Beginners should start with the optimizer in the Optimize & Test section then progress to the Advanced Optimizers if more control is needed.

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

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

Optimize 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 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 RRSP Contributions, Optimize Pension Ages and Optimize Retirement Fund Withdrawals optimizers.

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. Determines 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. Determines 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. Determines the maximum amount that can be donated without allowing for contingencies.

Account Withdrawal Test: determines how much money can be removed from your accounts (or must be added) in year one.

Total Return Test: determines the maximum total return reduction in all accounts for all years.

Inflation Test: determines the maximum sustained inflation rate over all years.