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.
If you select the multitab object in Design mode, you should see the
object properties displayed in the VBA editor:
These properties are adjustable from the Workbook\_Open event.
1 2 3 4 5 6 7 8 9 10 |
Private Sub Workbook_Open() Application.EnableEvents = True With Sheet1.MultiPage1 .Font.Name = "Times New Roman" .Font.Size = 10 .Width = 170 End With End Sub |
Once the multipage control has been created, it is possible to assign some code to its Change event.
1 2 3 4 5 6 7 8 9 10 |
Private Sub MultiPage1_Change() If MultiPage1.SelectedItem.Caption = "Black Volatility" Then ThisWorkbook.Sheets("Main").Rows("25:45").Hidden = True ThisWorkbook.Sheets("Main").Rows("46:70").Hidden = False ThisWorkbook.Sheets("Main").Rows("71:120").Hidden = True Else ThisWorkbook.Sheets("Main").Rows("25:70").Hidden = True ThisWorkbook.Sheets("Main").Rows("71:120").Hidden = False End If End Sub |
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()
1 |
Public Property Get PV() As Double: End Property |
The latter needs to be implemented in the derived classes FixedLeg and FloatLeg.
1 2 3 4 5 6 |
Private mPV As Double Public Property Get ILeg_PV() As Double Call ILeg_BuildCF ILeg_PV = mPV End Property |
The ISwap interface will include a new property specific to the present value.
1 |
Public Property Get NetPresentValue() As Variant: End Property |
Then we implement this new property within the IRS class.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Public Property Get ISwap_NetPresentValue() As Variant ISwap_NetPresentValue = GetPV End Property Private Function GetPV() As Variant On Error GoTo errhandler Dim fixedPV As Variant: fixedPV = mFixedLeg.ILeg_PV Dim floatPV As Variant: floatPV = mFloatLeg.ILeg_PV Dim PV(1, 2) PV(0, 0) = "NPV" PV(0, 1) = "Fixed Leg NPV" PV(0, 2) = "Float Leg NPV" PV(1, 0) = fixedPV - floatPV PV(1, 1) = fixedPV PV(1, 2) = floatPV GetPV = PV Exit Function errhandler: GetPV = Err.Description End Function |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Public Function SwapPV(ByVal pCurve As Range, _ ByVal pTradeDate As Date, _ ByVal pStartDate As Date, _ ByVal pFixedRate As Double, _ ByVal pFixedFrequency As String, _ ByVal pFloatFrequency As String, _ ByVal pTenor As Double, _ ByVal pImethod As String) As Variant Dim leg1 As FixedLeg Dim leg2 As FloatLeg Dim swap As IRS Dim curve() As Variant Dim fixedLegPV As Double Dim floatLegPV As Double Dim im As InterpType: If pImethod = "Linear" Then im = InterpType.Linear Else im = InterpType.Cubic On Error GoTo errhandler 'RateCurve array curve = pCurve.Value 'Leg1 (FixedLeg) Initialisation and definition Set leg1 = New FixedLeg leg1.ILeg_TradeDate = pTradeDate leg1.ILeg_Curve = curve leg1.FixedRate = pFixedRate leg1.ILeg_Tenor = pTenor leg1.ILeg_Frequency = pFixedFrequency leg1.ILeg_StartDate = pStartDate leg1.ILeg_IMethod = im 'Leg2 (Float Leg) Initialisation and definition Set leg2 = New FloatLeg leg2.ILeg_TradeDate = pTradeDate leg2.ILeg_Curve = curve leg2.ILeg_Tenor = pTenor leg2.ILeg_Frequency = pFloatFrequency leg2.ILeg_StartDate = pStartDate leg2.ILeg_IMethod = im Set swap = New IRS swap.ISwap_FixedLeg = leg1 swap.ISwap_FloatLeg = leg2 SwapPV = swap.ISwap_NetPresentValue Exit Function errhandler: SwapPV = "Swap PV: " & Err.Description End Function |
This is what it should look like:
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)
(2)
With:
(3)
And:
(4)
Implementation
Since the term can be summarized as the fixed leg discounted to today’s date,
its calculation becomes straightforward.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
'************************************************************************** '************************************************************************** '********************** Black 76 VBA Swaption Premium ********************* '************************************************************************** '************************************************************************** Public Function SwaptionPremium(ByVal CallPutFlag As String, _ ByVal TradeDate As Date, _ ByVal ExpiryDate As Date, _ ByVal Tenor As Double, _ ByVal F As Double, _ ByVal Strike As Double, _ ByVal vol As Double, _ ByRef ZcCurve As Variant, _ ByVal FixedFrequency As String, _ ByVal FloatFrequency As String, _ ByVal InterpMethod As String) As Variant On Error GoTo errhandler If F = 0 Then F = SwapRate(ZcCurve, TradeDate, ExpiryDate, FixedFrequency, FloatFrequency, Tenor, InterpMethod) End If Dim T As Double: T = (ExpiryDate - TradeDate) / YB Dim CPFlag As Integer, i As Integer Dim im As InterpType: If InterpMethod = "Linear" Then im = InterpType.Linear Else im = InterpType.Cubic Dim d1 As Double: d1 = (Log(F / Strike) + 0.5 * vol * vol * T) / (vol * Sqr(T)) Dim d2 As Double: d2 = d1 - vol * Sqr(T) Dim InterpRate As Double: InterpRate = Interp(ExpiryDate, ZcCurve, im) / 100 Dim Rc As Double: Rc = RConv(TradeDate, ExpiryDate, "A5", "CONT", InterpRate) Dim leg As FixedLeg: Set leg = New FixedLeg 'RateCurve array Dim curve() As Variant: curve = ZcCurve.Value 'FixedLeg Initialisation and definition Dim fixedLegPV As Double With leg .ILeg_TradeDate = TradeDate .ILeg_Curve = curve .ILeg_Tenor = Tenor .ILeg_Frequency = FixedFrequency .ILeg_StartDate = ExpiryDate .ILeg_IMethod = im End With Dim cf: cf = leg.ILeg_CF For i = 0 To UBound(cf) fixedLegPV = fixedLegPV + F * cf(i, 2) Next i If UCase(CallPutFlag) = "CALL" Then CPFlag = 1 Else CPFlag = -1 SwaptionPremium = (fixedLegPV / F) * _ CPFlag * (F * Application.WorksheetFunction.NormSDist(CPFlag * d1) - Strike * Application.WorksheetFunction.NormSDist(CPFlag * d2)) Exit Function errhandler: SwaptionPremium = "Swaption premium error: " & Err.Description End Function |
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!
0 Comments
1 Pingback