VaR for an illustrative portfolio of Interest Rate & Cross Currency Swaps - EXCEL Example

In stock
Product Details
Course Type: EXCEL download
Files Included: 1 worksheet

About the Course

This course consists of an EXCEL file which illustrates the calculation of VaR for a portfolio of interest rate and cross currency swaps using the historical simulation approach and a PDF file which explains the workings of the EXECL worksheet.

The course extends from the basic pricing course for interest rate and cross currency swaps. The purpose is to obtain a series of IRS & CCS prices that would then be used in the calculation of individual & portfolio returns and hence Value at Risk (VAR) for the individual instruments & portfolio using the historical simulation approach.

EXCEL’s Data Table functionality is used for inputting the term structure for a given day into the model, with resulting zero rates, forward rates and IRS prices for that day are then automatically calculated and stored. The process is repeated for 180 days to arrive at a 180-day price series.

Returns for this series are calculated as inputs to build histograms of returns which are then used to determine the daily historical simulation VAR estimates.

The Excel spreadsheet includes

  1. VaR estimates for an IRS transaction using the historical simulation approach
  2. VaR estimates for a CCS transaction using the historical simulation approach
  3. VaR estimates for a portfolio comprising of an IRS & CCS transaction using the historical simulation approach
  4. MTM pricing models for sample IRS & CCS transactions

Learning Objectives

After taking this course you will be able to:

  • Derive zero and forward rates
  • Derive IRS and CCS prices
  • Store the results using EXCEL’s Data Table functionality
  • Calculate the return series from the resulting prices
  • Generate a histogram of returns from which VaR estimates can be derived for individual instruments as well as for a portfolio of instruments


The student should be familiar with derivative products and basic Value at Risk approaches, and be comfortable with basic mathematics, statistics, probability and EXCEL.

In order to understand the worksheet, it is important to be comfortable with the concepts behind bootstrapping and the derivation of zero & forward rates as well as the pricing of IRS and CCS instruments.

In particular the student must be familiar with the following:

  • Linear interpolation to determine the rates of specific tenors from rates of adjacent tenors
  • How to price a fixed income bond using zero rates
  • The price of a par bond at time 0 (This is equivalent to the bond's face value).
  • The formula of the relationship between forward and spot (zero) rates
  • The pricing of a fixed for floating interest rate swap and a cross currency swap
  • A basic understanding of Value at Risk & the historical simulation approach

The student should also know how to use and interpret EXCEL's VLOOKUP, HLOOKUP and DATA TABLE functions.

Target Audience

The course is aimed at professionals who deal with pricing, valuation and risk issues related to derivative and structured products.

Save this product for later