Using the VaR | Expected Shortfall Tool

Simulation Master Tutorials > Using the VaR | Expected Shortfall Tool

The VaR | Expected Shortfall tool easily calculates these risk measures for a previously run simulation.  This tutorial will cover how to use the tool as well as how to calculate these measures directly in a worksheet using the VALUEATRISK and EXPSHORTFALL worksheet functions.  To learn more about value at risk and expected shortfall, refer to these blog articles:

Value at risk

Expected shortfall

Before Using the Tool

The first step is running a simulation on a model that computes portfolio gains and losses for a given time horizon (daily, monthly, etc.).  A simple portfolio model is shown below.

Now we run a simulation using Simulation Master with portfolio gains/losses (cell B5) as the output.

Running the VaR | Expected Shortfall Tool

Once the simulation has been run, we can run the tool.  Click the Special Tools button on the ribbon, and then click the VaR | Expected Shortfall button.

The Var | Expected Shortfall form will appear.  We'll select the simulation that was run on the model in the drop-down box.  The simulation data sheet is named "Simulation Data".  We want to calculate at the 95% confidence level, so we'll enter 95 in the confidence level box.

Now click the Run button to calculate.  The results will appear on the form.

A report of the results can be created by clicking the Create Report button.

Calculation Details

Value at Risk

The first step is to find value at risk. The VaR threshold is determined by the percentile of 100 – confidence level. In the example, we used a 95% confidence level. So VaR is determined at the 100 – 95 = 5th percentile of the simulation outcomes.

Maximum Loss

The maximum loss is simply the smallest outcome in the simulation. When combined with VaR, minimum outcome defines the range of possible outcomes that are past the VaR threshold.

Expected Shortfall

Expected shortfall is the expected value of all outcomes beyond the VaR threshold. That is, given that loss has exceeded VaR, expected shortfall is the expected value of the loss.

Expected shortfall = mean of outcomes to the left of VaR threshold.

Sign Convention

When reporting results, Simulation Master uses the convention that a positive value is a loss, and a negative value is a gain.

VALUEATRISK and EXPSHORTFALL Functions

Instead of using the VaR | Expected Shortfall tool, you can calculate VaR and expected shortfall directly in a worksheet.

VALUEATRISK Function

The VALUEATRISK function will calculate value at risk in a worksheet cell.  The function is entered as follows:

=VALUEATRISK("simulation name", confidence level)

Where simulation name is the worksheet containing the simulation data, and confidence level is entered as a percentage.

For our example, VALUEATRISK is entered in cell J1.

EXPSHORTFALL Function

The EXPSHORTFALL function will calculate expected shortfall in a worksheet cell.  The function is entered as follows:

=EXPSHORTFALL ("simulation name", confidence level)

Where simulation name is the worksheet containing the simulation data, and confidence level is entered as a percentage.

For our example, EXPSHORTFALL is entered in cell K1.