Recently we released an update that supported running Excel’s Goalseek function within a Monte Carlo simulation. This proved very useful but led to the obvious question: how to optimize the result of a simulation - which isn’t possible using Goalseek.

It is possible to do this using some VBA code, but it’s not as easy to use as Goalseek. To make it easier to run this kind of analysis, we’ve put together a simple point-and-click linear optimizer for running simulations. The package and a more complete description are available from our library page:

Optimizing Simulation Variables

This is an early release and may contain some bugs. Please save your work before installing or running the optimizer! If you have any comments or questions about the optimizer, please let us know.

We’re pleased to announce the release of the most recent version of the RiskAMP Add-in, version 2.83. Updates are available for both the Personal Edition and the Professional Edition on our download page.

The most recent updates to the add-in include a number of performance improvements, which should speed up slow simulations, particularly on Excel 2007 and Vista. We recommend upgrading if you’re using Excel 2000/2003 or Windows XP as well.

When you have a known or desired result in a spreadsheet, but you don’t know the input values, Excel’s built-in Goalseek function can be very helpful in providing or reaching a solution. This can be true in both a flat spreadsheet and in a probability simulation.

The latest version of the RiskAMP Add-in has been updated so it’s now possible to use Goalseek within a simulation. To try it out, you should make sure you have the most recent version of the Add-in installed (version 2.80); you can download updates from our download page.

Using Goalseek within a simulation requires VBA, so you will need to know something about VBA programming. For the most part, this is fairly simple, and actually scripting Goalseek to run is fairly simple if you record a macro.

We’ve posted a very simple example of using Goalseek and a Monte Carlo simulation on our library page:

Excel 2000/2003 Version (.xls)
Excel 2007 Version (.xlsm)

You should download the file that matches your version of Excel.

This spreadsheet runs a Monte Carlo simulation from VBA, and at each iteration it runs Goalseek to solve a simple problem on the spreadsheet. To run the simulation, we use the RiskAMP VBA library (described here). At each iteration, the code runs Goalseek using this code:

Range(”D16″).GoalSeek Goal:=0, ChangingCell:=Range(”C16″)

To figure out that line, we simply recorded a macro and ran Goalseek manually. Using this technique it’s possible to run Goalseek on a spreadsheet and capture the results from a simulation like any other spreadsheet value.

To generate random values for use in a simulation, we use a version of the Mersenne Twister algorithm. This random number generator (technically a pseudo-random number generator, or PRNG) is particularly well-suited to running Monte Carlo simulations as it has a very long period, and it’s far superior to the random number function built into Excel.

Having a good random number generator can be useful outside of running simulations, however, so we’re happy to provide a simple plug-in that adds this function to Excel. The MTRand add-in adds a single function, MTRand(), which generates a random value.

MTRand Add-in Page

We’re providing the MTRand add-in free of charge, and the source code is available for use or modification. You’re welcome to use the add-in and source code for any purpose, subject to the license terms noted on the page.

We’re proud to announce the release of our update version 2.70, and the our new product version structure. Starting immediately, we’ll be offering a Personal & Learning Edition of our product that offers our powerful Monte Carlo simulation engine at a low price for academic and small business users. The Personal Edition is powerful enough for most business tasks and analysis applications.

Our Professional Edition has added performance and features for power users and large enterprises, including integrated Latin Hypercube Sampling (LHS) and the PERT distribution for modeling expert data.

Maintaining multiple versions means we can continue to provide leading analytics software at a low price, while supporting the needs of power users. Check out the features in each version to figure out which version is right for you.

Trial versions of both the Personal and Professional Edition are available on our downloads page, and our sample spreadsheets work with both versions of the add-in.

The latest update to the RiskAMP Add-in is a roll-up of several new features and bug fixes. This release includes

• The SimulationTime() function, which can be used to measure simulation running time
• A fix for an error in which Excel 2007 was not detected correctly
• Fixes for two bugs which could cause excel to crash in extreme situations

In addition, this update includes performance improvements for both Excel 2003 and Excel 2007. We recommend that users update at their convenience. The latest version can be downloaded from our website at

http://www.riskamp.com/download.php

