Building a Loan Payment Calculator

Building a Loan Payment Calculator

Where I work, we've built a Web-based loan origination system. Clients can enter various parameters and see which loan products they qualify for. Then we pass some XML files to Fannie Mae for credit analysis and receive our response in XML. This data is parsed, and an e-mail message is sent back to the client. We then use Simple Object Access Protocol (SOAP) to pass e-mail messages around to the various internal departments. This system reduces the time it takes to return an acknowledgment to the user from days to a few minutes. Best of all, this system is available 24 hours a day, 7 days a week.

Let's build a very simple online calculator that includes user interaction. The program will let users enter the amount they want to borrow, an interest rate, and the term (in months) over which they want to repay the loan. The finished product is shown in Figure 13-12.

Figure 13-12

The finished online calculator application.

We will have to write some validation code to ensure the information the user enters is valid. We will also add a hyperlink control to enable navigation to another page, one that displays the loan payment schedule. The hyperlink will be disabled until legitimate information is entered and a monthly payment is calculated. When the user clicks the Calculate button and the payment is calculated, the Payment Schedule hyperlink is enabled and the user can navigate to another page, as shown in Figure 13-13.

Notice in Figure 13-14 that on the loan schedule page, the values from the first page are retained and are used to populate a Web Forms data grid. We can show some useful information such as how much of the payment goes to principal and how much to interest. The PrincipalRemaining and PaidToDate columns provide a good roadmap of how much of the loan is left to be paid. We will build a table from scratch, populate it with the loan information, and bind it to the data grid. This program will also demonstrate how to navigate from page to page as well as how to cache variables from one page to use on another.

Figure 13-13

The calculator application enables the Payment Schedule hyperlink after it calculates a payment.

Figure 13-14

The calculated values populate a Web Forms data grid.

Building Our Loan Application Project

Start a new Web Forms project and call it Loan Application. On the default form, add the controls listed in Table 13-3 and set their properties as described. The form should look something like Figure 13-15.

Figure 13-15

Your form should look similar to this.

Table 13-3  Properties for the Loan Application Controls

Object	Property	Value
Label	Name	lblTitle
	Font	Verdana
	Bold	True
	Underlined	True
	Text	The Pavlova, Ltd. Loan Calculator
Label	Name	lblAmount
	Text	How much do you want to borrow?
Text box	Name	tbAmount
	Text	""
Label	Name	lblRate
	Text	Interest Rate
Text box	Name	tbRate
	Text	""
Label	Name	lblTerm
	Text	Term of Loan (in months)
Drop-down list	Name	ddlTerm
Button	Name	btnCalculate
	Text	Calculate
Label	Name	lblMonthly
	Text	""
Range validator	ID	rvAmount
	ControlToValidate	tbAmount
	MaximumValue	150000
	MinimumValue	1000
	ErrorMessage	The loan must be between $1,000 and $150,000
	Type	Currency
Required field validator	ID	rfvAmount
	ControlToValidate	tbAmount
	ErrorMessage	Please enter a loan amount
Regular expression validator	ID	revRate
	ControlToValidate	tbRate
	ErrorMessage	Please enter a numeric rate in the format X.X
	ValidationExpression	\d*[.]{0,1}\d*
Required field validator	ID	rfvRate
	ControlToValidate	tbRate
	ErrorMessage	Please enter an interest rate
Hyperlink	ID	hlSchedule
	Text	Payment Schedule

We want to add the months in which to repay the loan to the drop-down list. Right-click the drop-down list, and then select Properties. Select the Items property box and then click the ellipsis to display the ListItem Collection Editor dialog box. Add the numbers shown in Figure 13-16, representing 15 years—from 12 through 180 months.

Figure 13-16

Add these values.

Adding Code to the Code-Behind Form

Right-click on the designer surface and select View Code to open the file WebForm1.aspx.vb, which is our code-behind form. Each .aspx Web Form that we create has its associated .aspx.vb code module behind it. This module contains our business logic. The relationship between the two forms is shown in Figure 13-17.

Figure 13-17

The relationship between a Web Form and its code-behind form.

WebForm1.aspx.vb contains the code that responds to events fired from the Web Form. Add these two Imports statements before the WebForm1 class statement.

Imports System.Math Imports System.Web.Caching Public Class WebForm1 Inherits System.Web.UI.Page

