Bonds Part IV: Bond Calculator Code

In this post, the fourth in my series on bonds, I’ll discuss the VBA program I used to build my first bond pricing model. I used the model in a previous post to demonstrate how bond pricing works, and later I’ll modify it to analyze medieval bonds issued by the Republic of Venice.

The program I’ll discuss here has three goals: (1) take basic information from users about a bond, (2) calculate the bond price and its cash flows, and (3) generate a spreadsheet with the results. Likewise, my code has three main parts. There is what I call (1) its main body, which initiates or calls the other two parts and includes bond calculations; there is (2) the formatting function, which prepares the spreadsheet; and finally there is (3) the userform, which generates a form in which users can type their bond information.

My commentary will follow the main body from top to bottom, with detours to explain the function and the userform. I’ll take time to introduce programming concepts I haven’t used before, and, to avoid being repetitious, I’ll include links to discussions in previous posts on concepts I’ve already introduced elsewhere. To end this post, I’m only going to write a quick summary of the program and the new concepts I introduced here and leave more general, concluding comments until the end of the next post, which will discuss a modified version of this post’s program.

The Code

As I’ve done in previous posts on code, I’m going to begin with the whole code itself, copied from Excel Developer and pasted directly here. I’m also going to include a link with which readers can download my original Excel file with that code. I’m forced to do this because of my userform. I didn’t have enough time to figure out how to generate it with only code, so I’m forced to include the file for readers to download so that they don’t have to build the userform for themselves.

To download the Excel file, click its file name: Bond Pricing Through Coupons

A color-coded version of the code appears below. The main body is in red, the function in green, and the userform in purple.

Sub bond_price_cash_flows()

Formatting

Dim FaceValue, Coupon, Maturity, YTM
Dim CashFlow, CouponValue, MaturityValue, CashFlowValue
Dim Rate, Row, Price, MarketRate, Column
Dim CashFlowCounter, CashFlowRow, CashFlowColumn
Dim ShowCashFlows

frmBondInfo.Show

FaceValue = Cells(3, 2)
Coupon = Cells(4, 2)
Maturity = Cells(5, 2)
YTM = Cells(6, 2)

If Cells(3, 4).Value = “Cash Flows” Then
ShowCashFlows = 1
End If

CashFlowRow = 4
CashFlowColumn = 4

CouponValue = FaceValue * Coupon

For CashFlowCounter = 1 To Maturity

If CashFlowColumn > 13 Then
CashFlowRow = CashFlowRow + 1
CashFlowColumn = 4

End If

Select Case CashFlowCounter
Case 1 To (Maturity – 1)
CashFlowValue = (CouponValue / ((1 + YTM) ^ CashFlowCounter))
Case Maturity
CashFlowValue = ((CouponValue + FaceValue) / ((1 + YTM) ^ CashFlowCounter))
End Select

If ShowCashFlows = 1 Then
Cells(CashFlowColumn, CashFlowRow).Value = CashFlowValue
Cells(CashFlowColumn, CashFlowRow).NumberFormat = “$0.00”
End If

Price = Price + CashFlowValue

CashFlowColumn = CashFlowColumn + 1

Next CashFlowCounter

Cells(9, 2).Value = Price
Cells(9, 2).NumberFormat = “$0.00”

End Sub

Here’s the function’s code.

Function Formatting()

Range(“a1:h100”).Select
Selection.Clear

Columns(“A:A”).ColumnWidth = 17

Cells(1, 1).Select
ActiveCell.FormulaR1C1 = “Bond pricing through cash flows”
With Selection.Font
.Size = 15
.Bold = True
End With

Cells(3, 1).Select
ActiveCell.FormulaR1C1 = “Face Value”
Cells(4, 1).Select
ActiveCell.FormulaR1C1 = “Coupon”
Cells(5, 1).Select
ActiveCell.FormulaR1C1 = “Maturity”</span
Cells(6, 1).Select
ActiveCell.FormulaR1C1 = “Yield to Maturity”
Cells(9, 1).Select
ActiveCell.FormulaR1C1 = “Price”

End Function

Next comes the userform code.

Private Sub chkCashFlows_Click()

If chkCashFlows.Value = True Then
Cells(3, 4).Select
ActiveCell.FormulaR1C1 = “Cash Flows”
End If

End Sub

Private Sub cmdCalculate_Click()

Cells(3, 2) = txtFaceValue.Text
Cells(4, 2) = txtCoupon.Text * 0.01
Cells(5, 2) = txtMaturity.Text
Cells(6, 2) = txtYTM * 0.01

