Build a Complete Bond Pricer in VBA | Jack Tellington | Skillshare

Build a Complete Bond Pricer in VBA

Jack Tellington

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (32m)
    • 1. Introduction To The Bond Instrument

      2:48
    • 2. Create an Userform

      8:06
    • 3. Manage The Data Storage Management

      4:01
    • 4. Instantiate The Object

      5:10
    • 5. Implement The Formula

      6:54
    • 6. The Userform From Excel to VBA

      4:46

About This Class

In this course you will learn how to create a bond pricing tool . You will get to know important new functionalities on VBA, from create manually an unesthetic tool on a spreadsheet to structuring and optimimizing your work using the Userform. You will also learn some best practices on how to manage the data storage on VBA using the class.

Transcripts

1. Introduction To The Bond Instrument: Hello, folks. Welcome to this new bond pricing class by VB a finance dot com. Today, we're going to see how we can create a bond pricing tool in V. B. A. I will also show you how to use the feature user form As we go along in the class, you will see that learning about user forms is a very good investment because user forms can make so many tasks faster, less laborious or more consistent. First, let's have an overview of the bond principles and of the indicators we will calculate on Excel. A bond is basically a debt instrument that enables the company to raise money through a public market and given extra that we commonly call interest to the purchaser of the bond. There are several kind of bonds instruments that are exchanged on the market, each with different characteristics. Let's start with the easiest bond to calculate the zero coupon bond. This is the kind of bond that does not pay interest during the bond life. So when the bond matures, meaning it's in date expires, the investor will receive one lump sum equal to the initial investment on which is added the interest the price of a zero coupon bond yield can be calculated by using FIS formula here. F is the bond face value, which means the amount paid to the holder at maturity are the interest rate anti the number of years until maturity. There is another type of bond where the buyer gets intermediate cash flows that are actually discounted coupons. What's a coupon? We define the coupon as the annual interest rate paid on a bond expressed as a percentage of the bond price. As you can see in the formula below, we define the bond price by doing the some of the discounted to intermediate cash flows on which we had the final coupon. There are now two indicators I want to show you that are going to be very important for our bond pricing tool, the 1st 1 being the duration. The duration is simply a measurement of how long it takes for the bond to be repaid by its internal cash flows. However, in the case of a zero coupon bond, the duration does not apply since there are not any intermediate cash flows here. Most importantly, we have to remember that D derivative of R is expressed in function of the duration. Now that we have defined the duration, we can introduce a new indicator that is called the Convexity. The convicts city basically measures the degree at which the duration changes when the yield to maturity changes. The convicts city is usually used as a risk management tool because it helps to measure the amount of market risk to which a portfolio of bonds is exposed. This is the end of this class. Before jumping to the next one, make sure to do the quiz Thank you all for your attention. 2. Create an Userform: Hello, folks. Welcome back to the bond pricing class by VB a finance dot com Today we're going to see the application of the principles I just introduced you to using this awesome tool the user form. How could we define a user form on VB? A. A user form is simply a window or a dialog box that collects and processes input from the user. In a Microsoft Office program, we can add a user for man V B A by right clicking on Microsoft Excel object. We will then go to insert and click on user form. A new window is displaying now. We call it the design window on which we're going to design our user form. We can already launched the user form and displayed on the screen to see how it looks. For now, it is just a classic window on which we're going to add widgets that will make the link between our application and the user. It will therefore replace the Excel grid, which will no longer be on the main interface of our application. Regarding our project, we will add on the user form specific tools that will enable us to input parameters for the bond to price. We will also have an area where we will display the results. Not only the bond price but its sensitivity. Convexity and the coupons list until maturity. We will now start to design the user form. It will be the first step of our project. First, we're going to name the window. We will name it Bond pricer. So the name of the user form this property is the name that the user form has in the V B A code. So we will be able to access the user form and its properties by using the variable bond pricer in the V B A code associate ID to the user form and to modify the title of the window on the top. We need to modify the caption property. Now we're going to divide our user form into two sub parts. The first part will contain our parameters. We will add a type frame widget by dragging and dropping the window from the toolbox and select frame. Then we give the dimensions we wish to have in our user form. Let's try to do something nice here. This first frame will have the label name parameters. The second area is the section that will contain a results. Let's simply name it results. Here we go. Let's try to display the user form. Now we have our two frames, parameters and results. Now we will fill the space using the tool box. Still, by dragging and dropping controls that will be useful to display the results and input the parameters. So the first step is our parameters. We will add a text box that will enable the user to input the first parameters. Here we go and a label type widget that will be used to name the text box on the screen. As you can see, the label is next to the text box so the user can know that this text box will get the value that is described by the label. Then, with the inputs we will get from the text box, we will be able to calculate the bond price. So this first parameter will be the bond part of value. We will give it the name par value box like on the user form. The name property is the name our textbooks will have in the code. Here. It's not the label that we have to name. It's rather the text box. Now we can copy and paste the same thing for the other parameters. - Now we have the four parameters, although one is missing, we need to add the period is City of coupons. For this parameter, we will not use a text box. It is not really adapted since there are only four possible choices for the period is city . However, we have an amazing widget here called Combo Box for this use because it will limit the user's choice. The different choices will be added in the user form code. We also add a label with the caption Coupon period is city and a name coupon period is city box that will enable us to access it easily in the code section. We now have all the parameters. Let's try to launch it to see how it will appear from the user side. Now we will design the results part of our user form. The first step is to add three text box that will display the price, sensitivity and convexity. We will then add a list box widget that will display the list of coupons and by default the Bill Book of the Bond. Don't hesitate to copy and paste to save time. Copy the three parameters on top, but don't forget to modify the caption and the box name. So let's change the name and caption. We can now add the list box and proceed the same way we did before. Select list box from the toolbox and we drag and drop in the window and we rename it to coupon list box. Now our user form is ready. We can display and see what it will look like. Okay, let's start with the V B. A code behind the user form and that will allow us not only to get the user inputs but also display the results on the user form. So by doing this, we're going to right click here in the Project Explorer on the user form Project and View code. The first step is to implement the function that will initialize the user form and will put in the user form the values by default of our parameters in V B. A. The initialization functions of the user form will have to be written with this format, so each time we execute the user form, this function will be executed before the user form displace. We can do a quick test. I am using the function debug dot print that will execute and display a message and then I can launch the user form. I am opening the immediate window to check if the message done is displayed, which means our initialization function was successfully executed. So now we can initialize the parameters. The first step is to set a default value of the four parameters that are going to be input in the text box. We can see here the direct relationship between the property name and the variable we access in the code. Then we have to add the different choices three user will have relative to the coupon period is city. We will have The choice is no coupon monthly, quarterly, semi annually or yearly. We now have the initialization function on which we can do a quick test. This is working. As you can see, we have the default values here. When we opened the user form and in the combo box, the different choices of coupon period is city. This is the end of the class before jumping to the next one. Make sure you do the quiz. Thank you all for your attention. 3. Manage The Data Storage Management: Hello, folks. Welcome back to the bond pricing class by VB a finance dot com. Today we have to ask ourselves about the storage of the data we will get from the text box . How will we send the data to our pricer itself or to the pricing functions? One solution that we have would be to do pricing functions of this type with parameters next to each other. So we would have par value, annual discounted rate, etcetera, and we would have different functions with this long prototype. But it's not the best option in our case. One other solution would be to store all of these parameters and only one variable that we could call in the different pricing functions in a simple manner. The parameters variable would then contain all parameters and will be used in all functions . This is possible in VB, a through class module class module are modules whose role is to store parameters and functions. We will then be able to use them as variables to create a class module. We right click on the project in the project and click on class module. We will name our class module bond because it's a bond and it has different properties. So the first step is the part value. Here is what we call the variable visibility, which could be public or private. Private means it will only be accessible in this module, whereas public can be used everywhere in the application. We will send it to public because you want to have access to our variable from the user form so that we're able to populate it directly from text box values written by the user. And we will create an attributes for each parameter. So the coupon period is city has four different values. If we go back to the user form, we have defined in the initialization function that the period Isett E can take five different values. No coupon monthly, quarterly, semi annual and yearly. To store these five values, we will use an integer type variable. We will set zero for no coupon, 12 for monthly meaning there are 12 coupons per year, four for quarterly, four coupons per year, two for semiannual and one for annual one could have it to take in V. B. A is to add option explicit on the top of each module We can also add it to the user form. This option means we must declare all variables. It will avoid confusions when we try to debug the application between the variables we haven't set and those we thought we set which in fact had a slightly different name. It looks like a constraint in the code. But in fact we're saving so much time by using this when we debug the app. Now we have an object in which weaken store parameters. We are now able to code the part that will get our user form information and store it in those parameters. We said we could manipulate our class module like a variable, and that's the case here because our class module bond becomes a type in the same way as int variant or double, for example, and we will be able to declare a variable of this type. We will set it as a global variable of our module, and it will be the bond assigned to this user form. The bond type appears in the list of choices. This bond corresponds to our class module. We will have a naming issue, usually for the module class. It is better to add CLS before the name. It will avoid the confusion with the variables. So we will rename it this way. Here we go. So CLS underscore Bond is the class of a type and bond an object that need to be instance. What does that mean? It means we have to create the storage that will contain a CLS underscore bond object type . And we will create an object that contains the five parameters. This is the end of this class before jumping to the next one. Make sure you do the quiz. Thank you all for your attention. 4. Instantiate The Object: in this class, we will create the storage that will contain Ah CLS underscore bond object type and we will create an object that contains the five parameters. How can we proceed with this? We said bond equals new CLS underscore bond. And while initializing, we tell the application to create our storage space on which our bond variable will point, and now we're ready to populate this variable. Since this variable is global, it will remain set as long as the user form is displayed on the screen. So what we want is that the results are displayed in real time when we modify the value of the different parameters. For that, we can use a VB event. What is an event? It's a signal that will be issued each time a particular action is executed. Like when the user modifies a cell. This signal will be retrieved in the code and from there on action will be taken based on this event. So the events that correspond to a change in a cell are the on changed events and weaken. Simply catch them by creating functions of this type private sub par value box changed and this function will be executed Each time the part value box is modified, we can test the same way we did with the initialization debug dot print. So we have here the display of the par value change. Why? Because when we open the user form, we have defined a default value that has changed the par value box. Sophie changed the value a second time. We will see again the message par value change, Which means our function is executed each time we change text box values. Now we will simply modify the bond part value stored in the object bond each time this function is called. So each time we change the value, the variable par value db of the bond class will be modified. We also need to manage the case when we delete the text box value. We have an error here, but why? Simply because the variable par value db of the CLS bond bond type object is expected a double variable. However, we have an empty text box. It will be an empty box which is a string value. So we need to manage this case empty text box. We will simply put a condition because of the initialization code. This way our application is protected from this error type, and we can now empty the part value text box without facing any error. Now we have to do the same thing for the different perimeters for the period is city perimeter. It's a little bit different because this is not a text box, but a combo box. So we have different possible values. So we're going to do the translation between the user input value and are int type variable in the class bond. To do so, we will use a conditional structure of type select case that does exactly what we want. That is to say, different actions in function of the variable value. So we will have a syntax like this select case depending on the value of our period. Isett e coupon combo box bucks. We will have case for the different cases. The first case will be no coupon, so we will set the value to zero for the second case monthly. We will set the period is city at 12. Because we have 12 coupons in the year Case quarterly. We will set the period of city at four for four coupons per year. Case semiannual at two for two coupons per year and case annual one coupon per year. Finally, we in the select case with an end select case. So everything seems to be working great so far to verify that our bond object is executing correctly. We will simply do a sub test that will display the variable of our bond. And we will call this function from each function changes. So we're going to try to modify one of these values. We can see that our bond object is successfully modified. As you can see, we have 345 for the par value. So this is working fine. We will keep this test function in our code for now. We will use it later to do further tests. So we have now are user form our user interface, a process that transfers the data input by the user to a variable that stores this information and a variable of CLS bond type that we have created. Now we can code the pricing functions to send not only the bond price, but the convexity sensitivity and the coupons list. So we will put these functions in our CLS bond class, a class that can contain variables and also functions. It is logical to store it there because the data that will be returned are associated with a bond. This is the end of this class before jumping to the next one. Make sure you do the quiz. Thank you all for your attention. 5. Implement The Formula: Hello, folks. Welcome back to the bond pricing class by VB a finance dot com In this class, we're going to implement the formulas of each of the function properties. So to calculate the price of a bond, we need the coupons list as a quick reminder. The value of a bond is the sum of the discounted cash flows, plus the final cash flow. So we will also implement a function that will return the coupons list. We will do this through a table for each of the coupons. We would need to store three bits of information that we will display later in the user form. We would like the coupon period, the coupon price and the coupon present value. To store these three bits of information, we can create a new class that will call CLS underscore coupon. So we will do the same way as we did with a class CLS underscore bund. We will create three parameters in the CLS underscore coupon, so we can now start to implement the function that will generate the coupons list. We can start with declaring a variable that will define the coupons number to create. So the coupons number is simply defined as the maturity in years multiplied by the coupon period. ISS ity That is an integer Let's take a quick example. If the bond maturity is two years with quarterly period is city. We define it here for the quarterly period is city. We will have our period isn t variable equals to four and a maturity equals to two. So we will have eight coupons which matches with the reality. So now we're going to declare a counter that will enable us to do a four loop that will range from one to our coupons number and at each loop it aeration we will create a coupon. Each of these coupons would be stored in a table that we're going to initialize. We set the table dimension. We know the table dimension because it's the table that is aimed at storing our coupons. So we have a dimension that is equal to the coupons number we want to store in each line of the table. We will store a coupon object type. So what? Each loop around, we set up our coupon variable like we did with the bond variable in the user form. We also need to set up the coupon variable. We set the coupon variable so that the application set to storage in memory. Then we can fill in the coupon so the period number is simply the counter value. What I do here is accessing directly to one of the class parameters in the class function. So it's useless to use a prefix here because we are in the same class. So when I type the variable name, the application recognizes that I am talking about a parameter of this class. So here's the annual coupon rate multiplied by the par value and then the present value is simply the coupon amount divided by the discounted value. So one plus the annual discount rate raised to the period. Since we have a rate in years, we also want the period in years, so we will divide it by the maturity to get the period in years. So the last step now we have filled the coupon variable. We will add this variable coupon in our coupon table so we will go to the counter index. It will be the coupon we just populated. Then this function will simply return at the end of the loop the coupon table. So we have now the function that returns the coupons list. And in order to test it, we will go to the price function because we want the coupon list in the function price and we will declare a coupon variable list bar that will store the coupons list. That function will return and we will add a stop here so we will be able to see when we debug the variable value. So to execute this code, we need to call the price function in the main. So we will add in the test code, we will call the price function. Let's launch the application. Oh, we have an error here. So to debug a class, we need to go step by step because VB A does not show you where exactly you did an error. So you click on F eight and go through the function step by step Press F eight to go down. We have an error at this line. Let's put a stop on this line. Let's launch the application again. Here we have an issue because our code tries to dimension ate a table of size zero which is impossible. The table goes from 1 to 0, which is totally forbidden. This is happening because we forgot to manage one case where the period is. City is at zero, so the coupons number is equals to zero, so we need to add a condition. If the coupons number is not equal to zero, then we execute this code. Otherwise it will return our variant variable that will be empty. So let's start the application again. We arrive at the stop we set. We will add a watch by doing right. Click and click on add watch and look at the coupon list value. It is empty, as expected, because we're on a zero coupon bond. As we can see here, the coupon period is city is empty. Let's now check for a semi annual period. Is city. This time we have the coupon list that contains the period, the present value and the value. So now we can focus on implementing the pricing function, sensitivity and convexity. So the bond pricing formula is simply the sum of the annualized cash flows, plus the final cash flow, which is the nominal paid to the buyer. So we just need to loop on the different coupons and add their present value. So let's determine the first variable loop counter. Let's also declare this some variable, which is the sum of the discounted value. Then let's loop and use the bond function that enables us to get the superior line of a variant. By doing this, we will be able to loop on all the coupons. We set the Summit zero and we have the some underscore LG equal. Some underscore LG plus coupon list of our So for a quick reminder in this table, we have a coupon type object for each line on object that contains three parameters the period, the value and the discounted value. What we are interested in is the present value. So we just need to dot present value DB and the application to access the affiliated present value on this some we add the present value at part value maturity that corresponds to the some that is collected at maturity by the Bond buyer, similar to the function that calculated the coupons number. We used the discounted formula to calculate the final discounted cash flow and we returned the value. This is the end of this class before jumping to the next one. Make sure you do the quiz. Thank you all for your attention. 6. The Userform From Excel to VBA: form in Excel. Hello, folks. Welcome back to the bond pricing class by VB a finance dot com At the end of the previous, we wrote our pricing function. Now let's try to display it in our user form. To do so, we will modify the test function again that we will call it calculate. Since we call the function test at each change in the user form, the application will calculate the bond price each time there's a change in the parameters . Let's start with the user form. We have now the price with decimals. The formula for the sensibility function is very similar to the price function, so we can copy and paste to the code and modify it. The first modification to do is to multiply each cash flow by the period as a reminder. Since the period is not annualized, we're dividing it by the maturity to give the period. In years, we do the same thing for the final cash flow, and we divided by the price multiplied by one plus the annual discount rate. This sensitivity is simply the first derivative of the price that we have showed you on the power point to retrieve this formula, you can go back to the theoretical part where we explained it. The convexity formula is also very similar. So again we can copy and paste the code and simply square this value plus one which is the formula we get when we do the second derivative of the price by the interest rate. So here we do the same thing. So the maturity squared. And finally we also need this term squared. We now have the formula for Price, Convexity and Sensibility. We still need to connect the convexity and Sensibility to their respective text box. We do this in the sub calculate which will be executed each time. There's a change in parameters. So now we contest and it works perfectly. We have now the sensibility and the convexity measures. We can see it increase along with the maturity which is normal. We now need to display the coupons list. To do that. Let's go back to the user form code and in the calculate function, we're going to get the coupons list and populate it with our widget list box. We declare a variable Cuban list that will store the coupons list that we will get from our bond. We will have to loop on the bond. So we need to add a counter and we will populate our list box. This way. Add item here we at an object in the list box that corresponds to a line here and at this line, we will add, are three elements. Two things we need to do before that we're going to define the columns number of our list box, as we said, three for the bond characteristics each time we will calculate. We will also clear the coupon list box to avoid accumulating lines in our table every time we relaunched the calculation. So we're ready to add items. Modify the three column characteristics for each item. So we access the coupon list property of list box that contains the widget lines list, and we're going to have to indicate which index we want to modify. In our case, it is the last line because it's the line, we added, lest to get the last line we do coupons list box dot list count minus one. Why minus one? Because the lines are numbered. Starting from zero. We put the value one here to precisely the value that will be displayed in the first column , and we access the coupon list index counter, and the first coupon property we will display is the period. So we simply right dot period LG. We do the same things for the two other columns without forgetting to change the index to specify which column we modify now we contest. Oh, we have an error here. It's because our coupon list is an integer, so we need to manage this case. Here are par. Value does not display correctly. Let's try to understand why it's because the index starts at zero. So we need to change this. It's all good. Now we have our coupon list with the period number, the coupon value and the present value, our bond Pricer is now finished. It enables the user to input the different parameters input and to know the price, the risk measures and the coupons list. This is the end of this class. Make sure you do the last quiz. Please don't hesitate to contact us for any questions that you might have. We'll see you again in the next class. Thank you all for your attention.