Categories: Case Study Editors Choice EXCEL Examples Interest Rate Swaps Middle Office Middle Office and VaR Treasury Treasury Risk Treasury Training Value at Risk

3 mins read# Value at Risk (VaR) for Interest Rate Swap (IRS) & Cross Currency Swap (CCS)

## CCS & IRS Value at Risk – Model Requirements

## CCS & IRS Value at Risk – Setting the model up for an MTM run using historical rates

### 1) Boostrapped Zero Rates from the valuation model.

### 2) Implied forward rates from the valuation model

### 3) The IRS price series from the valuation model

This post is a continuation of our earlier post that describes the usage of historical simulation for VaR calculation of IRS and CCS (Swaps). In this session we will actually walk through the sample Excel spreadsheet built to achieve that objective. The sheet is built using a simplified IRS valuation model and Excel data tables. Once the concept is clear, you can easily extend this approach to a Cross Currency Swap by upgrading the valuation model for swaps.

As discussed earlier, the Value at Risk (VaR) calculation process is a two step process. In step one we calculate historical MTM using a series of historical rates.

**Figure 1 IRS CCS VaR Historical Simulation – Basic Flow**

In step two, we use the historical MTM series to calculate daily returns and use this return series to calculate the estimate for Value at Risk for our IRS or CCS.

**Figure 2 IRS CCS VaR Historical Simulation – VaR Model**

Our first requirement is an IRS valuation model. You can create a basic sheet that can be used to calculate the price or MTM of an interest rate swap. If you are new to this subject, please see the following posts for a quick refresher.

Pricing Interest Rate Swaps – Process. Also see MTM & Valuation of a simple Interest Rate Swap and Bootstrapping Zero and Forward Curves from treasury term structure data case studies for a step by step guide to building a basic valuation model in Excel.

Note that the model uses a given day’s rate term structure to determine the par term structure. The zero curve is bootstrapped from this par term structure which in turn is used to calculate implied forward curve. The forward rates are used to determine the floating rates for each future reset date. The difference between the present values of cash flows of the receiving leg and that of the paying leg is the value or MTM of the instrument.

The specification of the interest rate swap that we are using for this case study are as under:

**Figure 3 IRS CCS VaR Historical Simulation – IRS Specification**

Once we have this basic model in place adjustments are made to it so that it can produce a series of results based on the given interest rate term structure for a number of days (e.g. 180 days). In order to do this we insert a sheet in the EXCEL file to capture the history of base rates used. Assign an index number to each date as follows:

**Figure 4 IRS CCS VaR Historical Simulation – Setting up Rates Database**

Next, create another sheet that will be bring the term structure data in a format that can be used by the valuation model as shown below. This requires calculate of intermediate interpolation rates and converting the base rates and maturity tenors to a model readable format.

**Figure 5 IRS CCS VaR Historical Simulation – Par Rates**

With the model setup, we can now use our index numbers and the Excel vlook up function to pick up each complete term structure associated with the relevant index number and feed it to the valuation model. The feed gets executed by building and creating multiple excel data tables for:

**Figure 6 IRS CCS VaR Historical Simulation – Zero Rates**

**Figure 7 IRS CCS VaR Historical Simulation – Forward Rates**

**Figure 8 IRS CCS VaR Historical Simulation – IRS MTM Series**

Once you have the MTM series for your IRS, your job is more or less done. We take the series and use the Excel Histogram function to estimate Value at risk using the Historical Simulation approach. See Value at Risk – Calculation Refresher for background on Value at Risk and Value at Risk – Historical Simulation in Excel for background on using Data Tables for VaR in Excel.

Excel Histogram tools returns us two components. The first is table output. For the IRS in question, the worst case loss at 99.44% confidence level is -11.01% of notional value. At 67.6% confidence level the VaR estimate falls to -0.03%. of notional

**Figure 9 IRS CCS VaR Historical Simulation – VaR Estimates**

The second is the chart output which presents the same results in graphical form.

**Figure 10 IRS CCS VaR Historical Simulation – VaR Estimate – Chart Output **

Published by

October 26, 2012 3:50 am

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm

This website uses cookies.