Cells(3, 2).NumberFormat = “$0.00”
Cells(4, 2).NumberFormat = “0.0%”
Cells(6, 2).NumberFormat = “0.0%”

Unload Me

End Sub

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub cmdReset_Click()

txtFaceValue.Text = “”
txtCoupon.Text = “”
txtMaturity.Text = “”
txtYTM.Text = “”

End Sub

Private Sub txtFaceValue_Change()

End Sub

Private Sub UserForm_Click()

End Sub

The Formatting Function

Beginning at the top of the main body of the program, my first line of code after the sub command is the name of my function, Formatting, which executes the function. I like to refer to this as the main body calling the function.

I don’t have much to write about the function itself. It’s basically just another VBA program nested within the main body of my program. When the function’s code ends, Excel jumps back to the main body. The only new thing about the function is in its first line. While the main body begins with the word sub, as in Sub bond_price_cash_flows(), the formatting function begins with the word function, as in Function Formatting().

The rest of the formatting function includes code I’ve covered before in a previous blog series, so I’ll only quickly summarize the function. It begins with a range command that clears a section of cells for the spreadsheet. Then, a columns command increases the width of the first column (column A) to accommodate text that will print in that column. The rest of the function uses cells commands to print the title and headings for the spreadsheet.

As simple as my function is, it accomplishes something important. By removing distracting code from the main body and placing that code in a separate function, I can make my program more modular. In my previous blog series, I mentioned that I wanted to remove elements of code into functions, so that my programs will be easier to follow and won’t look so cluttered. Adding this formatting function did exactly that. To go a step further, I’m going to remove the calculations from the Venetian bond program I’ll write and put them in  their own functions.

Generating a UserForm

After calling the function, the next section of code in the main body lists variables (which I discussed here) and calls the userform. The purpose of the userform, as I explained earlier, is to generate an input box in the middle of the spreadsheet for users to input bond information. Later, the program will use that basic information to calculate bond values.

The code that calls the form, frmBondInfo.Show, has two parts. First is the name of the form, followed by the extension .show. Note the descriptive name. It begins with the prefix frm, which indicates that it is a userform, and the rest of the name, BondInfo, indicates that the form is about bond information. This is following a standard naming practice I use throughout my program.

There’s more to a userform, of course, than its name. It’s necessary to build it in Excel’s Developer window, which is an easy process. First generate a blank form and, then, modify it. I’m going to go through this process so readers understand how it works and learn how to create userforms for themselves.

Generating a blank form takes one step. As my screenshot shows, click Insert and userform in Developer.

This will cause a blank userform and its toolbox to appear near the top-left of the screen.

Clicking and dragging those small white boxes at the form’s edge allows one to change its size and shape. Adding text and other items requires clicking on the icons in the toolbox on the left and, then, clicking on the area at which one wants them in the form. The end result will be something like the form I built for my VBA program, which is a bit larger than the default one I generated in the screenshot, and obviously mine includes many more items on it.

Adding Items to the Form

My userform has four kinds of objects on it. I’ve added four label boxes on the left, one for each textbox on the right. Below those is a checkbox for cash flows and, at the bottom, are three buttons. Let me illustrate how to add them with an example. Going back to the blank userform, I’m going to add a label box by clicking the letter A icon on the top left of the toolbox. Then, I click at a random spot on the userform, and a label appears.

I can modify the box now that it’s on the form. I can click and drag its edges to change its size or location. When moving boxes, incidentally, it’s useful to use the grid of dots on the form to orient the box. To change its text, I click on the text itself, delete Label1, and type something else. The problem with this method is that it’s too easy to accidentally jump to the form’s code screen. Double-clicking anywhere on the form will cause this to happen. When this happens, press the F7 key or double-click the name of the userform at the top-left of the Developer window to go back to the graphical interface for my blank userform.

The Properties Window

The best way I’ve found to change text and other basics of userform items is through the properties window, which appears at the bottom left of Developer. In the screenshot below, I’ve enlarged the window so that all of its properties appear. These would be all of the properties for the label box I added earlier, the name of which appears at the top of the window.

Through this window, users can change the basic characteristics of items by clicking on the box to the right of a property’s name and changing the values there. For instance, if I wanted to change the text in the label box without having to click on the box itself, I can change the text next to the caption property. Right now, it’s Label1, but I could easily type anything in its place.

In most cases, properties are self-explanatory. Font obviously refers to the font of the text of the object. TextAlign refers to the alignment of that text. Height and Width refer to the height and width of the object in pixels.