Now add the following code to the Page_Load event handler. This code simply initializes our drop-down list and disables the hyperlink.

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here  If Not IsPostBack Then ddlTerm.SelectedIndex = 0 hlSchedule.Enabled = False End If  End Sub Private Sub btnCalculate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCalculate.Click calculatePayment() hlSchedule.Enabled = True End Sub Private Sub calculatePayment() Dim iLoanAmount As Integer = CInt(tbAmount.Text) Dim sRate As Single = (CSng(tbRate.Text) / 100) Dim iterm As Integer = CInt(ddlTerm.SelectedItem.Value) Dim sPayment As Single = Pmt(sRate / 12, iterm, _ -iLoanAmount, 0, DueDate.BegOfPeriod) lblMonthly.Text = "Monthly Payment: " & _ Math.Round(sPayment, 2).ToString("C") Cache("LoanAmount") = iLoanAmount Cache("Rate") = sRate Cache("Term") = iterm Cache("Payment") = Math.Round(sPayment, 2) End Sub

The Life of a Web Form

A Web Form has four basic states in its life cycle—initialization, loading the page, event handling, and clearing up resources.

• Page initialization. The Page_Init event is fired when a page is initialized. At this point, controls perform all initialization required to create and set up each instance.

• Page load. The Page_Load event occurs after initialization. Here the page checks to see whether it is being loaded for the first time. It also performs data binding, reads and updates control properties, and restores the state saved from a previous client request.

• Event handling. Every action on a Web Form fires an event that goes to the server. Essentially there are two views of a Web Form—client view and server view. All processing of data is performed on the server. When an event is fired, the event goes to the server and returns the corresponding data.

• Cleanup. This stage is the last one to occur when a form is ready to be discarded. The Page_Unload event fires and does such cleanup work as closing files, closing database connections, and discarding objects.

How Our Program Works

When the Page_Load event fires, all the controls have been instantiated. There are tasks (such as initializing controls) that we want to perform only when the page first loads. Using the Page class's IsPostBack property, we can do just that. IsPostBack gets a value indicating whether the page is being loaded in response to a client postback or whether it is being loaded and accessed for the first time. If the page load is not in response to a client postback, we know that the page is being loaded for the first time. Here we simply select the first item in our drop-down list (so that we have a current value) and disable the hyperlink control.

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not IsPostBack Then ddlTerm.SelectedIndex = 0 hlSchedule.Enabled = False End If End Sub

When the user clicks the Calculate button, our validation controls perk up their ears. If any field is empty or contains an invalid value, the user is prompted to fix whatever is wrong and the code in the button's Click event does not fire. When all the validation criteria are met, the routine calculatePayment is called. We know that when this routine is called, we will receive a solid value because we validated each of the fields.

I like to use controls such as drop-down lists with predefined values. The man-machine interface is the most difficult to program because users can do anything imaginable—and many things unimaginable. But by populating a drop-down list with valid data, users can select only a valid value.

Private Sub btnCalculate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCalculate.Click calculatePayment() hlSchedule.Enabled = True End Sub

The calculatePayment routine does the heavy lifting. We can initialize the first three variables based on values in the form's controls. Remember, these variables are stored as text values, so we simply cast them as the correct numeric values of integer or single. The rate must be divided by 100 because a rate of 8.2 is really 0.082 when used in calculations.

Private Sub calculatePayment() Dim iLoanAmount As Integer = CInt(tbAmount.Text) Dim sRate As Single = (CSng(tbRate.Text) / 100) Dim iterm As Integer = CInt(ddlTerm.SelectedItem.Value)

Determining the payment is simple because Visual Basic .NET has a built-in financial function named Pmt. This function returns a Double value (we use a Single in our code) specifying the payment for an annuity on the basis of periodic, fixed payments and a fixed interest rate. We pass in the value of the variables taken from the Web Form and place them as parameters to the Pmt function. The loan amount is given a negative sign because it returns a negative amount, so we make it positive. There is no future value so that value is 0.

PMT(RATE, Number of Periods, Loan Amount, _ Future Value, Due Date)

We again take advantage of the new Visual Basic .NET feature of dimming and initializing this variable on the same line.

Dim sPayment As Single = Pmt(sRate / 12, iterm, _ -iLoanAmount, 0, DueDate.BegOfPeriod)

We imported the Math library because we wanted the Round method that it includes. This method will display our value with only two decimal values instead of 10, which would normally be shown. Rounding values like this is an added touch that separates professional software from the rest.

