VBA and Quant finance
This article is actually a first part of an introductory course to VBA coding, given at Solvay School of Economics in Feb. 2014. The Excel sheet and VBA swap pricing code are attached.
Visual Basic for Applications (VBA) is not ” trendy”, properly speaking, in the financial industry. It is however massively used in many institutions for several reasons. People naturally come to work on Excel as soon as detailed calculations and validation are involved. If you are asked to implement swap analytics for instance, you will probably start calculating it in Excel. Many people without programming background will therefore work essentially on Excel. But this is also true for more advanced profiles, like Quants, who benchmark their calculations in Excel, and deliver DLL or XLL assemblies that will feed Excel spreadsheet or VBA apps.
After C++ and C#, Excel VBA is a very common language in a Quant activity. And VBA stands as a natural extension of Excel for libraries integration/testing.
One of VBA drawbacks and Excel more generally, is the computation time. As we will see in a second application, a Monte Carlo procedure turns out to be noncompetitive compared to other languages. A second drawback is the lack of unit testing framework.
However, Excel remains a good programming / front end environment for any trader or portfolio manager, who would like to have a full understanding of its own workbooks, and the capacity to debug it on the fly.
This course won’t be a complete guide to VBA programming but at least it will provide you the minimum notions of a proper design of VBA models.
A first application: VBA Swap Pricing
VBA stands as a language that is commonly mastered by a very eclectic range of professionals. During this course we will tackle more or less technical aspects of VBA. A first application consists in designing an object oriented framework for interest rate swap (IRS) analytics. Interest rate derivative pricing will serve as a background to illustrate technical features of VBA, but we won’t dive deep into analytical details. In particular we will put aside certain innovations in IRD pricing introduced since the credit crunch (2008) events, such as CVA, collateralization, OIS discounting, and multicurve framework.
This first application covers then a pretty vanilla instrument, fundamental though, in money and FI markets. Its implementation is a good start to explore most common notions of VBA practices.
Swap basics
The aim of this session is to build an efficient framework for the pricing of spot and forward starting swaps. The support of accruing swaps would not involve much additional complexity, but since it is not especially necessary for the rest of the course, we won’t cover it.
An IRS is a contract that exchanges payments between two legs, starting from a spot date, or from a specific date in the future. One of these legs includes fixed payments, while the other leg is indexed on a money market rate, typically Libor. Certain brokers as ICAP provide fixed rate quotations, so that this fixed rate ensures the equality of both legs in value.
The net present value of such payments schedule is rather straightforward to establish.
: At every instant
in a range of dates
the fixed leg pays out the amount
.
represents the cashflow time length, K the fixed leg rate, and N stands for the notional amount. Here we will consider that N is equal to 1.
represents the year fraction between each payment date. It is subject to variations due to holiday adjustments. For the simplicity of calculations we will consider that
doesn’t vary.
: At every instant T
in a range of dates
the fixed leg pays out the amount
, where
is an annually compounded floating rate indexed on observed Libor (in the case of the first cashflow of a spot starting IRS), or it can be calculated from a Zero Coupon curve (the series of
will be forward rates applicable to the period
). The basis of this Zc Curve is typically the same as the leg frequency.
Overall, the NPV on the trade date t, for a unitary notional will be:
(1)
(2)
The ATM swap rate will equalize both legs, implying that NPV is 0.
Consequently we will have:
(3)
As:
(4)
(3) can be expressed as:
We therefore obtain:
(5)
Inputs
This exercise requires a yield curve. Ideally this curve should follow
the float leg payment periodicity (major currencies usually have swap
markets based on 1M, 3M and 6M Libor), and discounting should rely on an
OIS bootstrapped curve. By convenience we rely on a single curve.
Implementation
Our implementation of swap analytics under VBA will support the exposition of cash flows, net present value, and par rate calculation.
We could introduce some basic features of VBA with a first “quick and dirty” implementation, but let’s put in place a structured way to do it.
First, to open the VBA editor, you might click on the “Developer” Excel ribbon:
Or alternatively you can press ALT+F11.
The project explorer displays every project available in the current Excel session. Each workbook has its own project.
By default a VBA project contains sheet code for each worksheet contained in the workbook.
Other code modules can be added to this VBA project, such as:
- “standard” modules: standard modules are generally used to provide generic functionalities used throughout the whole workbook.
- UserForms: their usage is very restricted. The code behind is limited to the useform.
- Class modules: class modules are generally depicted as an advanced feature of VBA programming, but people familiar with object oriented programming will recognize in these class modules many features of classes in other languages, such as Fields, Properties, Functions.
- Sheet code: contains events related code, private functions, and events proper to objects declared within the sheet code.
- The “ThisWorkbook” module: contains events-related code for the whole workbook.
Returning cash flows
Working on swap analytics will act as a peg for our teaching of VBA. First we will pay interest to a generic way to return swap cash flows. This step will be helpful afterwards to arrive to NPV and ATM rate calculation.
Let’s insert a “Swap” module, in which we create a SwapCF() function.