Some properties, on the other hand, aren’t so clear from their names. Excel Help and a quick Google search are good guides for these. I want to focus on TabIndex, which controls the order in which users can tab through a userform. When a VBA program executes the userform, the cursor appears at the item with the lowest tabindex number (actually of the items with which users can interact. The cursor never goes into a label box, so their tabindex numbers don’t count.)

When the user presses the tab or enter key, the cursor jumps to the next highest tabindex item. In my userform, the cursor begins on the topmost textbox with a tabindex of one. Press tab, and the cursor goes down to the next textbox with a tabindex of two, and so on. When it reaches the last tab-indexed item, the cursor jumps back to the label box with an index of one. In this way, users can cycle through the entire userform without having to use a mouse.

The important thing to keep in mind is that adding items to a userform sometimes requires renumbering the tab order. When I added the checkbox to my userbox, I had already added the rest of the items. This meant that users needed to tab through all of the other items before the cursor would jump back to the checkbox, which is in the middle of those items. To fix this problem, I changed the tabindex numbers so that the checkbox’s number is in the middle.

UserForm Items

Earlier in this post, I commented that my userform has four types of items: labels, text boxes, a check box, and buttons. To finish my introduction to building userforms, I’m going to describe quickly each of these components. Here’s another screenshot of the form.

I already explained how to add a label box. My form has four of those boxes, which I use to indicate which textbox to their right takes the face value, coupon, maturity, and yield to maturity of the bond. The only change I made to the default label box is to align text to the right and change the captions.

Textboxes are the next kind of item on my form. They allow users to type input into a userform. I added and customized these boxes just as I did with my labels. The thing I did differently was to make sure to give each of the textboxes a descriptive name. I follow what seems to be the standard method in my userbox research. Textboxes start with the prefix txt, followed by a descriptive name for that specific box. Thus for the box that’s for the face value, the name is txtFaceValue; for coupons, txtCoupon; for maturity, txtMaturity; and for the yield to maturity, txtYTM. These names are far more descriptive and useful in code than the default names they would have had: TextBox1, TextBox2, TextBox3, and TexBox4.

The third kind of item on my userform is the checkbox I mentioned earlier. Like other checkboxes that in Excel and throughout the internet, a checkmark appears in my checkbox when a user clicks it. This will make my program print the cash flows of the bond. I’ll explain how this works later, when I’m discussing my userform’s code. The checkbox’s name includes the prefix chk and CashFlows.

The fourth and last item is the command button. This is the button that users can click to make my userform to do something. I have three such buttons at the bottom of my form: Calculate, Reset, and Cancel. The names indicate exactly what they do. Calculate makes VBA calculate the value of the bond. Reset clears the form. Cancel closes the UserForm. The names for these items begin with the prefix cmd, followed by a descriptive name.

The UserForm Itself

I just realized that I have one more kind of item to mention before moving on to the userform’s code. That’s the userform itself. By clicking on it, and none of the items on it, one can access its properties window, which allowed me to change its caption and name properties. I changed its caption from the default UserForm1 to the more descriptive name Bond Information. As a result, Bond Information appears at the top of the form, indicating what kind of information it wants.

To make it easier to work with the userform in my code, I changed its name to fit the format of my other item names. Because it’s a userform, I begin with the prefix frm. Then I give it the descriptive name BondInfo, which makes it appear as frmBondInfo in my code.

On to the Code

Now that I’ve described how to build a basic userform, I can move on to its code. To make a form do anything, it’s essential to write code for it. If I want my reset button, for instance, to clear the form’s fields, I need to write code to make it do that. Otherwise, nothing will happen when the user clicks the button. Without any code, at best the user can click the red X at the top right to close the form.

Adding code is a simple process. One needs to do it from the code screen I mentioned earlier. The best way to do this is to double-click the item for which you want to write code, which will make Developer generate the opening and closing lines of code for it. Code always begins with Private Sub and ends with End Sub. From this point on, incidentally, I’m going to refer to these kinds of short chunks of userform code as subroutines. To me, the name subroutine suggests that these items are smaller subprograms within a larger program.

Going back to my userform,

Here is the first subroutine at the top of the code screen.

Private Sub chkCashFlows_Click()

If chkCashFlows.Value = True Then
Cells(3, 4).Select
ActiveCell.FormulaR1C1 = “Cash Flows”
End If

End Sub

Readers should recognize immediately to which userform item this code applies. The name in the first line of code, chkCashFlows, indicates that this is for my checkbox. If readers look at the entire code screen by itself, the names should make immediately clear which subroutine goes with which userform item.

For the checkbox subroutine, it contains only an if-command. If the user has clicked on the checkbox, this would cause the value of the box itself to become true, chkCashFlows.Value = True. Then, the cells-command will print the words Cash Flows in a designated cell. Later in the program, those words in that cell will be a signal for the loop in the main body of the program to print the cash flows on the spreadsheet.