lblMonthly.Text = "Monthly Payment: " & _ Math.Round(sPayment, 2).ToString("C")

It's necessary to retain the values we got from this form and pass them to another form that will display the payment schedule. That bit of work is simple with the Cache class, which implements the cache for a Web Forms application. One instance of this class is created per application domain, and it remains valid as long as the application domain remains active. As long as our program is running, its Cache object remains intact. We simply add to the Cache with the following syntax:

Cache("Key") = value

Here's the relevant code from our program:

Cache("LoanAmount") = iLoanAmount Cache("Rate") = sRate Cache("Term") = iterm Cache("Payment") = Math.Round(sPayment, 2)

You can improve your application's performance by storing your objects and values in the cache. The cache is global to the ASP.NET application, is thread safe, and implements automatic locking so that it is safe for you to access your cached objects and values concurrently from more than one page.

Taking a Closer Look at Our Drop-Down List

Take a look at the HTML code behind our WebForm1.aspx file by clicking HTML in the lower left side of the designer. You can see that the drop-down list control has the asp: directive along with various attributes that deal with its size and position.

<asp:dropdownlist style="Z-INDEX: 107; LEFT: 24px; POSITION: absolute; TOP: 188px" runat="server" Height="22px" Width="104px"> <asp:ListItem Value="12">12</asp:ListItem> <asp:ListItem Value="24">24</asp:ListItem> <asp:ListItem Value="36">36</asp:ListItem> <asp:ListItem Value="48">48</asp:ListItem>  </asp:dropdownlist>

Remember that when the form is run, we programmatically select the first element in the drop-down list to ensure that we have a valid value. If you look at the HTML source within the browser as your page is displayed, you can see that the item is selected in the HTML code.

<option selected="selected" value="12">12</option> <option value="24">24</option> <option value="36">36</option> <option value="48">48</option>

Then, when the user selects a value from the drop-down list and clicks the Calculate button and a postback occurs, the code is changed to show that the user selected a new value.

<option value="12">12</option> <option value="24">24</option> <option value="36">36</option> <option value="48">48</option> <option selected="selected" value="60">60</option><option value="72">72</option>

Adding the Payment Schedule Page

Now it's time to add another Web Form to our program. This page will hold the payment schedule. After we have this page set up, we can set the hyperlink property for our control on the input page so that it will navigate to the payment schedule page. Click Project | Add Web Form, select the Web Form template, and keep the default name WebForm2.aspx, as shown in Figure 13-18.

Figure 13-18

Add another Web Form to the program.

Return to the WebForm1 main page to complete our remaining task—assigning the NavigateURL property of our hyperlink control. Right-click on the control and select Properties. Click the ellipsis next to NavigateURL to display the Select URL dialog box, shown in Figure 13-19. Select WebForm2.aspx.

Figure 13-19

Select WebForm2.aspx in the Select URL dialog box.

Return to the payment schedule form (WebForm2.aspx), and add two labels and a data grid. We want to change the default look of the form to something a bit more interesting. Right-click on the data grid, and select Auto Format. Select the Colorful 5 scheme. Our form is shown in Figure 13-20.

Figure 13-20

Add two labels and a data grid.

Set the properties of the labels and data grid as shown in Table 13-4.

Table 13-4  Properties for the WebForm2.aspx Controls

Object	Property	Value
Label	ID	lblTitle
	Font/Size	Larger
	Text	Loan Payment Schedule
Label	ID	lblDetails
	Text	""
Data grid	ID	dgSchedule
	CellPadding	5
	CellSpacing	2

Right-click on the designer, and then select View Code. The second code-behind form, this one named WebForm2.aspx.vb, is added to your project.

Adding Our Class Code

Add the following two Imports statements at the top of the class:

Imports Loan_Application.WebForm1

Imports System.Web.Caching Public Class WebForm2 Inherits System.Web.UI.Page Protected WithEvents dgSchedule As _ System.Web.UI.WebControls.DataGrid Protected WithEvents lblTitle As _ System.Web.UI.WebControls.Label Protected WithEvents lblDetails As _ System.Web.UI.WebControls.Label

After the class and control definitions, add the following variables and routines. We will be using some of these variables in more than one location, so place them in the class-level area so that they can be seen throughout the entire class.