We’re pleased to announce the release of RiskAMP version 2.50, which adds full support for the new Excel 2007, including the ribbon interface, larger spreadsheets, and new data type support.

For users of Excel 2000, 2002, XP and 2003, the new version includes bug fixes and updates, as well as changes that make the add-in faster and more efficient. We recommend that users upgrade at their convenience.

Download the latest version from our downloads page. If you’d like to find out more about Excel 2007 support, visit our Excel 2007 page.

In response to a question the other day, we created a “hair chart” - a line chart showing the results of each iteration of a simulation in a single graph. This kind of chart is interesting in that it shows the wide variance of possible outcomes in a single picture. It can be helpful in explaining how a Monte Carlo simulation works, and why it’s a powerful technique for analysis.

The chart at right was generated from our retirement portfolio example; it shows the outcomes of 100 simulation trials, overlaid with the mean (green) and 5th and 95th percentile results (yellow). The key to this chart is the SimulationValue function. This function can display the results of any cell at any step of the simulation. In constructing the results table, we use this function to find the value of the portfolio in each year at each of 100 trials.

There are some limitations to these charts. For one thing, Excel is restricted to 255 data sets (rows) per chart; this means you can only display that many trials in a single graph. Creating the tables and manipulating the charts can be time-consuming; in this case, the table contains 100 rows, and we’ve made each line black and smooth. We did this with Excel macros, and we wouldn’t recommend doing it by hand (send us an email if you need help constructing the appropriate macros).

Download the spreadsheet and run it with 100 trials. It can be especially instructive to watch the chart generate (make sure “allow screen updates” is checked when you run the simulation).

Example Spreadsheet

If you’re using Excel 2007, you know there’s a completely new interface called the “ribbon”. Unfortunately, add-ins like ours which use menus don’t play well with the ribbon. To make things easier, we’ve constructed a custom ribbon interface that can control all the menu functions for the RiskAMP Add-in.

Using this ribbon plug-in with the RiskAMP Add-in, it’s easy to run Monte Carlo simulations in Excel 2007.

Please note that this is for test purposes and early adopters only. In the future, we’ll be releasing a combined add-in that includes the RiskAMP functions and the Office 2007 UI. For now, we’re releasing the ribbon interface as a separate add-in component.

You should only install this add-in if you already have Excel 2007 and the RiskAMP add-in installed. To uninstall the ribbon interface, use Add/Remove programs from the control panel.

RiskAMP Ribbon Menu Installer (exe)
RiskAMP Ribbon Menu Installer (zip)

[Update January 18, 2006] If you’re using Excel 2007, we have a test version of the combined installer ready. Send us a note at info@riskamp.com if you’d like to try it out.

We’re pleased to announce the release of RiskAMP version 2.10, which includes significant performance improvements since the last version. Simulations are now more efficient, meaning iterations should compile and run more quickly.

In addition to performance improvements, version 2.10 includes updated examples, minor bugfixes, and a rollup of changes since the 2.00 release. All users are encouraged to upgrade to the latest version at their convenience. To upgrade, simply download the new installer from the download page.

A question came up recently about using the RiskAMP Add-in via Excel automation. When you launch Excel from another application - an Access form, for example - add-ins aren’t loaded automatically. This means that RiskAMP functions won’t work in a spreadsheet.

The same problem exists with all add-ins, and it’s just a function of how Excel works. There’s a way to solve it, but it’s sometimes a bit hard to figure out (it doesn’t help that it doesn’t make much sense).

Here’s an example of VB code that opens Excel and then activates all the add-ins. Add a project reference to the Microsoft Excel library, then use this function:

Private Sub Start_Excel()

   Dim xlAddIn As Excel.AddIn
   Dim xlApp As New Excel.Application

   xlApp.Application.Visible = True

   ' for this method to work, a workbook must be
   ' loaded with Open or Add

   xlApp.Workbooks.Add()

   ' loop over all the Add-ins that are set to
   ' "installed", and toggle the flag from True
   ' to False and back again.

   For Each xlAddIn In xlApp.AddIns
      If xlAddIn.Installed Then
         xlAddIn.Installed = False
         xlAddIn.Installed = True
      End If
   Next xlAddIn

End Sub

