[Page 291 (continued)]6.4. A Case Study: Analyze a Loan This case study develops a program to analyze a loan. Assume the loan is repaid in equal monthly payments and interest is compounded monthly. The program should request the amount (principal) of the loan, the annual rate of interest, and the number of years over which the loan is to be repaid. The four options to be provided by buttons are as follows: Calculate the monthly payment. The formula for the monthly payment is payment = P*r/(1 - (1 + r)^(-n)),
[Page 292]where p is the principal of the loan, r is the monthly interest rate (annual rate divided by 12) given as a number between 0 (for 0 percent) and 1 (for 100 percent), and n is the number of months over which the loan is to be repaid. Because a payment computed in this manner can be expected to include fractions of a cent, the value should be rounded up to the next nearest cent. This corrected payment can be achieved using the formula correct payment = Math.Round(payment + .005, 2).
Display an amortization schedule, that is, a table showing the balance on the loan at the end of each month for any year over the duration of the loan. Also show how much of each monthly payment goes toward interest and how much is used to repay the principal. Finally, display the total interest paid over the duration of the loan. The balances for successive months are calculated with the formula balance = (1 + r) * b - m,
where r is the monthly interest rate (annual rate / 12, a fraction between 0 and 1), b is the balance for the preceding month (amount of loan left to be paid), and m is the monthly payment. Show the effect of changes in the interest rate. Display a table giving the monthly payment for each interest rate from 1 percent below to 1 percent above the specified annual rate in steps of one-eighth of a percent. Quit.
Designing the Analyze-a-Loan Program For each of the tasks described in preceding options 1 to 4, the program must first look at the text boxes to obtain the particulars of the loan to be analyzed. Thus, the first division of the problem is into the following tasks: 1. | Input the principal, interest, and duration.
| 2. | Calculate the monthly payment.
| 3. | Calculate the amortization schedule.
| 4. | Display the effects of interest-rate changes.
| 5. | Quit.
|
Task 1 is a basic input operation and Task 2 involves applying the formula given in Option 1; therefore, these tasks need not be broken down any further. The demanding work of the program is done in Tasks 3 and 4, which can be divided into smaller subtasks. | | 3. | Calculate amortization schedule. This task involves simulating the loan month by month. First, the monthly payment must be computed. Then, for each month, the new balance must be computed together with a decomposition of the monthly payment into the amount paid for interest and the amount going toward repaying the principal. That is, Task 3 is divided into the following subtasks:
[Page 293] 3.1 | Calculate monthly payment. | 3.2 | Calculate new balance. | 3.3 | Calculate amount of monthly payment for principal. | 3.4 | Calculate amount of monthly payment for interest. | 3.5 | Calculate total interest paid. |
| 4. | Display the effects of interest-rate changes. A table is needed to show the effects of changes in the interest rate on the size of the monthly payment. First, the interest rate is reduced by one percentage point and the new monthly payment is computed. Then the interest rate is increased by regular increments until it reaches one percentage point above the original rate, with new monthly payment amounts computed for each intermediate interest rate. The subtasks for this task are then as follows:
4.1 | Reduce the interest rate by 1 percent. | 4.2 | Calculate the monthly payment. | 4.3 | Increase the interest rate by 1/8 percent. | 4.4 | Repeat until a certain condition is met. |
|
The hierarchy chart in Figure 6.13 shows the stepwise refinement of the problem. Figure 6.13. Hierarchy chart for the Analyze-a-Loan Program. 
The User Interface Figure 6.14 shows a possible form design and Table 6.4 gives the initial settings for the form and its controls. Figures 6.15, 6.16, and 6.17 (following the program) show possible runs of the program for each task available through the buttons. The width and height of the list box were adjusted by trial and error to handle the extensive output generated.
[Page 294] Figure 6.14. Template for the Analyze-a-Loan program. 
Table 6.4. Objects and initial properties for the Analyze-a-Loan program.Object | Property | Setting |
|---|
frmLoan | Text | Analysis of a Loan | lblPrincipal | Text | Amount of loan: | txtPrincipal | | | lblYearly Rate | Text | Interest rate: | txtYearly Rate | | | lblNumYears | Text | Number of loan years: | txtNumYears | | | btnPayment | Text | Calculate Monthly Payment | btnAmort | Text | Display Amortization Schedule | btnRateTable | Text | Display Interest Rate Change Table | btnQuit | Text | Quit | lstResults | | |
[Page 295] Figure 6.15. Monthly payment on a 30-year loan. 
Figure 6.16. Amortization for year 30 of a loan. 
[Page 296] Figure 6.17. Interest-rate-change table for a 30-year loan. 
Pseudocode for the Analyze-a-Loan Program Calculate Monthly Payment button: INPUT LOAN DATA (Sub procedure InputData) COMPUTE MONTHLY PAYMENT (Function Payment) DISPLAY MONTHLY PAYMENT (Sub procedure ShowPayment) Display Amortization Schedule button: INPUT LOAN DATA (Sub procedure InputData) DISPLAY AMORTIZATION SCHEDULE (Sub procedure ShowAmortSched) Compute monthly interest rate COMPUTE MONTHLY PAYMENT (Function Payment) Calculate and display amortization table Display total interest paid Display Interest Rate Change Table button: INPUT LOAN DATA (Sub procedure InputData) DISPLAY INTEREST RATE CHANGE TABLE (Sub procedure ShowInterestChanges) Decrease annual rate by .01 Do Display monthly interest rate COMPUTE AND DISPLAY MONTHLY PAYMENT (Function Payment) Increase annual rate by .00125 Loop Until annual rate > original annual rate + .01
[Page 297]Writing the Analyze-a-Loan Program Table 6.5 shows each task discussed before and the procedure that carries out the task. Table 6.5. Tasks and their procedures.Task | | Procedure |
|---|
1. | Input principal, interest, and duration. | InputData | 2. | Calculate monthly payment. | ShowPayment | 3. | Calculate amortization schedule. | ShowAmortizationSchedule | | | 3.1 Calculate monthly payment. | Payment | | | 3.2 Calculate new balance. | Balance | | | 3.3 Calculate amount paid for principal. | ShowAmortizationSchedule | | | 3.4 Calculate amount paid for interest. | ShowAmortizationSchedule | | | 3.5 Calculate total interest paid. | ShowAmortizationSchedule | 4. | Show effect of interest-rate changes. | ShowInterestChanges | | | 4.1 Reduce interest rate. | ShowInterestChanges | | | 4.2 Compute new monthly payment. | Payment | | | 4.3 Increase interest rate. | ShowInterestChanges | | | 4.4 Repeat until a certain condition is met. | ShowInterestChanges |
Private Sub btnPayment_Click(...) Handles btnPayment.Click Dim principal As Double 'Amount of loan Dim yearlyRate As Double 'Annual rate of interest Dim numMonths As Integer 'Number of months to repay loan InputData(principal, yearlyRate, numMonths) ShowPayment(principal, yearlyRate, numMonths) End Sub Private Sub btnAmort_Click(...) Handles btnAmort.Click Dim principal As Double 'Amount of loan Dim yearlyRate As Double 'Annual rate of interest Dim numMonths As Integer 'Number of months to repay loan InputData(principal, yearlyRate, numMonths) ShowAmortizationSchedule(principal, yearlyRate, numMonths) End Sub Private Sub btnRateTable_Click(...) Handles btnRateTable.Click Dim principal As Double 'Amount of loan Dim yearlyRate As Double 'Annual rate of interest Dim numMonths As Integer 'Number of months to repay loan InputData(principal, yearlyRate, numMonths) ShowInterestChanges(principal, yearlyRate, numMonths) End Sub Private Sub btnQuit_Click(...) Handles btnQuit.Click End End Sub
[Page 298]Sub InputData(ByRef principal As Double, _ ByRef yearlyRate As Double, ByRef numMonths As Integer) 'Input the loan amount, yearly rate of interest, and duration Dim percentageRate As Double, numYears As Integer principal = CDbl(txtPrincipal.Text) percentageRate = CDbl(txtYearlyRate.Text) yearlyRate = percentageRate / 100 numYears = CInt(txtNumYears.Text) numMonths = numYears * 12 End Sub Sub ShowPayment(ByVal principal As Double, _ ByVal yearlyRate As Double, ByVal numMonths As Integer) Dim monthlyRate As Double = yearlyRate / 12 Dim monthlyPayment As Double 'Calculate monthly payment monthlyPayment = Payment(principal, monthlyRate, numMonths) 'Display results lstResults.Items.Clear() lstResults.Items.Add("The monthly payment for a " & _ FormatCurrency(principal) & " loan at") lstResults.Items.Add(FormatNumber(yearlyRate * 100) & _ "% annual rate of interest for " _ & FormatNumber(numMonths / 12, 0) & " years is " & _ FormatCurrency(monthlyPayment)) End Sub Sub ShowAmortizationSchedule(ByVal principal As Double, _ ByVal yearlyRate As Double, ByVal numMonths As Integer) Dim questionYear As String Dim startMonth As Integer Dim monthlyRate As Double = yearlyRate / 12 Dim monthlyPayment As Double Dim totalInterest As Double = 0 Dim yearInterest As Double = 0 Dim oldBalance, newBalance As Double Dim numYears As Double = numMonths / 12 Dim principalPaid, interestPaid As Double Dim principalReduced As Double Dim fmtStr As String 'Ask for the year to display questionYear = "Please enter year (1-" & numYears & _ ") for which amortization is to be shown:" startMonth = 12 * CInt(InputBox(questionYear, "Which Year?")) - 11 'Display column headings lstResults.Items.Clear() fmtStr = "{0,-8}{1,-15}{2,-15}{3,-15}" lstResults.Items.Add(String.Format(fmtStr, "", "Amount Paid", _ "Amount Paid", "Balance at"))
[Page 299] lstResults.Items.Add(String.Format(fmtStr, "Month", _ "for Principal", "for Interest", "End of Month")) monthlyPayment = Payment(principal, monthlyRate, numMonths) oldBalance = principal fmtStr = "{0,4} {1,-15:C}{2,-15:C}{3,-15:C}" 'Loop for each month of the loan period For monthNum As Integer = 1 To numMonths 'Calculate the relevant figures newBalance = Balance(monthlyPayment, oldBalance, monthlyRate) principalPaid = oldBalance - newBalance interestPaid = monthlyPayment - principalPaid totalInterest = totalInterest + interestPaid 'Display results if current year is the desired year to display If (monthNum >= startMonth)And (monthNum < startMonth + 12) Then lstResults.Items.Add(String.Format(fmtStr, monthNum, _ principalPaid, interestPaid, newBalance)) yearInterest = yearInterest + interestPaid End If 'Update the old balance for the next pass through the For loop oldBalance = newBalance Next 'Display totals principalReduced = 12 * monthlyPayment - yearInterest fmtStr = "{0,-38}{1,-15:C}" lstResults.Items.Add("") lstResults.Items.Add(String.Format(fmtStr, _ "Reduction in principal:", principalReduced)) lstResults.Items.Add( String.Format(fmtStr, "Interest Paid:", yearInterest)) lstResults.Items.Add(String.Format(fmtStr, "Total interest over " & _ numYears & " years:", totalInterest)) End Sub Function Balance(ByRef payment As Double, _ ByVal principal As Double, ByVal monthlyRate As Double) As Double 'Compute balance at End of month Dim newBalance As Double newBalance = (1 + monthlyRate) * principal 'If balance is less than payment, then this 'payment is the last one for this loan If newBalance <= payment Then payment = newBalance Return 0 Else Return newBalance - payment End If End Function
[Page 300]Function Payment(ByVal principal As Double, _ ByVal monthlyRate As Double, ByVal numMonths As Integer) As Double Dim estimate As Double 'Estimate of monthly payment If numMonths = 0 Then 'If no months then the loan must be repaid immediately estimate = principal ElseIf monthlyRate = 0 Then 'If loan has no interest then just repay the principal over time estimate = principal / numMonths Else 'Otherwise, use the formula for determining the monthly payment estimate = principal * monthlyRate / (1 - _ (1 + monthlyRate) ^ (-numMonths)) End If 'Round the payment up if it there are fractions of a cent If estimate = Math.Round(estimate, 2) Then Returnestimate Else ReturnMath.Round(estimate + 0.005, 2) End If End Function Sub ShowInterestChanges(ByVal principal AsDouble, _ ByVal yearlyRate AsDouble, ByVal numMonthsAsInteger) Dim newRate AsDouble, monthlyRate AsDouble, monthlyPayment AsDouble Dim fmtStr AsString = "{0,15} {1,-15} {2,-15}" 'Display effect of interest changes lstResults.Items.Clear() lstResults.Items.Add(String.Format(fmtStr, "", "Annual", "")) lstResults.Items.Add(String.Format(fmtStr, "", _ "Interest Rate", "Monthly Payment")) 'Set the annual rate's lower limit for the table newRate = yearlyRate - 0.01 fmtStr = "{0,15} {1,-15} {2,-15:C}" 'Loop the annual rate from its lower limit to its upper limit Do 'Calculate the monthly payment for the corresponding monthly rate monthlyRate = newRate / 12 monthlyPayment = Payment(principal, monthlyRate, numMonths) lstResults.Items.Add(String.Format(fmtStr, "", _ FormatPercent(newRate, 3), monthlyPayment)) newRate = newRate + 0.00125 'Increment by 1/8 of one percent for 'the next pass through the loop 'To avoid rounding errors, add 0.001 to the upper-limit condition LoopUntil newRate > yearlyRate + 0.01 + 0.001 End Sub
[Page 301]Comments Tasks 3.1 and 3.2 are performed by functions. Using functions to compute these quantities simplifies the computations in ShowAmortizationSchedule. Because the payment was rounded up to the nearest cent, it is highly likely that the payment needed in the final month to pay off the loan will be less than the normal payment. For this reason, ShowAmortizationSchedule checks if the balance of the loan (including interest due) is less than the regular payment and, if so, makes appropriate adjustments. The standard formula for computing the monthly payment cannot be used if either the interest rate is zero percent or the loan duration is zero months. Although both of these situations do not represent reasonable loan parameters, provisions are made in the function Payment so that the program can handle these esoteric situations.
|