Command Buttons

The rest of my userform code is for command buttons, beginning with Calculate. When the user clicks this button, the form prints basic bond information, including the face value, coupon, maturity, and YTM, on the spreadsheet, after which Excel jumps back to the main body of the program to finish its calculations and print the rest of its results.

Here’s the Calculate button’s code.

Private Sub cmdCalculate_Click()

Cells(3, 2) = txtFaceValue.Text
Cells(4, 2) = txtCoupon.Text * 0.01

Cells(5, 2) = txtMaturity.Text
Cells(6, 2) = txtYTM * 0.01

Cells(3, 2).NumberFormat = “$0.00”
Cells(4, 2).NumberFormat = “0.0%”
Cells(6, 2).NumberFormat = “0.0%”

Unload Me

End Sub

The first set of four cells-statements print bond values on the spreadsheet. The names of the userform items indicate where the values are. For the first line, for instance, cell B3 will display the value the user typed into txtFaceValue, which is the face value of the bond. I added the suffix .Text to indicate that the code is referring to a textbox, not to a variable. Two of these lines also multiply their values by 0.01, which converts their numbers into decimals. Later I can calculate percentages by multiplying bond values by these decimals.

The next three cells-commands change the format of the numbers on the spreadsheet. They make the face value appear as a dollar value and the coupon and yield to maturity values appear as percentages.

The last line of code in this subroutine is Unload Me, which closes the userform so that Excel can go back to the main body of the program. As well, unload me is the only line of code for the cancel button’s subroutine. The purpose of the cancel button is to close the userform without inputting any information, so that’s the only line of code the button needs to operate correctly.

The last command button on my userform is the Reset button, which clears all of the input boxes on the form. I do this by writing empty spaces into all four boxes.

txtFaceValue.Text = “”
txtCoupon.Text = “”
txtMaturity.Text = “”
txtYTM.Text = “”

Please note that I don’t end this button’s code with an unload me command, because I don’t want the reset button to close the userform. When users click that button, they only clear the input box fields, after which they can input new data into the form. If they want to close the form, they need to click the cancel button.

Back to The Main Body

After the userform closes, it’s back to the main body to prepare for the bond calculations. I want to mention three short sections of code in this preparatory section. The first has four lines that assign values to variables. They take the numbers the user input into the userform, which were printed in cells 3-2, 4-2, 5-2, and 6-2, and assign them to their requisite variables. When the user, for instance, typed the face value of the bond into the form, the form printed that value in cell C2, which becomes the value of the variable FaceValue.

FaceValue = Cells(3, 2)
Coupon = Cells(4, 2)
Maturity = Cells(5, 2)
YTM = Cells(6, 2)

The second section sets up the program in case the user chose to have cash flows printed on the spreadsheet, which he or she indicated earlier by clicking the checkbox in the userform. If he or she clicked it, the if-statement I added to the program will assign a number to the variable ShowCashFlows, which later will be a signal to print the cash flows as the program is calculating them.

If Cells(3, 4).Value = “Cash Flows” Then
ShowCashFlows = 1
End If

The final section is a simple line of code that calculates the dollar value of the bond’s coupon: CouponValue = FaceValue * Coupon. Earlier, the user input the face value and coupon of the bond into the userform, and now the program multiplies those values together to determine the bond’s dollar value. The result, to be stored in CouponValue, will be important for bond calculations just a few lines further down the program.

The Main Loop

Most of the rest of the main body is dedicated to calculating bond values with the help of a for-loop (for a refresher on for-loops, please refer to this previous post). The first line of the loop is For CashFlowCounter = 1 To Maturity, which makes the loop repeat until it reaches the last cash flow, when CashFlowCounter equals the Maturity variable. Just before the end of the loop, after it has finished its calculations, there is a line of code that increases the CashFlowCounter variable by one. Each time, then, the loop gets closer by one to the value of Maturity.

The next line of code in the loop is an if-statement that organizes how my program prints cash flows on the spreadsheet. Originally, I wrote the program so that it would print the first flow at cell D3 and then work its way down, cell-by-cell. I quickly noticed a problem with this format. Once the program reached cash flow twenty-one, users would need to scroll down to view the entire list of flows. To make the spreadsheet easier to read, I added the if-statement so that cash flows would print in rows of ten, as the screenshot below shows.

This is the if-statement that helps organize these cash flows.

If CashFlowColumn > 13 Then
CashFlowRow = CashFlowRow + 1
CashFlowColumn = 4
End If