For this method to work, the RiskAMP Add-in must be already installed, and “checked” in the Excel add-ins menu. But basically, if the Add-in is loaded when you start Excel normally, this code should work to start it via automation.

We recently had a question about using correlation among asset classes within an investment portfolio. The functions to put this together all exist, but it can be somewhat confusing - particularly using the correlated value functions.

We’re posting a cleaned-up version of that example here. Basically, it’s a model of an investment portfolio that includes correlation among various asset classes to determine periodic returns.

This example is oversimplified. Among other things, inflation and costs are omitted. Also note that the portfolio is rebalanced in each period (weights in each period always reflect the portfolio targets). Nevertheless, this may be useful in understanding how to apply the correlated return functions.

File: Correlated Asset Portfolio Example

If you have any questions about the model or would like help undestanding the example, please send us an email.

One of the features we’ve heard requests for recently is a way to distribute spreadsheets containing RiskAMP functions to users who don’t have the add-in. In particular, it can be frustrating if simulation functions turn into errors when the spreadsheet is recalculated on someone else’s machine.

We’ve worked out a way to “lock” simulation functions, so that you can send out a completed simulation to users without the add-in. This is currently in testing, but users are welcome to download the new version to try it out. Note that these functions are not yet documented, and using them can be somewhat confusing.

Basically, “locking” simulation functions will remove all RiskAMP functions - random distributions and simulation functions - from the spreadsheet, replacing them with the values contained in the cell. The RiskAMP functions aren’t really gone, but they’re invisible, and the spreadsheet will work on any copy of Excel. If you want to re-run a simulation, you can “unlock” the simulation functions, and they’ll return to their original place in the spreadsheet.

If you save a spreadsheet with locked functions, they’ll be preserved so you can unlock them again when you re-open the spreadsheet.

In addition to making spreadsheets more portable, the locking mechanism can speed up spreadsheet processing if you have a large model and do a lot of calculations after the simulation is complete. Once locked, the RiskAMP function cells look like regular numbers (or strings, or dates, or whatever kind of data they represent).

Please note: these functions are new, and are being tested. Please don’t use them on any data you don’t have backed up. When we’re sure they’re stable, we’ll publish a release version containing locking and unlocking functions.

If you’d like to try the test version, download version 2.04 here.

We’re pleased to announce the release of RiskAMP version 2.00, which includes a major functionality update: the addition of Latin Hypercube Sampling. Latin Hypercube Sampling (LHS) is a method of sampling from random distributions which ensures that the set of generated samples is a good representation of actual variability in the distribution (wikipedia link).

In addition to the new LHS framework, RiskAMP version 2.00 includes a new unified help system, which combines the context (function-specific) help system with the manual, for an easier to use reference.

All users are encouraged to upgrade to the latest version at their convenience.  To upgrade, simply download the new installer from the download page.

In response to a user question about using VBA, we’ve prepared an example spreadsheet that uses VBA to call simulations, and more importantly, a function wrapper module that makes it much more straightforward to use the RiskAMP functions.

First, an example. This spreadsheet contains a simple model based on a single random number. VBA is used to run a simulation three times, with different numbers of trials, and capture some result values from the spreadsheet. While this model is fairly simplistic, it should demonstrate how to run simulations from VBA, and how to preserve the results from simulations.

Next, the function wrapper module. Because the RiskAMP Add-in is an XLL module (and not itself VBA), calling functions requires a somewhat complicated syntax in VBA. The wrapper module aims to simplify that by providing native functions which handle the syntax of the actual calls.

This module can be included in any spreadsheet via VBA. To include the module, save it somewhere on your disk. Then open the VBA editor and right-click on “VBA Project” (normally the top line in the tree-view at the left-hand side of the screen). From the menu, select “Import file…” and navigate to the file. You’ll then be able to use the functions in any of your own VBA code.

You can also examine the wrapper module in any text editor, to see how it works, add functions, or examine it for security.

Download files:

VBA_Example.xls 

RiskAMP_VBAModule.bas (you may need to right-click the link to save this file).

Version 1.89 is released and available for download. This version incorporates a number of bug fixes for internal issues, including string counting and number conversion. In particular, this version fixes an issue in which empty cells were incorrectly identified as having a zero (0) value.

All users of versions 1.7 and forward are encouraged to upgrade.