Dim dsSchedule As DataSet Dim tblTable As DataTable Dim iLoanAmount As Integer Dim sRate As Single Dim iTerm As Integer Dim sPayment As Single Dim colColumn1 As DataColumn Dim colColumn2 As DataColumn Dim colColumn3 As DataColumn Dim colColumn4 As DataColumn Dim colColumn5 As DataColumn Dim colColumn6 As DataColumn Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here  iLoanAmount = Cache("LoanAmount") sRate = Cache("Rate") iTerm = Cache("Term") sPayment = Cache("Payment") lblDetails.Text = "Loan Amount: " & _ iLoanAmount.ToString("C") & " Rate: " & _ sRate.ToString("P") & " Term: " & _ iTerm.ToString & " months." 'Build the dataset and table constructTable() calculateSchedule()End Sub Private Sub calculateSchedule() Dim iInstallment As Integer Dim drDataRow As DataRow Dim sPrincipal As Single Dim sPaidToDate As Single = 0 Dim sTotalPrincipal As Single = iLoanAmount For iInstallment = 1 To iTerm drDataRow = dsSchedule.Tables("Schedule").NewRow dsSchedule.Tables("Schedule").Rows.Add(drDataRow) drDataRow("Installment") = iInstallment drDataRow("Payment") = sPayment.ToString("C") sPrincipal = PPmt(sRate / 12, iInstallment, iTerm, _ -iLoanAmount, 0, DueDate.BegOfPeriod) drDataRow("Principal") = Math.Round(sPrincipal, _ 2).ToString("C") drDataRow("Interest") = (sPayment - _ sPrincipal).ToString("C") sTotalPrincipal -= sPrincipal drDataRow("PrincipalRemaining") = _ sTotalPrincipal.ToString("C") sPaidToDate += sPayment drDataRow("PaidToDate") = sPaidToDate.ToString("C") dsSchedule.AcceptChanges() Next With dgSchedule .PageSize = iTerm .DataSource = _ New DataView(dsSchedule.Tables("Schedule")) .DataBind() End With End Sub Private Sub constructTable() 'Instantiate the dataset and table dsSchedule = New DataSet("PaymentSchedule") tblTable = New DataTable("Schedule") dsSchedule.Tables.Add(tblTable) colColumn1 = New DataColumn("Installment") colColumn1.DataType = System.Type.GetType("System.Int32") colColumn2 = New DataColumn("Payment") colColumn2.DataType = System.Type.GetType("System.String") colColumn3 = New DataColumn("Principal") colColumn3.DataType = System.Type.GetType("System.String") colColumn4 = New DataColumn("Interest") colColumn4.DataType = System.Type.GetType("System.String") colColumn5 = New DataColumn("PrincipalRemaining") colColumn5.DataType = System.Type.GetType("System.String") colColumn6 = New DataColumn("PaidToDate") colColumn6.DataType = System.Type.GetType("System.String") With tblTable.Columns .Add(colColumn1) .Add(colColumn2) .Add(colColumn3) .Add(colColumn4) .Add(colColumn5) .Add(colColumn6) End With End Sub

How the Calculator Works

When the page loads, we read the values we stuffed in the cache and assign them to our class-level variables. The details of the loan are displayed in the lblDetails label. Note that we can use the formatting method of the ToString method to quickly and painlessly format our output.

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here iLoanAmount = Cache("LoanAmount") sRate = Cache("Rate") iTerm = Cache("Term") sPayment = Cache("Payment") lblDetails.Text = "Loan Amount: " & _ iLoanAmount.ToString("C") & " Rate: " & _ sRate.ToString("P") & " Term: " & _ iTerm.ToString & " months." 'Build the dataset and table constructTable() calculateSchedule() End Sub

In our constructTable routine, a DataSet object and table are instantiated. The table is then added to the new data set. Six columns are created and given names that will be shown as the column titles. The first column will hold an integer because this column will display the payment number. However, we want to format the rest of the fields and display them as currency, so they are all made to hold the String data type. Finally, each of the six columns are added to the table columns collection.