Swap Module
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Option Explicit Public Function SwapCF(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) As Variant SwapCF = 0 End Function |
In the above code, parameters are passed by value (ByVal). It means that the actual value of these arguments is passed to SwapCF(). If ByRef is used, a reference, a memory address (could be seen as a pointer) is passed to SwapCF().
For the moment, the function doesn’t return anything.
We will often use the “Option Explicit” mention at the top of any code module, page, or class module. This option can also be selected from the VBA Tools\Options menu.
If this option is ticked, the VBA compiler will produce an error at runtime, if a variable is met and is not declared priorly.
It is indeed a good reflex to explicitly declare all variables used, for several reasons. First, it reduces the number of errors while coding, because it helps to identify variables names that would have been mis-spelt.
Also declaring the data type makes the code more efficient as it enables a better allocation of memory resources.
A possible architecture could have an object orientation, with specialized objects defining legs properties. These legs would be attached to a swap object, for terminal calculations.
In the below code we define the interface of a standard leg.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Option Explicit Public Property Let Frequency(pFrequency As Integer): End Property Public Property Get Frequency() As Integer: End Property Public Property Let TradeDate(pTradeDate As Date): End Property Public Property Let TENOR(pTenor As Double): End Property Public Property Let StartDate(pStartDate As Double): End Property Public Property Let curve(pCurve As Variant): End Property Public Property Get cf() As Variant: End Property Public Property Get NbCF() As Integer: End Property Public Property Get PV() As Double: End Property Public Property Let IMethod(pImethod As InterpType): End Property Public Sub BuildCF() End Sub |
An interface is like a contract with the classes that implement it. It has to be implemented in a class module, and all of the interface’s public members should be implemented in the class module as well: procedures, methods. Otherwise it doesn’t compile.
Above you will notice a property of “InterpType” type. This is not a primitive type such as Double or Integer. We still need to create this custom type in a separate module, that we will call TypesAndGVariables.
1 2 3 4 5 6 7 8 9 |
Option Explicit Public Const YB As Double = 365.25 Public im As Integer Public Enum InterpType Linear = 0 Cubic = 1 End Enum |
The year basis is arbitrarily set to 365.25. A more generic implementation would allow users to define this year basis according to official conventions (MoneyMarket Actual/360, MoneyMarket Actual/365, BondBasis 30/360 …).
Also we define the InterpType, which is actually an Enum, used for the interpolation method definition.
Let’s create a Class module called “FixedLeg”. To implement ILeg, FixedLeg should mention:
1 |
Implements ILeg |
At the top of the module.
Once done, if you double click on the object drop down, you should see “ILeg” appearing. If you click on it, the code will include automatically the setter of the first property declared in ILeg.
1 2 3 4 |
Implements ILeg Private Property Let ILeg_Frequency(RHS As Integer) End Property |
Now if you adjust the SwapCF() function code such as:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Option Explicit Public Function SwapCF(ByVal Curve As Range, _ ByVal TradeDate As Date, _ ByVal StartDate As Date, _ ByVal FixedRate As Double, _ ByVal FixedFrequency As String, _ ByVal FloatFrequency As String, _ ByVal Tenor As Double) As Variant Dim FixedLeg As FixedLeg: Set FixedLeg = New FixedLeg End Function |
You should get the following error:
That’s because the FixedLeg class needs to implement every property defined in ILeg. So we need to complete this implementation of ILeg into FixedLeg.
Below you have the illustration of how to define the accessors to the Frequency property. The latter is accompanied by two ”backing” fields mMonthLag and mFrequency, which are used later in calculations.
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 |
Option Explicit Implements ILeg Private mMonthLag As Integer Private mFrequency As Integer Private Property Get ILeg_Frequency() As Integer ILeg_Frequency = mFrequency End Property Private Property Let ILeg_Frequency(RHS As Integer) Select Case RHS Case 1 mFrequency = 1 mMonthLag = 12 Case 2 mFrequency = 2 mMonthLag = 6 Case 4 mFrequency = 4 mMonthLag = 3 Case 12 mFrequency = 12 mMonthLag = 1 End Select End Property |
Let’s complete ILeg implementation:
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 |
Option Explicit Implements ILeg Private mMonthLag As Integer Private mFrequency As Integer Private mTenor As Double Private mCurve As Variant Private mStartDate As Date Private mTradeDate As Date Private mTenor As Double Private mFixedRate As Double Private mIMethod As InterpType Public Property Let ILeg_Tenor(RHS As Double) mTenor = RHS End Property Private Property Let ILeg_curve(RHS As Variant) mCurve = RHS End Property Public Property Let FixedRate(pFixedRate As Double) mFixedRate = pFixedRate End Property Public Property Let ILeg_TradeDate(RHS As Date) mTradeDate = RHS End Property Public Property Get ILeg_NbCF() As Integer ILeg_NbCF = mFrequency * mTenor End Property Private Property Let ILeg_IMethod(RHS As InterpType) mIMethod = RHS End Property Private Property Let ILeg_StartDate(RHS As Double) mStartDate = RHS End Property |
Finally the read-only property ILeg_CF needs additional calculations:
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 |
Private mCF() As Variant Private mPV As Double Public Property Get ILeg_CF() As Variant Call ILeg_BuildCF ILeg_CF = mCF End Property Public Sub ILeg_BuildCF() Dim i As Integer Dim date1 As Date, date2 As Date Dim rate1 As Double, rate2 As Double mPV = 0 ReDim mCF(mFrequency * mTenor - 1, 3) For i = 0 To mFrequency * mTenor - 1 'First cash flow date date1 = DateAdd("m", i * mMonthLag, mStartDate) 'Cash flow end date date2 = DateAdd("m", (i + 1) * mMonthLag, mStartDate) 'Interpolated rate at CF end date rate2 = Interp(date2, mCurve, mIMethod) 'Cash Flow Date mCF(i, 0) = date2 'Fixed Rate mCF(i, 1) = mFixedRate / mFrequency 'DF on Cash Flow Date mCF(i, 2) = 1 / ((1 + rate2) ^ ((date2 - mTradeDate) / YB)) 'Interpolated ZC rate on Cash Flow Date mCF(i, 3) = rate2 'Update leg present value mPV = mPV + (mFixedRate / mFrequency) / ((1 + rate2) ^ ((date2 - mTradeDate) / YB)) Next i End Sub |
The Interp() function will allow two kind of interpolations, linear and cubic.
The linear interpolation of a rate b corresponding to a date a in the bounds is:
(6)
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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
Public Function Interp(ByVal pX As Double, ByVal pArray As Variant, ByVal pInterpMethod As Integer) As Double If pInterpMethod = 0 Then Interp = LI(pX, pArray) Else Interp = CI(pX, pArray) End Function Public Function LI(ByVal X As Date, pArray As Variant) As Double Dim l As Integer Dim yArray: If TypeName(pArray) = "Range" Then yArray = pArray.Columns(2).Value2 Else yArray = Application.Index(pArray, 0, 2) Dim xArray: If TypeName(pArray) = "Range" Then xArray = pArray.Columns(1).Value2 Else xArray = Application.Index(pArray, 0, 1) If X <= xArray(LBound(xArray), 1) Then LI = yArray(LBound(yArray), 1): Exit Function If X >= xArray(UBound(xArray), 1) Then LI = yArray(UBound(yArray), 1): Exit Function For l = LBound(xArray) To UBound(xArray) If CDate(xArray(l, 1)) >= X Then LI = yArray(l - 1, 1) + (X - CDate(xArray(l - 1, 1))) / (CDate(xArray(l, 1)) - CDate(xArray(l - 1, 1))) * (yArray(l, 1) - yArray(l - 1, 1)) Exit Function End If Next l End Function Private Function StrTableToDbl(ByVal pArray As Variant) Dim i As Integer, j As Integer Dim newarray: newarray = pArray For i = LBound(pArray, 1) To UBound(pArray, 1) For j = LBound(pArray, 2) To UBound(pArray, 2) If TypeName(pArray(i, j)) = "String" Then newarray(i, j) = CDate(pArray(i, j)) Next j Next i StrTableToDbl = newarray End Function Private Function GetRank(ByVal pArray As Variant, ByVal pVal As Double) As Integer Dim i As Integer For i = 1 To UBound(pArray) If pArray(i, 1) >= pVal Then GetRank = i Exit For End If Next i End Function Public Function CI(ByVal X As Double, pArray As Variant) As Double Dim yArray If TypeName(pArray) = "Range" Then yArray = pArray.Columns(2).Value2 Else yArray = Application.Index(pArray, 0, 2) End If Dim xArray If TypeName(pArray) = "Range" Then xArray = pArray.Columns(1).Value2 Else xArray = StrTableToDbl(Application.Index(pArray, 0, 1)) End If Dim k As Double: k = GetRank(xArray, X) Dim i As Integer, j As Integer Dim sum As Double Dim tmp As Double If k < 2 Then k = 2 ElseIf k > UBound(xArray) - 2 Then k = UBound(xArray) - 2 End If For i = k - 1 To k + 2 sum = 1 For j = k - 1 To k + 2 If i <> j Then sum = sum * (X - xArray(j, 1)) / (xArray(i, 1) - xArray(j, 1)) Next tmp = tmp + sum * yArray(i, 1) Next CI = tmp End Function |
After these changes, the SwapCF() function can be updated to return the fixed leg cashflows.
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 |
Public Function SwapCF(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) As Variant Dim leg1 As FixedLeg: Set leg1 = New FixedLeg Dim curve() As Variant On Error GoTo errhandler 'RateCurve array curve = pCurve.Value 'Leg1 (FixedLeg) definition leg1.ILeg_TradeDate = pTradeDate leg1.FixedRate = pFixedRate leg1.ILeg_curve = curve leg1.ILeg_Tenor = pTenor leg1.ILeg_Frequency = pFixedFrequency leg1.ILeg_StartDate = pStartDate SwapCF = leg1.ILeg_CF Exit Function errhandler: SwapCF = "SwapCF: " & Err.Description End Function |
Here is what you obtain in Excel if you insert this function:

