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

The Historical Simulation approach 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 and interest rate swaps on exotic emerging and frontier market pair fall in this category.

Based on multiple client request, the Excel sheet that showcases the model required to MTM IRS and CCS portfolios and then use the historical simulation approach to estimate value at risk for the individual security as well as a portfolio is **now available for sale**. The sheet contains a 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 financetrainingcourse.com. The sheet evaluates IRS & CCS portfolio VaR figures for an IRS and CCS struck on USD-PKR exchange rate.

Here is a quick review of what is on offer.

## Interest Rate & Cross Currency Swaps VaR – Summary Tab Review

## Interest Rate & Cross Currency Swaps VaR – Historical rates Tab Review

Data for PKR interest rates and US Treasury yield data for the years 2003 to 2009 are provided. The PKR rate data is indexed. This indexation will be used 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:

## Interest Rate & Cross Currency Swaps VaR – Historical rates Tab Review

On the tab labeled “Term Structure”, based on the index number, the rates for a given date are pulled up from the Data tabs. The par term structures are then determined by interpolating rates from the respective PKR rates and USD treasuries yields.

## Interest Rate & Cross Currency Swaps VaR – Bootstrapped Forward Rates

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

## Interest Rate & Cross Currency Swaps VaR – Projected Yield Curves

The derived curves are also depicted graphically:

## Interest Rate & Cross Currency Swaps VaR – IRS MTM Model

Using the PKR zero rates determined, the IRS is priced:

## Interest Rate & Cross Currency Swaps VaR – CCS MTM Model

Similarly using the PKR & USD zero rates the price for the CCS is determined:

“Results – Rates & Prices” tab is a data warehouse of the par rates used, the zero and forward rates derived and the calculated IRS & CCS prices. The data is stored using EXCEL’s data table functionality. 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. Accordingly, the rates and prices are then determined and recorded:

## Interest Rate & Cross Currency Swaps VaR – VaR model using Historical Simulation

The price series is then used to calculate return series for the IRS and CCS instruments. The sum of the price series gives the portfolio value and the return series for this too is determined. Alternatively, the portfolio return series is determined by using the notional amounts of the two deals to determine the weights of each instrument in this two-instrument portfolio. The weights are then used together with the individual securities return series to determine a weighted average return series for the portfolio:

## Interest Rate & Cross Currency Swaps VaR – VaR Summary Tab

The returns are then plotted as 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 we have constructed them ourselves so that they change if the selection for the index numbers is changed:

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%.

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