SAFE TOOLBOXES® comes with hundreds of functions to perform Monte Carlo Simulation that allow you to understand the range of all possible outcomes in your model. However, when your model also involves a decision process, you may also be interested in knowing which decision leads to the best range of outcomes. This class of problem is known by the name “optimization under uncertainty” and it is a major breakthrough in solving complex business problems.
An optimization together with simulation can be achieved with the following three steps:
A basic Asset and Liability Management will be presented below to illustrate how you can apply this technique to solve a chanceconstrained optimization problem.
Suppose that a pension fund wants to minimize the chance that its assets will worth less than its liabilities over a 5year horizon. The pension fund managers must decide the amount invested in bonds or stocks at the beginning of each year to face a fixed liability at the end of each year.
The managers projected that the pension fund assets and liabilities will evolve as presented below:
 Bond’s returns follow a Vasicek model with parameters: Speed of mean reversion = 20%, Longterm interest rate = 4%, Volatility = 1%, Current interest rate = 3%.
 Stock’s returns follow a Geometric Brownian Motion model with parameters: Mean = 10%, Volatility = 20%.
So, the first step to build our simulationoptimization model is to create a Monte Carlo Simulation model for an arbitrary fixed decision. The spreadsheet below presents an implementation of such model with an equal amount invested in each asset class throughout the years:

A 
B 
C 
D 
E 
F 
G 
H 
1 
A ChanceConstrained Asset and Liability Management example 



2 








3 
Time 
0 
1 
2 
3 
4 
5 

4 
Assets 
100.0 
102.0 
109.0 
118.6 
141.1 
156.8 
<=G5+G6 
5 
Bonds 

51.7 
52.2 
55.5 
60.3 
73.0 
<=F4*G16*(1+G12) 
6 
Stocks 

50.3 
56.9 
63.1 
80.8 
83.8 
<=F4*G17*(1+G13) 
7 
Liabilities 
100.0 
106.0 
111.0 
115.0 
119.0 
123.0 

8 
Coverage ratio (Assets/Liabilities) 
1.00 
0.96 
0.98 
1.03 
1.19 
1.27 
<=G4/G7 
9 
Coverage ratio <1? 
0 
1 
1 
0 
0 
0 
<=IF(G8<1,1,0) 
10 
Coverage Ratio Indicator 
1 
<=IF(SUM(B9:G9)>0,1,0)+ B22*0 





11 








12 
Bond returns (Vasicek Model) 
3.0% 
3.4% 
2.3% 
1.9% 
1.6% 
3.5% 
<=sInterestRatesVasicek_RAND(20%,4%,1%,F12,1) 
13 
Stock returns (MGB Model) 

0.5% 
11.5% 
15.7% 
36.2% 
18.8% 
<=sRAND_Lognormal(10%,15%)1 
14 








15 
Decision variables (Proportion of bonds) 







16 
Bonds 

50% 
50% 
50% 
50% 
50% 

17 
Stocks 

50% 
50% 
50% 
50% 
50% 
<=1G16 
18 








As you may have noticed, the formula in cell B10 represents an indicator about the achievement of our goal in a given scenario/decision. The indicator will be valued with “0” if the value of the assets is greater than the liabilities during all five years and will be “1” if the value of the assets is less than the liabilities for at least one year.
Now that our Simulation Model is completed, we can go to the second step of our simulationoptimization model, where we compute the expectation of our interest variable using the “Data Table” approach.
In our ALM problem, we want to compute the expected value of our indicator to get the chance of not achieving our goal, given that we have made a particular set of decisions. So, to get an approximation of this probability based on a 1,000 sample using the Data Table approach , just follow the steps below:

A 
B 
C 
D 
E 
F 
G 
H 
14 





15 
Decision variables (Proportion of bonds) 







16 
Bonds 

50% 
50% 
50% 
50% 
50% 

17 
Stocks 

50% 
50% 
50% 
50% 
50% 
<=1G16 
18 








19 
Data Table Monte Carlo Simulation 






20 








21 
Expected value of Coverage Ratio Indicator 
60,3% 
<=AVERAGE(B25:B324) 





22 
Number to force calculation 
0.378114424 
<=RAND() 





23 








24 

1 
<=B10 





25 
1 
0 






26 
2 
1 






27 
3 
1 






… 
… 
… 
… 
… 
… 
… 
… 
… 
1020 
996 
1 






1021 
997 
0 






1022 
998 
1 






1023 
999 
0 






1024 
1000 
1 






1025 








Here we can see that holding the 5050 mix of bonds and stocks will lead to a 60.3% chance of mismatching. How to minimize this probability by changing the assets proportions is the last step in our simulation and optimization approach.
To set up the optimization model go to Solver (it should be enabled in Excel’s options to appear in the Ribbon) and fill in the form as displayed in the picture below.
One important parameter is the selection of the solver to be used. As a rule of thumb, you can try the “Generalized Nonlinear” first as it's the fastest one, but it only works with smooth functions (which is generally not the case in simulation optimization problems!). Normally the “Evolutionary Solver” will be your unique possible choice. Nevertheless, it has a disadvantage of being a very slow procedure that produces only satisfactory results (as you can never know if a global optimal was achieved).
By solving our optimization problem, we can see that the solution found reduced the chance of mismatching from 60.3% to 46.0%. This result is achieved with the following allocations:

A 
B 
C 
D 
E 
F 
G 
H 
14 





15 
Decision variables (Proportion of bonds) 







16 
Bonds 

4% 
39% 
89% 
62% 
66% 

17 
Stocks 

96% 
61% 
11% 
38% 
34% 
<=1G16 
18 








19 
Data Table Monte Carlo Simulation 






20 








21 
Expected value of Coverage Ratio Indicator 
46.0% 
<=AVERAGE(B25:B324) 





Please note that the results of our simulationoptimization model can differ a little each time that we run our model as we are using a simulation procedure and a nondeterministic optimization method to solve the problem.