Swap cashflows
Computing the cash flows of the float leg will follow the same previous steps: first, the creation of a FloatLeg class module, sharing the same properties as FixedLeg, but differing in the implementation of the ILeg_BuildCF() procedure.
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 |
Public Property Get ILeg_CF() As Variant Call ILeg_BuildCF ILeg_CF = mCF End Property Public Sub ILeg_BuildCF () Dim i As Integer Dim date1 As Date, date2 As Date Dim rate1 As Double, rate2 As Double, effRate As Double mPV = 0 ReDim mCF(mFrequency * mTenor - 1, 4) For i = 0 To mFrequency * mTenor - 1 'First cash flow date date1 = DateAdd("m", i * mMonthLag, mStartDate) 'Cash flow end date date2 = DateAdd("m", (i + 1) * mMonthLag, mStartDate) 'Interpolated rate at CF start date rate1 = Interp(date1, mCurve, mIMethod) 'Interpolated rate at CF end date rate2 = Interp(date2, mCurve, mIMethod) 'Forward rate applicable to the CF period effRate = ForwardRateFromDF(mTradeDate, date1, date2, rate1, rate2) 'Cash Flow date mCF(i, 0) = date2 'Cash Flow mCF(i, 1) = effRate 'Interpolated ZC Rate on Cash Flow date mCF(i, 2) = rate2 'Discount Factor on Cash Flow date mCF(i, 3) = 1 / ((1 + rate2) ^ ((date2 - mTradeDate) / YB)) 'Forward Rate over CF period mCF(i, 4) = effRate 'Update leg present value mPV = mPV + effRate / ((1 + rate2) ^ ((date2 - mTradeDate) / YB)) Next i End Sub |
The above code requires the integration of two additional utility functions.
Bear in mind that the float leg cashflows are calculated accordingly:
(7)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Public Function ForwardRateFromDF(ByVal today As Date, _ ByVal date1 As Date, _ ByVal date2 As Date, _ ByVal rate1 As Double, _ ByVal rate2 As Double) As Double Dim DF1 As Double Dim DF2 As Double Dim fract1 As Double Dim fract2 As Double fract2 = ((date2 - today) / YB) fract1 = ((date1 - today) / YB) DF2 = 1 / ((1 + rate2) ^ fract2) DF1 = 1 / ((1 + rate1) ^ fract1) ForwardRateFromDF = DF1 / DF2 - 1 End Function Public Function max(ByVal a As Double, ByVal b As Double) As Double If a >= b Then max = a Else max = b End Function |
Now that both legs compute their respective cashflows, we can generate an IRS class implementing the following properties (below the ISwap interface):
1 2 3 4 5 6 |
Option Explicit Public Property Let FixedLeg(pFixedLeg As FixedLeg): End Property Public Property Let FloatLeg(pFloatLeg As FloatLeg): End Property Public Property Get CashFlows() As Variant: End Property |
Here is the IRS class module:
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 |
Option Explicit Implements ISwap Private mFixedLeg As FixedLeg Private mFloatLeg As FloatLeg Public Property Get ISwap_CashFlows() As Variant ISwap_CashFlows = GetCashFlows End Property Public Property Let ISwap_FixedLeg(RHS As FixedLeg) Set mFixedLeg = RHS End Property Public Property Let ISwap_FloatLeg(RHS As FloatLeg) Set mFloatLeg = RHS End Property Private Function GetCashFlows() As Variant On Error GoTo errhandler Dim nbFixed: nbFixed = mFixedLeg.ILeg_NbCF Dim nbFloat: nbFloat = mFloatLeg.ILeg_NbCF Dim nbPayments As Integer: nbPayments = max(nbFixed, nbFloat) ReDim CashFlows(nbPayments, 5) Dim fixedIndex As Integer, floatIndex As Integer, i As Integer Dim fixedCfArray As Variant: fixedCfArray = mFixedLeg.ILeg_CF Dim floatCfArray As Variant: floatCfArray = mFloatLeg.ILeg_CF Do If fixedCfArray(fixedIndex, 0) < floatCfArray(floatIndex, 0) Then CashFlows(i, 0) = fixedCfArray(fixedIndex, 0) CashFlows(i, 1) = fixedCfArray(fixedIndex, 1) 'Zc Rate CashFlows(i, 3) = fixedCfArray(fixedIndex, 2) fixedIndex = fixedIndex + 1 ElseIf fixedCfArray(fixedIndex, 0) > floatCfArray(floatIndex, 0) Then CashFlows(i, 0) = floatCfArray(floatIndex, 0) CashFlows(i, 2) = floatCfArray(floatIndex, 1) CashFlows(i, 3) = floatCfArray(floatIndex, 3) CashFlows(i, 4) = floatCfArray(floatIndex, 4) CashFlows(i, 5) = floatCfArray(floatIndex, 2) floatIndex = floatIndex + 1 Else CashFlows(i, 0) = fixedCfArray(fixedIndex, 0) CashFlows(i, 1) = fixedCfArray(fixedIndex, 1) CashFlows(i, 2) = floatCfArray(floatIndex, 1) CashFlows(i, 3) = floatCfArray(floatIndex, 3) CashFlows(i, 4) = floatCfArray(floatIndex, 4) CashFlows(i, 5) = floatCfArray(floatIndex, 2) fixedIndex = fixedIndex + 1 floatIndex = floatIndex + 1 End If If fixedIndex <> nbFixed & floatIndex <> nbFloat Then i = i + 1 Else Exit Do Loop While fixedIndex <> nbFixed And floatIndex <> nbFloat GetCashFlows = CashFlows Exit Function errhandler: GetCashFlows = Err.Description End Function |
And finally, the SwapCF() should be updated to take into account the second leg:
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 |
Public Function SwapCF(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) As Variant Dim leg1 As FixedLeg: Set leg1 = New FixedLeg Dim leg2 As FloatLeg: Set leg2 = New FloatLeg Dim curve() As Variant On Error GoTo errhandler 'RateCurve array curve = pCurve.Value 'Leg1 (FixedLeg) definition leg1.ILeg_TradeDate = pTradeDate leg1.FixedRate = pFixedRate leg1.ILeg_Curve = curve leg1.ILeg_Tenor = pTenor leg1.ILeg_Frequency = pFixedFrequency leg1.ILeg_StartDate = pStartDate 'Leg2 (Float Leg) definition leg2.ILeg_TradeDate = pTradeDate leg2.ILeg_Curve = curve leg2.ILeg_Tenor = pTenor leg2.ILeg_Frequency = pFloatFrequency leg2.ILeg_StartDate = pStartDate 'Swap creation Dim swap As IRS: Set swap = New IRS swap.ISwap_FixedLeg = leg1 swap.ISwap_FloatLeg = leg2 SwapCF = swap.ISwap_CashFlows Exit Function errhandler: SwapCF = "SwapCF: " & Err.Description End Function |
Calculating the ”ATM” swap rate
Previously we defined that the ATM (”ATM” for ”At The Money”, ie, we are referring to the swap fixed rate that cancels both legs) swap rate should follow this relationship:
(8)
This is tantamount to calculate the float leg present value (since the
term represents the
floating cashflows discounted), the sum of fixed leg discount factors.
We have already all this information from the previous computations.
Let’s update the ISwap interface class with the ad-hoc property:
1 |
Public Property Get ATMSwapRate() As Double: End Property |
And implement this property in the IRS class:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Function GetATMSwapRate() As Double On Error GoTo errhandler Dim fixedDF As Variant: fixedDF = mFixedLeg.ILeg_CF Dim fixedSumDF As Double Dim i As Integer For i = 0 To UBound(fixedDF) fixedSumDF = fixedSumDF + fixedDF(i, 2) Next i GetATMSwapRate = mFixedLeg.ILeg_Frequency * mFloatLeg.ILeg_PV / fixedSumDF Exit Function errhandler: GetATMSwapRate = Err.Description End Function |
Now here is the code that we use to return the ATM swap rate in an Excel VBA function:
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 |
Public Function SwapRate(ByVal pCurve As Range, _ ByVal pTradeDate As Date, _ ByVal pStartDate As Date, _ ByVal pFixedFrequency As Integer, _ ByVal pFloatFrequency As Integer, _ 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 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.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 'Swap creation Set swap = New IRS swap.ISwap_FixedLeg = leg1 swap.ISwap_FloatLeg = leg2 SwapRate = swap.ISwap_ATMSwapRate Exit Function errhandler: SwapRate = "Swap Rate: " & Err.Description End Function |
A test to ensure that the ATM rate is correctly computed would be to multiply the cash flows by their respective discount factors, in other words, calculate in Excel the present value of Fand F
, and ensure that their difference leads to 0.
Returning cash flows from VBA
As the number of cash flows can differ depending on the chosen frequency or tenor, it would be wise to adjust automatically the dimensions of the cash flows table.
To do so, the SwapCF() function can be called from a VBA code linked to a worksheet event. Worksheet_Change, for instance.\
1 2 3 4 5 |
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D10:B17")) Is Nothing Then DisplayCF End If End Sub |
The code of the DisplayCF() function is defined below:
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 |
Private Sub DisplayCF() 'Delete previous cashflows ThisWorkbook.Sheets("Main").Range("CashFlows").Value = "" 'Displays cashflows Dim cf As Variant Dim RCurve As Range: Set RCurve = ThisWorkbook.Sheets("Zero Curve").Range("ZCCurve") Dim StartDate As Date: StartDate = ThisWorkbook.Sheets("Main").Range("Expiry").Value Dim TradeDate As Date: TradeDate = ThisWorkbook.Sheets("Main").Range("TradeDate").Value Dim FixedRate As Double: FixedRate = ThisWorkbook.Sheets("Main").Range("Strike").Value Dim FixedFrq As Double: FixedFrq = ThisWorkbook.Sheets("Main").Range("FixedFrequency").Value Dim FloatFrq As Double: FloatFrq = ThisWorkbook.Sheets("Main").Range("FloatFrequency").Value Dim Tenor As Double: Tenor = ThisWorkbook.Sheets("Main").Range("Tenor").Value Dim StartRow As Integer: StartRow = ThisWorkbook.Sheets("Main").Range("CashFlows").Row Dim StartColumn As Integer: StartColumn = ThisWorkbook.Sheets("Main").Range("CashFlows").Column Dim IMMethod As String: IMMethod = ThisWorkbook.Sheets("Main").Range("IMethod").Value cf = SwapCF(RCurve, TradeDate, StartDate, FixedRate, FixedFrq, FloatFrq, Tenor, IMMethod) Dim EndRow As Integer: EndRow = StartRow + UBound(cf) Dim EndColumn As Integer: EndColumn = StartColumn + UBound(cf, 2) Dim cfRange As Range: Set cfRange = ThisWorkbook.Sheets("Main").Range( _ Cells(StartRow, StartColumn), Cells(EndRow, EndColumn)) cfRange.Name = "CashFlows" cfRange.Value = cf End Sub |
Now the cashflow schedule will adjust itself dynamically based on the
excel layer parameters (expiry tenor, cashflow frequency … ).
This is this the end of this first part on VBA swap pricing. In the next article we will explain how we expose Net present value and use the existing types to obtain swaption analytics.
References
Some great VBA references (not swap specific):
Haug E.G. (1997), The Complete Guide to Option Pricing Formulas, McGraw-Hill, New York
Webber N. (2011), Implementing Models of Financial Derivatives, Wiley, 1 edition
0 Comments
1 Pingback