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 \alpha 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.

\bold{Fixed\space leg}: At every instant T_{i} in a range of dates [T_{\alpha+1}\dots T_{\beta}] the fixed leg pays out the amount \tau \times K \times N. \tau 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. \tau_{i} 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 \tau_{i} doesn’t vary.

\bold{Float\space leg}: At every instant T_{i} in a range of dates [T_{\alpha+1}\dots T_{\beta}] the fixed leg pays out the amount \tau \times L_{i} \times N, where L_{i} 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 L_{i} will be forward rates applicable to the period T{i-1}, T_{i}). 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)   \begin{equation*} NPV_{t}^{S}=NPV_{t}^{F}- NPV_{t}^{V} \end{equation*}

(2)   \begin{equation*} NPV_{t}=\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau K}- \sum_{i=\alpha +1}^{\beta }{DF_{i}\tau L_{i-1,i}} \end{equation*}

The ATM swap rate will equalize both legs, implying that NPV is 0.

Consequently we will have:

(3)   \begin{equation*} K=\frac{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau L_{i-1,i}}}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }}= \frac{PV^{F}}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }} \end{equation*}

As:

(4)   \begin{equation*} \tau L_{i-1,i}= \frac{DF_{i-1}}{DF_{i}} - 1 \end{equation*}

(3) can be expressed as:

We therefore obtain:

(5)   \begin{equation*} K=\frac{\sum_{i=\alpha +1}^{\beta }{DF_{i}\lbrack \frac{DF_{i-1}}{DF_{i}} - 1\rbrack }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }}= \frac{\sum_{i=\alpha +1}^{\beta }{DF_{i}-DF_{i-1} }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }}= \frac{DF_{\beta }-DF_{\alpha }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }} \end{equation*}

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:

VBA swap

Developer 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.

 

VBA swap

VBA Projects

 

 

 

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.

VBA swap

Swap Module



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.

 

VBA swap

VBA Options

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.

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.

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:

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.

Now if you adjust the SwapCF() function code such as:

You should get the following error:

VBA swap

Compile 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.

Let’s complete ILeg implementation:

Finally the read-only property ILeg_CF needs additional calculations:

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 [x_{1},x_{2}] is:

(6)   \begin{equation*} b=y_{1}+(a-x_{1})\frac{(y_{2}- y_{1})}{(x_{2}- x_{1})} \end{equation*}

After these changes, the SwapCF() function can be updated to return the fixed leg cashflows.

Here is what you obtain in Excel if you insert this function:

VBA swap

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.

The above code requires the integration of two additional utility functions.

Bear in mind that the float leg cashflows are calculated accordingly:

(7)   \begin{equation*} \tau L_{i-1,i}= \frac{DF_{i-1}}{DF_{i}} - 1 \end{equation*}

Now that both legs compute their respective cashflows, we can generate an IRS class implementing the following properties (below the ISwap interface):

Here is the IRS class module:

And finally, the SwapCF() should be updated to take into account the second leg:

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)   \begin{equation*}K=\frac{\sum_{i=\alpha +1}^{\beta }{DF_{i}\lbrack \frac{DF_{i-1}}{DF_{i}} - 1\rbrack }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }}= \frac{\sum_{i=\alpha +1}^{\beta }{DF_{i}-DF_{i-1} }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }}= \frac{DF_{\beta }-DF_{\alpha }}{\sum_{i=\alpha +1}^{\beta }{DF_{i}\tau }} \end{equation*}

This is tantamount to calculate the float leg present value (since the
term DF_{i}\lbrack \frac{DF_{i-1}}{DF_{i}} - 1\rbrack 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:

And implement this property in the IRS class:

Now here is the code that we use to return the ATM swap rate in an Excel VBA 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 F^{v}and F^{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.\

The code of the DisplayCF() function is defined below:

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

 

 

Facebooktwittergoogle_plusredditlinkedinmail
Upload finance computation files VBA Swaption