Private Sub constructTable() 'Instantiate the dataset and table dsSchedule = New DataSet("PaymentSchedule") tblTable = New DataTable("Schedule") dsSchedule.Tables.Add(tblTable) colColumn1 = New DataColumn("Installment") colColumn1.DataType = System.Type.GetType("System.Int32") colColumn2 = New DataColumn("Payment") colColumn2.DataType = System.Type.GetType("System.String") colColumn3 = New DataColumn("Principal") colColumn3.DataType = System.Type.GetType("System.String") colColumn4 = New DataColumn("Interest") colColumn4.DataType = System.Type.GetType("System.String") colColumn5 = New DataColumn("PrincipalRemaining") colColumn5.DataType = System.Type.GetType("System.String") colColumn6 = New DataColumn("PaidToDate") colColumn6.DataType = System.Type.GetType("System.String") With tblTable.Columns .Add(colColumn1) .Add(colColumn2) .Add(colColumn3) .Add(colColumn4) .Add(colColumn5) .Add(colColumn6) End With End Sub

Now that the table has been dynamically created and added to the data set, it can be populated. The procedure-level variables are dimmed—no surprises here.

Private Sub calculateSchedule() Dim iInstallment As Integer Dim drDataRow As DataRow Dim sPrincipal As Single Dim sPaidToDate As Single = 0 Dim sTotalPrincipal As Single = iLoanAmount

Essentially, we loop through from 1 to the number of months, perform calculations, and then bind the information to the data grid. For each iteration of the loop, a new DataRow object is added, created from the Schedule table in the data set. That new empty row is then added to the Schedule table. Recall that we gave each of the columns names such as "Installment" and "Payment" when we created the table. Now that we have a new data row, we can easily access the columns in the row by their names. The Installment column will contain the number of months from 1 to the term of the loan. The Payment column will always contain the same value—the fixed payment each month—so we simply add that value. Again, notice that we format the value by converting it to "C", for currency.

For iInstallment = 1 To iTerm drDataRow = dsSchedule.Tables("Schedule").NewRow dsSchedule.Tables("Schedule").Rows.Add(drDataRow) drDataRow("Installment") = iInstallment drDataRow("Payment") = sPayment.ToString("C")

Visual Basic has another handy built-in financial function, PPmt. This function returns a value specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. By passing in the rate (divided by 12 to represent a single month), the payment, the term of the loan, the amount, 0, and whether the payment is due at the beginning or end of the period, we get the principal amount. That amount is then rounded, formatted, and placed in the Principal column of that row.

sPrincipal = PPmt(sRate / 12, iInstallment, iTerm, _ -iLoanAmount, 0, DueDate.BegOfPeriod) drDataRow("Principal") = Math.Round(sPrincipal, _ 2).ToString("C")

We can easily deduce how much of the payment goes toward interest by simply subtracting the principal from the payment.

drDataRow("Interest") = (sPayment - _ sPrincipal).ToString("C")

The total principal to be paid (that is, the loan amount) is decremented by the principal paid for this single payment. The amount is formatted and added to the remaining principal column of the row.

sTotalPrincipal -= sPrincipal drDataRow("PrincipalRemaining") = _ sTotalPrincipal.ToString("C")

We initialize how much we have paid to date to 0 at the beginning of the routine and then increment it for each payment made. The user can then see how much he or she has paid into the loan for each payment. That value is then added to the correct column of the row. Finally we add the changes to the data set for that new row.

sPaidToDate += sPayment drDataRow("PaidToDate") = sPaidToDate.ToString("C") dsSchedule.AcceptChanges()

We wrap up the routine by setting how many rows the data grid displays to the number of months of the loan. A DataView object that contains the Schedule table with all the new rows is assigned to the DataSource property of the data grid. Finally we bind the DataView object to the data grid and it is displayed.

With dgSchedule .PageSize = iTerm .DataSource = New _ DataView(dsSchedule.Tables("Schedule")) .DataBind() End With

After all this, you should make an important note. All this code is for logic processing and none for displaying the data on the Web Form. On our Web Forms, we used the same object models for the controls that we used in Windows Forms. And the code behind is just like Windows Forms code. This is a milestone in Web development. Programming for the Web is nearly identical to programming for Windows!

Tracing Our Program

A useful way to find out what is happening in our program is to enable tracing. In the HTML section of the WebForm1.aspx form, add the attribute Trace="True" to the first line.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind = "WebForm1.aspx.vb" Trace="True" Inherits = "Loan_Application.WebForm1"%>

By adding this attribute, you can get some handy information about the page, including its Session ID and the timing information to display the page. This information is shown in Figure 13-21. (The Visible property of each control on the page was temporarily set to False to capture the figure.)

Figure 13-21

Enable tracing to get a wealth of information about the page.