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
What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…
6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…
9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…
5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…