As 2016 started, we wish you and your family a great year ahead. Happy New Year!

This post is a continuation of our discussion on swap and VBA swaption pricing, and a conclusion of this topic.

In a previous article, we introduced a VBA object oriented architecture to price a swap. Here we expose how to compute the Swap NPV, along with Swaption pricing (in a Black&Scholes framework). Again the Excel sheet and VBA code are attached at the end of the article.

First let’s make a short digression about worksheet events that enable the dynamic display of our different analytics.

User Interface (UI) controls

In the Developer tab, the Multitab object is accessible through Insert>Toolbox.

You might wonder about the difference between Form controls and ActiveX controls. The former are Excel native whereas ActiveX controls are loaded separately. Forms controls are generally simpler in terms of usage. ActiveX controls are useful when more flexible design is required with regards to Form controls.

VBA controls

VBA controls

 

 

 

 

 

 

 

 

ActiveX controls

ActiveX controls

 

 

 

 

 

 

 

 

 

 

If you select the multitab object in Design mode, you should see the
object properties displayed in the VBA editor:

 

 

Properties window

Properties window

 

 

 

 

 

 

 

 

 

 

 

These properties are adjustable from the Workbook\_Open event.

 

 

Once the multipage control has been created, it is possible to assign some code to its Change event.

 

Returning the swap NPV

Let’s get back to our swap computations.

Returning the swap NPV becomes straightforward once the cashflows have been computed.

However the ILeg interface should now mention an additional property PV()

 

The latter needs to be implemented in the derived classes FixedLeg and FloatLeg.

 

 

The ISwap interface will include a new property specific to the present value.

 

 

Then we implement this new property within the IRS class.

 

 

It is then possible to create a user defined function in the swap module that retrieves the swap net present value from the IRS – ISwap\_NetPresentValue property.

 

 

This is what it should look like:

Swap NPV

Swap NPV

 

 

VBA swaption pricing

Definitions and formulas

We can easily extend the previous output to obtain the premium of a swaption in a Black Scholes framework.

Swaptions can be categorized between:

Payer swaptions: the holder of a payer swaption has the right, but not the obligation, to pay the fixed rate of a forward IRS, and receive the floating rate.

Receiver swaptions: reversely the holder of a receiver swaption will have the right to receive the fixed rate of the forward IRS, and pay the floating leg.

Swaptions are often priced using the SABR model, and a calibrated volatility cube (see Pat Hagan & Michael Konikov paper “Interest Rate Volatility Cube: Construction and Use”, 2004.

We will show more simply how to price this swaption in a Black 76 framework.

Here are the formulas :

(1)   \begin{equation*} c= \lbrack \frac{1-\frac{1}{(1+\frac{F}{m})^{t_{1} m}}}{F}\rbrack e^{-rT} \lbrack F N(d_{1})-X N(d_{2})\rbrack \end{equation*}

(2)   \begin{equation*} p= \lbrack \frac{1-\frac{1}{(1+\frac{F}{m})^{t_{1} m}}}{F}\rbrack e^{-rT} \lbrack X N(-d_{2})-F N(-d_{1})\rbrack \end{equation*}

With:

(3)   \begin{equation*} d_{1}= \frac{\ln (F/X)+(\frac{\sigma ^{2}}{2})T}{\sigma \sqrt{T}} \end{equation*}

And:

(4)   \begin{equation*} d_{2}=d_{1}- \sigma \sqrt{T} \end{equation*}

Implementation

Since the term \lbrack 1-\frac{1}{(1+\frac{F}{m})^{t_{1} m}}\rbrack e^{-rT} can be summarized as the fixed leg discounted to today’s date,
its calculation becomes straightforward.

We hope that you enjoyed these two articles that clarify interest rate swap pricing (in a simple context) and establish a reusable object oriented architecture for further IRD analytics (caps & floors, swaptions) or index based leg products such as floating rate notes.

And again we wish you a great 2016 year!

Facebooktwittergoogle_plusredditlinkedinmail
Upload finance computation files VBA Swaption