When the loop prints a cash flow in row 13, the if-statement makes the next loop print a fresh row of cash flow numbers, starting at the top right, one row to the right and in the fourth column from the top of the spreadsheet. As the screenshot shows, this breaks up cash flows neatly into rows of ten.

The next section of code is the most important part of the loop, if not of the whole program. The section calculates the value of each individual cash flow and keeps a running total that will become, by the end of the last loop, the total price of the bond. To do this, I wrote code that calculates two versions of the following equation, which I discussed in my bond pricing post:This equation calculates the present or today’s value of a coupon or final cash flow of a bond, which the bondholder will receive at a future date. Market rates influence what this price will be, so the equation includes those market rates in the price of the bond. More specifically, it divides the value of the cash flow by a modified version of the interest rate the bond fetches on the market. For a fuller explanation of this, please refer back to that bond pricing post.

To incorporate the equation into my VBA program, I needed to transcribe it into a format VBA can read. Actually, I have two versions of the equation: the first for case one when I need to calculate the present value of a coupon, and the second for case two when I need the present value for the final cash flow, which includes a coupon and a face value.

Select Case CashFlowCounter
Case 1 To (Maturity – 1)
CashFlowValue = (CouponValue / ((1 + YTM) ^ CashFlowCounter))
Case Maturity
CashFlowValue = ((CouponValue + FaceValue) / ((1 + YTM) ^ CashFlowCounter))
End Select

To make things clearer, let me isolate the equation in case one: CouponValue / ((1 + YTM) ^ CashFlowCounter). I’ve italicized the denominator. In this case, cash flow value is stored in the variable CouponValue, because I’m only calculating for a coupon. I divide this number by a modified version of YTM or the yield to maturity. For the sake of this equation, I’m considering the YTM to be the same as the market value of the bond. Indeed, I showed in a previous post how sites like Yahoo!Finance include the YTM as part of the daily rates of a bond.

Now to calculate the denominator, first add one to the yield, (1 + YTM). Then I calculate for the exponent, which sits to the right of the ^ sign. If I’m going to follow the original equation correctly, the exponent should equal the number of times the loop has repeated, which also equals the number of cash flows it has calculated. The number of loops is stored in the variable CashFlowCounter, which makes it the variable that should be the exponent of the equation.

Once Excel has calculated the denominator and solved for the present value of the coupon, it checks to see if it should print the cash flow on the spreadsheet with this if-statement:

If ShowCashFlows = 1 Then
Cells(CashFlowColumn, CashFlowRow).Value = CashFlowValue
Cells(CashFlowColumn, CashFlowRow).NumberFormat = “$0.00”
End If

Earlier, I wrote that, if the user clicks the checkbox in the userform, the variable ShowCashFlows will be equal to one. If this is the case, then, according to the code above, the program will print the current loop’s cash flow value (with variable CashFlowValue) at the proper cell on the spreadsheet. The second cells command makes sure that that cash flow appears as a dollar value.

Before the loop ends, it also updates a running total of cash flows, which after the last loop will print as the total price of the bond. The code for the running total is this short line: Price = Price + CashFlowValue. The Price variable keeps the tally, which will print after the loop ends with the following two lines of code: Cells(9, 2).Value = Price and Cells(9, 2).NumberFormat = “$0.00”. These two lines will make the price appear in cell B9 as a dollar value.

Fast Final Thoughts

I want to end this post with a quick summary of my VBA program. The next (shorter) post will discuss a modified version of my VBA programs, at the end of which I’ll include mode detailed comments about both programs.

As I explained at the beginning of this post, the purpose of my VBA program is to calculate the price of individual cash flows of a bond, from which it calculates the total price of the bond. I broke the program into three parts. The first is what I call the main body, which calculates cash flows and calls the other two parts. The second part is a formatting function, which prints necessary column titles and adjusts the sizes of various cells in the spreadsheet. The third part is the userform, which generates a form in which users can type basic inputs for the bond: the face value, maturity, and coupon and yield to maturity rates.

The most important part of the program is the loop, which calculates cash flows. Based on an equation I discussed in a previous blog post, the loop’s calculation divides the value of a given cash flow by a modified version of the market rate of the bond. The loop repeats for each cash flow and, after keeping a running tally of cash flow prices, prints the total price of the bond after the last loop.

In my next post, I’ll quickly review how I modified this program to give a series of yields, which in my bond pricing post I used to test the inverse relationship between prices and yields. Altogether, I believe the two VBA programs I’ve written give a good foundation for the program I’ll write to evaluate the historical prices of medieval Venetian bonds.