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

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

## CCS & IRS Value at Risk – Model Requirements

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

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

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:

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

Figure 6 IRS CCS VaR Historical Simulation – Zero Rates

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

Figure 7 IRS CCS VaR Historical Simulation – Forward Rates

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

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

Share

## Evaluating portfolio performance. A single metric to rule them all?

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

## Project Plain speak – Sense making for the financial world.

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

## Oil markets time machine. Past, present and future.

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

## Signals in the data. Oil prices, tea leaves and crude price direction.

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

## Building a supply side model for crude oil

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

## Modeling demand for liquid fuels

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