Value at Risk for Interest Rate & Cross Currency Swaps – EXCEL worksheet overview

3 mins read

VaR for IRS & CCS – About the EXCEL sheet

The Historical Simulation approach for calculating Value at Risk has become the default approach for us to work with when it comes to non liquid securities linked to liquid primary markets. Long dated cross currency swaps (CCS) and interest rate swaps (IRS) on exotic emerging and frontier market pair fall in this category.

Based on multiple client request, the Value at Risk for Interest Rate & Cross Currency Swaps – EXCEL worksheet is now available for sale. It showcases

1) How to calculate the MTM IRS and CCS portfolios, and

2) How to estimate value at risk using the historical simulation approach for the individual security as well as a portfolio

The sheet evaluates IRS & CCS portfolio VaR figures for an IRS and CCS struck on USD-PKR exchange rate. Navigate through the worksheet with the table of contents that takes the user to the relevant tabs/ data/ outputs within the file as well as to html links for the relevant courses on

Here is a quick review of what is on offer.

Historical rates tab

Data for PKR interest rates and US Treasury yield data for the years 2003 to 2009 are provided. The worksheet indexes the PKR rate data. Then it uses the indexation for selecting a given day’s data for the purpose of the calculation. The illustrated file works with index numbers 1-180, but the user may use any other set of 180 numbers:

PKR Interbank rates series
US Treasury rates series

On the tab labeled “Term Structure”, for an index number, the worksheet pulls up the rates for a given date from the Data tabs. Interpolating the respective PKR rates and USD treasuries yields gives the par term structures.

Par term structure

Bootstrapped Zero and Forward Rates

In the “Zero – PKR” & “Zero – USD” tabs, the worksheet then bootstraps the zero rate and forward rates curves from these par term structures:

Zero & Forward Rates
Bootstrapping zero and forward rates

Projected Yield Curves

Also the sheet depicts the derived curves graphically:


Using the determined PKR zero rates, the sheet prices the IRS:

Value at Risk for IRS & CCS EXCEL worksheet - IRS pricing model
IRS pricing model


Similarly using the PKR & USD zero rates the sheet determines the price for the CCS:

Value at Risk for IRS & CCS EXCEL worksheet - CCS Pricing Model
CCS pricing model

“Results – Rates & Prices” tab is a data warehouse of the interpolated par rates , derived zero and forward rates and the calculated IRS & CCS prices. The sheet uses EXCEL’s data table functionality to store data. First, the index numbers to be used in the calculation are copied and pasted (as values) to all of the seven data tables on this sheet. Then the sheet stores the determined rates and prices against the index numbers:

Data store of derived rates and prices

Interest Rate & Cross Currency Swaps VaR model using Historical Simulation

The sheet then uses the price series to calculate return series for the IRS and CCS instruments. The sum of the individual prices gives the portfolio values. Used this value, the sheet determines the return series for portfolio too. Alternatively, the sheet calculates the portfolio return series by using the notional amounts of the two deals to determine the weights of each instrument in this two-instrument portfolio. These weights, together with the individual securities return series, determine a weighted average return series for the portfolio:

Value at Risk for IRS & CCS EXCEL worksheet - Return series
Returns series

Interest Rate & Cross Currency Swaps VaR Summary Tab

The sheet plots a histogram of returns for each instrument as well as the portfolio. This may be done using EXCEL’s Data Analysis Histogram functionality or in this case, from scratch so that they change with a change in the selection for the index numbers:

Value at Risk for IRS & CCS EXCEL worksheet - VaR estimates
VaR estimates

The table output of the histogram gives the worst case loss or the VaR estimate (highlighted in green) together with the confidence level. For example, given the data selection, the daily VaR estimate for CCS is 1.12% at a confidence level of 99.44%.

For a detailed concept walk through of the Excel example check out this post.

Now available for sale – IRS, CCS VaR model using historical simulation