Optimization

This guide discusses optimization, when the value you are trying to identify is the result of a simulation. We provide a linear optimizer written in VBA that can be used to solve simple functions.

The examples in this guide use the RiskAMP Monte Carlo add-in; if you don't have the add-in already, you can download a free trial version from our download page.

Typical Optimization with Goalseek

For simple optimization in Excel, you can use the built-in Goalseek function (Tools -> Goalseek...). This tool can solve linear optimization problems by changing the value in one cell (the independent variable) until the value of a second cell (the dependent variable) reaches a target value.

As typically used in Goalseek, the dependent variable will contain or be the result of a function including the independent variable. For example, ...

Optimization of a Simulation Result

If the dependent variable is the result of a simulation, then using Goalseek to find the solution won't work. For example, if the dependent variable is a simulation function 

=SimulationMean( A1, B1 )

when Goalseek runs it won't change the value, because the value is based on the last simulation. For this to work, we need to re-run the simulation based on different values of A1 or B1, until the dependent variable reaches the desired value.

Because the RiskAMP Add-in can be scripted from VBA, we can use VBA to perform this optimization. We've packaged a simple linear optimizer into a new Add-in that you can download and use with RiskAMP to perform this kind of analysis.

Practical Uses

Optimization of simulation results can be useful anytime you have one value driving another value: for example, in a retirement scenario, the likelihood that a retirement account will last for 20 years is dependent on a number of different variables: among them, the contribution amount prior to retirement and the withdrawal amount after retirement.

Using optimization, you might ask a question like, "what withdrawal amount should I make so that the likelihood of the portfolio lasting 20 years is at least 75%?"

Using a retirement model, you can find this value by testing and re-testing different values of the withdrawal amount until you reach a suitable risk level. Because the risk value - in this example, the likelihood that the portfolio will last for 20 years - can only be determined by running a simulation, Goalseek can't be used to find the value. But we can use VBA to simplify the process of optimization.

The Optimization Add-in

SimSeek is a very simple VBA add-in that performs linear optimization, running Monte Carlo simulations with the RiskAMP Add-in, which can make it easier to optimize particular values in your spreadsheet models.

This is the first version of the optimization Add-in, so it may still contain bugs or other errors. We can't emphasize this enough: Please save your work before running the functions contained in the SimSeek Add-in.

Download the optimization Add-in from our website here:

SimSeek.zip (30k)

This version will work with all versions of Excel supported by the RiskAMP Add-in (Excel 2000-2007). The zip archive contains two files: the optimizer (SimSeek.xla) and an example spreadsheet with instructions on how to use it. To install the optimizer, follow these steps:

  1. Download the zip file to your computer, and extract the files.

  2. From within Excel, open the Add-ins dialog:

      3. In Excel 2003, open the menu Tools -> Add-ins.

      In Excel 2007, click the Office button; click "Excel Options" at the bottom; click "Add-ins" on the left; select "Manage Excel Add-ins" at the bottom; then click "Go".

  3. Click "Browse" and find the SimSeek.xla file that you extracted from the zip archive.

  4. Click "OK" to close the dialog.

Using Optimization

The zip file, above, includes a very simple example that illustrates how to use the optimizer. In this section we'll describe how to run through that example; instructions are also included in the spreadsheet itself.

The spreadsheet includes a function that samples from a Normal distribution with a mean of 100 and standard deviation of 10. Because this is a Normal distribution, we know that the maximum value will be around 134. You can run a normal Monte Carlo simulation to check this value.

The question that we're asking in this example is, "what standard deviation is required so that the maximum value in the simulation is 200?".

To anwer this question, open the optimizer by clicking on the new toolbar button. (If you lose the toolbar, you can find it in "View" -> "Toolbars" -> "SimSeek Toolbar"). This will open the optimizer dialog. Fill in the values so that it looks like this:

If you look at the values in the spreadsheet, cell C6 is a Normal distribution; it takes a mean and standard deviation from cells C3 and C4, respectively. When a simulation is run, the maximum value is stored in cell C8.

The maximum value will be affected by the standard deviation; for larger deviations, we expect to see larger maximum values.

There are three important values in the optimizer dialog: the dependent cell, or the cell that we are trying to opimize; the target value, which is the value we want the depedent cell to reach; and the independent cell, which is the value we will be changing.

In the dialog shown here, cell C8 (the maximum value) is the dependent cell; the target value is 200; and cell C4 (the standard deviation) is the independent cell.

You can read this as "change the value of cell C4, until cell C8 reaches 200".

The "Error" value allows the optimizer to stop when it finds a value close to, but not exactly, the target value. Since the result is based on random values, it's rare that it would reach the exact value and allowing some error can greatly speed up the process.

The "Number of iterations" value specifies the number of iterations in each simulation. This should be selected for a reasonable balance of speed and specificity.

The "Number of steps (max)" value is the maximum number of simulations to run; because optimization can take a long time, and in some cases never find a suitable value, it's important to set a maximum number of steps after which it will stop.

Running the Optimizer

Once the values are set, click "Start". The optimizer will run for a while, and you'll see it running simulations, until it reaches a suitable result. When it's done, the value of the standard deviation will be about 33; and the maximum value should be somewhere near 200.

A More Useful Example

To see how this can be useful in a real example, open the example spreadsheet "Retirement Portfolio". This example was installed when you installed the RiskAMP add-in; it is available from the Start menu, under "Start" -> "All Programs" -> "RiskAMP" -> "Examples" -> "Retirement Portfolio".

If you leave all the default values in this example spreadsheet and run a normal Monte Carlo simulation, the value in cell D49 - the probability that the portfolio will last for 15 years - is about 41%.

Here we can ask the question, "what should the withdrawal amount be to increase this probability to 75%?" We can use the optimizer to find the appropriate withdrawal amount for that level of risk.

Open the optimizer dialog and set the values so they match what's seen here. Select cell D49 as the dependent cell - this is the probability that the portfolio will last 15 years. For the target value, enter 0.75 (because it's a percentage). Select cell D20 as the independent cell - this is the value that we want to change.

When you start the optimizer, it will run for a while (this can take some time), but eventually it will find a value for the withdrawal that provides a better chance that the portfolio will last 15 years - it will be around 87,500.

Limitations and Source Code

This program can only solve linear optimization problems. It can only solve for one dependent variable and change only one indepdendent variable.

This is an early version of the optimizer. It is itself not very optimal; it uses a naive search algorithm and as a result may take some time to converge on a solution. We will likely improve this over time if people find the application useful.

You are welcome to modify it yourself, if you want to improve it; it's a VBA add-in and can be edited as any other VBA project. If you come up with any useful improvements, please let us know.