Program 3:
Amortization Schedule

 

Objectives:  Gain experience using control structures.

Description:  This program is intended to provide you with experience using sequence, selection, and iteration.  You will use nested if statements as well as nested loops.


Error checking is not required in this, or subsequent programs.  Why not?


In this program you will develop an amortization schedule.  The word "amortize" means to "To write off gradually and systematically a given amount of money within a specific number of time periods."  Unlike the last programs, this amortization program generates a complete mortgage amortization schedule for the loan that allows you to estimate your monthly loan repayments.  It will show for each month how much of your monthly loan payment is being applied towards interest costs, and how much is actually being applied to reduce the outstanding balance (principal) of your loan.

Like the previous programs it will calculate the monthly payment of your loan as well as the total interest paid.  It will also show the total amount that you are required to repay as well as the interest paid as a percentage of the initial principal.


Your program requires a loop to make calculations for each payment, which means that the loop must run until all payments have been made, or (number of payments * payments per year) iterations.  You first determine the payment amount, and then for every payment you determine the interest paid, the principal paid, and the principal balance remaining.  Many of these values can be calculated using built-in VB functions: Financial.Pmt, Financial.IPmt, Financial.PPmt.  Here are the formulas.

  • monthlyPayment = -Financial.Pmt(decIntPerPayment, paymentsPerYear, loanAmount)

  • monthlyInterestPayment = -Financial.IPmt(decIntPerPayment, paymentCounter, paymentsPerYear, loanAmount)

  • monthlyPrincipalPayment = -Financial.PPmt(decIntPerPayment, paymentCounter, paymentsPerYear, loanAmount)

  • balance = balance - monthlyPrincipalPayment

  • See note at bottom....

In these formulas decIntPerPayment stands for the interest rate per payment (so it is the interest rate from the form divided by number of payments per year), paymentCounter is the loop control variable that counts the number of payments, paymentsPerYear is obtained from the Payments per Year, and loanAmount is obtained from the Total Borrowed field.

Each iteration of the loop should also update the total payments and total interest.

After every 12 payments (or whatever number is in Payments per Year field) the output should include a dashed line to separate individual years.  The modulus function comes in handy for this purpose.


The demo includes a check to be sure that the loan amount is under $1,000,000 because numbers that large will mess up the formatting.  You should do the same.

Make your interface closely resemble the demo and image below.


Tips:

Use labels for the summary output (Total Repaid, Total Interest Paid, Interest as Percentage of Principal).

Use a multi-line text box (a text box with the multi-line property set to True) for the amortization schedule.  Be sure that it includes a vertical scrollbar (another property).

The following functions will be useful:

Notice that the demo reformatted the Total Borrowed and Interest Rate after the user entered them and wrote them back to the text boxes.  You do not have to do this, but it is a nice touch.

The VB constant vbTab (tab character) and vbCrLf (carriage return/line feed) were also used in formatting output.

I recommend that you build a string to hold your amortization schedule data, and then output the string to the text box.  Why?  This is done to reduce the amount of overhead that is expended on input/output.  You can find an example of this here.

Your numbers need to reconcile with the demo (which reconciles to P1).  If they don't you have a rounding error, generally caused by an incorrect data type.


Demo


Submit pseudocode or an algorithm for this program.


If you are uncomfortable with the financial functions provided by VB, you can use the formulas from program 1, with a few additions:

totalPayments = loanDuration * paymentsPerYear ' Gets the number of total payments
decIntPerYr = percentInterest/100 ' Gets decimal interest per year
decIntPerPayment = decIntPerYr / paymentsPerYear ' Gets decimal interest per payment
factor = (1 - (1 + decIntPerPayment) ^ (-totalPayments)) / decIntPerPayment
monthlyPayment = loanAmount / factor ' Gets the amount to be paid for each payment, or paySize


' in loop
monthlyInterestPayment = decIntPerPayment * newBalance
monthlyPrincipalPayment = monthlyPayment - monthlyInterestPayment
newBalance = newBalance - monthlyPrincipalPayment