Developing a UserForm for Annuities

 Some Background.  In the last webpage we introduced the concept of annuities and presented the formulas.  In addition we presented the Excel functions associated with calculating certain unknown parameters.  In the present webpage we will focus on annuities where a buyer Starts the annuity with no money down, PV = 0. Adds an equal amount of money each month for a given number of years for N years.  Remember, to obtain the total number of payments you need to multiply the number of payments per year = 12 times N. Is interested in attaining some future return = FV. The annuity has some fixed annual rate of return i. This is somewhat of a simplification of what we talked about before by focusing on monthly payments and no money down.  This situation can be made to be equivalent to paying off a loan, but we will not consider that perspective in this webpage.  Your homework will involve building in some of these features that we are presently leaving out. We are going to develop a form where a user Must enter the annual rate of return, which will be divided by 12 within the event code. Can enter any two of the three arguments payment amount = Payment desired future return = FV (number of years) = N  which will be multiplied by 12 within the event code. The program will automatically compute the one argument of the three that the user left out when they click a button to calculate. There will also be buttons to clear a particular set of inputs and exit the program. Each time the program starts up the form will appear with no input numbers in the textboxes on the form. The argument that was  left out by the user and then calculated by the computer will be highlighted using some sort of special coloring of its textbox. This list gives us the necessary set of specifications.Next you want to develop a UserForm that looks like the following.

 The following table contains a listing of the controls and their properties.

 Control Property Value Form Name frmAnnuities Caption Computing Annuity Performance Measures Font Tahoma - 12 PT - Bold Frame Name frameRequiredInput Caption Required Input Label Name lblInterest Caption Annual Interest Rate TextBox Name txtInterest Frame Name frameInputs Caption Two of the Three Inputs Label Name lblPayment Caption Monthly Payment Amount TextBox Name txtPayment Label Name lblFutureValue Caption Desired Future Value TextBox Name txtFV Label Name lblNumberOfYears Caption Number of Years TextBox Name txtYears CommandButton Name cmdCalculate Caption Calculate Unknown CommandButton Name cmdClearInputs Caption Clear Inputs CommandButton Name cmdExit Caption Exit

 Event Code for the Exit CommandButton.  Now you want to make sure to copy the following code into the cmdExit_Click( ) procedure, for the click event of the cmdExit button. Private Sub cmdExit_Click() Unload frmAnnuities End Sub This will end the program. Event Code for the Clear Inputs CommandButton.  Now you want to copy the following code for the cmdClearInputs_Click( ) procedure, for the click event of the cmdClearInputs button. Private Sub cmdClearInputs_Click() txtInterest.Text = "" txtFV.Text = "" txtFV.BackColor = RGB(223, 223, 223) txtPayment.Text = "" txtPayment.BackColor = RGB(223, 223, 223) txtYears.Text = "" txtYears.BackColor = RGB(223, 223, 223) End Sub This code very simply takes all of the entries in the textboxes and makes them blank and change all the background colors back to light gray. Event Code for the Calculate Unknown CommandButton.  This code will be quite elaborate to develop so that we will present it in sections.  Then at the end we will put all of the code together so that you can copy it all at once if you desire. Declaring and Initializing Variables.  First we will start with dimensioning and initializing the variables that we will need.  This will go in the cmdCalculate_Click( ) procedure that will be initiated if you double click on the command button in design mode. Private Sub cmdCalculate_Click() Dim BlankMsg, NonNumericMsg, Msg As String Dim BlankPayment, BlankFV, BlankYears As Boolean Dim Payment, AnnualInterestRate, FutureValue As Double Dim InterestRate as Double Dim NumberYears, NumberPayments, BlankCounter, NonNumericCounter, ValidEntries As Integer ' Initializing variables BlankMsg = "" NonNumericMsg = "" Msg = "" BlankCounter = 0 ValidEntries = 0 NonNumericCounter = 0 BlankPayment = False BlankFV = False BlankYears = False ' ' Setting all of the background colors to light gray ' txtFV.BackColor = RGB(223, 223, 223) txtPayment.BackColor = RGB(223, 223, 223) txtYears.BackColor = RGB(223, 223, 223) End Sub The following gives a description of the variables.  There is always more than one way to program a solution to a problem and even more ways to define the variables.  Most of these variables relate to input validation rather than actual computation of the results. The Msg variables are used to develop messages that report input errors. The BlankMsg variable is used to develop an error message for too many elective inputs are left blank. The NonNumericMsg variable is used to develop a message about which messages are non-numeric. The BlankPayment, BlankFV and BlankYears Boolean variables are used as flags for determining which of the values needs to be computed. Payment, InterestRate, FutureValue and NumberPayments are the inputs or single output for the computation.  NumberPayments is developed from NumberYears and the assumption that there are monthly payments.  InterestRate is derived from the inputted AnnualInterestRate. BlankCounter and NonNumericCounter help the programmer keep track of how many input entries are of each type. ValidEntries counts up the number of valid numeric inputs. After this most of the variables need to be initialized. All of the background colors for the text boxes need to be set to light gray because some of them may be light green due to earlier caluculations. Inputting and Validating the Interest Rate.  The next major portion of the code relates to validating the inputs.  This section of code can be broken down into Validating the required AnnualInterestRate For the three remaining variables, Payment, FutureValue and NumberPayments Validating that exactly one is left blank Validating the others are numeric This section of code is quite elaborate.  The comments should help.  We will also discuss this more after the code and in much more detail in class.  The first section relates to the required input. ' Checking Inputs to Verify They are Sufficient for Calculation ' This section is validating that an appropriate number has been entered for the interest rate ' This segment relates to making sure something numeric is entered in the required input If txtInterest.Text = "" Or IsNumeric(txtInterest.Text) = False Then MsgBox "You must enter a number between 0.0 and 1.0" & vbCrLf & "to _  represent the Interest Rate", vbOKOnly, "Input Error in Interest Rate" txtInterest.Text = "" Exit Sub Else ' This segment validates whether the interest rate is actually a percentage AnnualInterestRate = CDbl(txtInterest.Text) If AnnualInterestRate >= 1# Or AnnualInterestRate <= 0# Then MsgBox "You must enter a number between 0.0 and 1.0" & vbCrLf & _ "to represent the Interest Rate", vbOKOnly, "Input Error in Interest _ Rate" txtInterest.Text = "" Exit Sub End If End If A blank space followed by an underscore,   _   is the line continuation character in VB.  It tells the compiler to treat the next line as a continuation of the current line.  You can take them out if you want when you copy the code, but make sure to put appropriate things on the same line. In this code segment we didn't do anything unusual to develop the messages if there are errors detected by the the If - Then clauses.  We check for blank entries, whether the inputs are non-numeric and if they are numeric whether they fall in the correct range for an interest rate. Inputting and Validating the Elective Inputs.  This next section of code gets even more elaborate because the user is expected to input any two of the three entries.  We must also check for whether they input numerical values.  In this section we are going to focus purely on validating the inputs and developing the error messages in response. ' This section of code is verifying that the user has input at least two of the three necessary  ' inputs in the input section and hasn't inputted invalid entries ' First we validate the input for the Payment variable in txtPayment ' This segment determines whether the input is blank and takes appropriate action If txtPayment.Text = "" Then BlankCounter = BlankCounter + 1 BlankPayment = True BlankMsg = BlankMsg & "Your entry for the payment amount is blank." &  vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtPayment.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the payment _ amount is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable Payment = CDbl(txtPayment.Text) ValidEntries = ValidEntries + 1 End If End If ' Now we validate the input for the FutureValue variable in txtFV ' This segment determines whether the input is blank and takes appropriate action If txtFV.Text = "" Then BlankCounter = BlankCounter + 1 BlankFV = True BlankMsg = BlankMsg & "Your entry for the desired future value is blank." & _ vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtFV.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the desired _ future value is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable FutureValue = CDbl(txtFV.Text) ValidEntries = ValidEntries + 1 End If End If ' Now we validate the input for the NumberPayments variable in txtYears ' This segment determines whether the input is blank and takes appropriate action If txtYears.Text = "" Then BlankCounter = BlankCounter + 1 BlankYears = True BlankMsg = BlankMsg & "Your entry for the number years to make payments is _ blank." & vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtYears.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the number _ years to make payments is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable NumberYears = CDbl(txtYears.Text) ValidEntries = ValidEntries + 1 End If End If Well, there are some obvious patterns to this segment of code, hopefully made clearer by the comments.  Each larger section focuses on each of the variables.  Within each section we first determine whether the input is blank.  If it is blank then we must increment a counter and augment a developing error message in case too many inputs are left blank.  The next subsection validates whether the entry is numeric.  If it isn't then an error message is augmented and a counter to keep track of errors is incremented.  Finally if the input passes both tests the appropriate variable in the program is updated and the number of valid inputs is incremented. It is important to remember that if the user enters too many valid inputs there is nothing to compute. Getting Error Messages to the User.  This next section of code relates to giving appropriate feedback to the user depending on what they entered. ' Printing out the error messages if some of the inputs are invalid, too many are left out  ' or too many are put in If BlankCounter > 1 Or NonNumericCounter > 0 Or ValidEntries = 3 Then ' The case where too many inputs are left out If BlankCounter > 1 Then Msg = "You have omitted too many of the inputs" & vbCrLf & _ BlankMsg & vbCrLf & vbCrLf End If ' The case where any of the inputs are non-numeric If NonNumericCounter > 0 Then Msg = Msg & "You have entered non-numeric inputs" & vbCrLf & _ NonNumericMsg End If ' The case where the user inputs all three of the elective inputs If ValidEntries = 3 Then Msg = "You have entered numeric values in all three of the inputs." & _ vbCrLf & "You need to leave exactly one of them blank." End If MsgBox Msg Exit Sub End If Performing the Computations.  Now we finally get to the portion of the code where we actually calculate the solution depending on the inputs.  You will definitely notice that while the formulas are complicated, the code is really simpler than what we have seen so far. ' Now we get to the section of the program where we actually compute our unknown. ' If the Payment Amount was left blank and the others were filled in appropriately ' this section of code will determine the payment amount. InterestRate = AnnualInterestRate / 12 NumberPayments = NumberYears * 12 ' Calculate the payment If BlankPayment = True Then Payment = FutureValue * (InterestRate / ((1 + InterestRate) ^ NumberPayments - 1)) Payment = FormatCurrency(Payment, 2, vbUseDefault) txtPayment.Text = Payment txtPayment.Font.Bold = True txtPayment.BackColor = RGB(200, 255, 200) End If ' Calculate the future value If BlankFV = True Then FutureValue = Payment * (((1 + InterestRate) ^ NumberPayments - 1) / InterestRate) FutureValue = FormatCurrency(FutureValue, 2, vbUseDefault) txtFV.Text = FutureValue txtFV.Font.Bold = True txtFV.BackColor = RGB(200, 255, 200) End If ' Calculate the number of payments If BlankYears = True Then NumberPayments = Log((InterestRate * FutureValue / Payment) + 1) / Log(1 + _ InterestRate) NumberYears = NumberPayments / 12 NumberYears = FormatNumber(NumberYears, 2, vbUseDefault) txtYears.Text = NumberYears txtYears.Font.Bold = True txtYears.BackColor = RGB(200, 255, 200) End If These formulas are based on those in the last page.  Notice we didn't make use of the built-in Excel functions.  The other thing to notice is that most of the statements have to do with formatting the output with properties such as the font and background color.The code all at once is in the following table.

 Private Sub cmdCalculate_Click() Dim BlankMsg, NonNumericMsg, Msg As String Dim BlankPayment, BlankFV, BlankYears As Boolean Dim Payment, AnnualInterestRate, FutureValue As Double Dim InterestRate as Double Dim NumberYears, NumberPayments, BlankCounter, NonNumericCounter, ValidEntries As Integer ' Initializing variables BlankMsg = "" NonNumericMsg = "" Msg = "" BlankCounter = 0 ValidEntries = 0 NonNumericCounter = 0 BlankPayment = False BlankFV = False BlankYears = False ' ' Setting all of the background colors to light gray ' txtFV.BackColor = RGB(223, 223, 223) txtPayment.BackColor = RGB(223, 223, 223) txtYears.BackColor = RGB(223, 223, 223)' Checking Inputs to Verify They are Sufficient for Calculation ' This section is validating that an appropriate number has been entered for the interest rate ' This segment relates to making sure something numeric is entered in the required input If txtInterest.Text = "" Or IsNumeric(txtInterest.Text) = False Then MsgBox "You must enter a number between 0.0 and 1.0" & vbCrLf & "to _  represent the Interest Rate", vbOKOnly, "Input Error in Interest Rate" txtInterest.Text = "" Exit Sub Else ' This segment validates whether the interest rate is actually a percentage AnnualInterestRate = CDbl(txtInterest.Text) If AnnualInterestRate >= 1# Or AnnualInterestRate <= 0# Then MsgBox "You must enter a number between 0.0 and 1.0" & vbCrLf & _ "to represent the Interest Rate", vbOKOnly, "Input Error in Interest Rate" txtInterest.Text = "" Exit Sub End If End If ' This section of code is verifying that the user has input at least two of the three necessary  ' inputs in the input section and hasn't inputted invalid entries ' First we validate the input for the Payment variable in txtPayment ' This segment determines whether the input is blank and takes appropriate action If txtPayment.Text = "" Then BlankCounter = BlankCounter + 1 BlankPayment = True BlankMsg = BlankMsg & "Your entry for the payment amount is blank." &  vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtPayment.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the payment _ amount is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable Payment = CDbl(txtPayment.Text) ValidEntries = ValidEntries + 1 End If End If ' Now we validate the input for the FutureValue variable in txtFV ' This segment determines whether the input is blank and takes appropriate action If txtFV.Text = "" Then BlankCounter = BlankCounter + 1 BlankFV = True BlankMsg = BlankMsg & "Your entry for the desired future value is blank." & _ vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtFV.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the desired _ future value is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable FutureValue = CDbl(txtFV.Text) ValidEntries = ValidEntries + 1 End If End If ' Now we validate the input for the NumberPayments variable in txtYears ' This segment determines whether the input is blank and takes appropriate action If txtYears.Text = "" Then BlankCounter = BlankCounter + 1 BlankYears = True BlankMsg = BlankMsg & "Your entry for the number years to make payments is _ blank." & vbCrLf Else ' This segment determines whether the input is non-numeric and takes appropriate action If IsNumeric(txtYears.Text) = False Then NonNumericCounter = NonNumericCounter + 1 NonNumericMsg = NonNumericMsg & "Your entry for the number _ years to make payments is non-numeric" & vbCrLf Else ' If the input is valid then it is assigned to the variable NumberYears = CDbl(txtYears.Text) ValidEntries = ValidEntries + 1 End If End If ' Printing out the error messages if some of the inputs are invalid, too many are left out  ' or too many are put in If BlankCounter > 1 Or NonNumericCounter > 0 Or ValidEntries = 3 Then ' The case where too many inputs are left out If BlankCounter > 1 Then Msg = "You have omitted too many of the inputs" & vbCrLf & _ BlankMsg & vbCrLf & vbCrLf End If ' The case where any of the inputs are non-numeric If NonNumericCounter > 0 Then Msg = Msg & "You have entered non-numeric inputs" & vbCrLf & _ NonNumericMsg End If ' The case where the user inputs all three of the elective inputs If ValidEntries = 3 Then Msg = "You have entered numeric values in all three of the inputs." & _ vbCrLf & "You need to leave exactly one of them blank." End If MsgBox Msg Exit Sub End If ' Now we get to the section of the program where we actually compute our unknown. ' If the Payment Amount was left blank and the others were filled in appropriately ' this section of code will determine the payment amount. InterestRate = AnnualInterestRate / 12 NumberPayments = NumberYears * 12 ' Calculate the payment If BlankPayment = True Then Payment = FutureValue * (InterestRate / ((1 + InterestRate) ^ NumberPayments - 1)) Payment = FormatCurrency(Payment, 2, vbUseDefault) txtPayment.Text = Payment txtPayment.Font.Bold = True txtPayment.BackColor = RGB(200, 255, 200) End If ' Calculate the future value If BlankFV = True Then FutureValue = Payment * (((1 + InterestRate) ^ NumberPayments - 1) / InterestRate) FutureValue = FormatCurrency(FutureValue, 2, vbUseDefault) txtFV.Text = FutureValue txtFV.Font.Bold = True txtFV.BackColor = RGB(200, 255, 200) End If ' Calculate the number of payments If BlankYears = True Then NumberPayments = Log((InterestRate * FutureValue / Payment) + 1) / Log(1 + _ InterestRate) NumberYears = NumberPayments / 12 NumberYears = FormatNumber(NumberYears, 2, vbUseDefault) txtYears.Text = NumberYears txtYears.Font.Bold = True txtYears.BackColor = RGB(200, 255, 200) End If End Sub