Complex Schedule

When MORTGAGE2 PRO is first run the initial screen shows the calculator with default information in the first 12 calculator input boxes. You can change the information in the boxes to reflect different data. The default calculator information is convenient and time saving regarding retyping redundant information. Usually, most new mortgages are going to be for 25 or 30 years with monthly compounding or semi-annual compounding thus having that information already entered as default, saves you time.

Immediately below the calculator boxes is the amortization spreadsheet that displays the amortization schedule based upon the initial data in the calculator boxes, assuming all the payments are made on time and there are no changes.

HOW TO MAKE CHANGES

If there are any changes in the amortization schedule, they are done on the four coloured areas of the amortization spreadsheet schedule as shown below.

Once you make a single change or many changes in the amortization spreadsheet schedule the data in the calculator boxes is meaningless, therefore do not go back to the calculator boxes with the mouse! That is why the Spreadsheet interest is shown separate from the Calculator Interest. The Calculator interest only applies to the calculator.

If payment #3 was received two days late, you would double click on the date, and the Change Payment Date window would appear.

Change the date to May 20th 2001 and click on OK. If you wanted all subsequent dates to be on the 20th of the month you would also select the Change all Subsequent Dates before clicking on OK.

To make changes in any of the other coloured cells you put the cursor on the cell and left click. The cell will then have a fuzzy border around it shown as 665.30 to let you know you are on that cell. Type in the new value and press any arrow key or the Enter key and the immediate change is shown as a yellow colour and the entire amortization spreadsheet is instantly recalculated. The yellow colour for a changed cell make it easier to find changes on a schedule with a lot of changes.

The prepayment cells will allow a positive and negative prepayment. A normal prepayment is positive and is subtracted from the outstanding balance along with the principal portion of the blended payment at that payment date. A negative prepayment allows one to continue on with the schedule, as in a builders loan. Additional funds (as they are advanced) are added to the existing balance as negative prepayments and therefore you can continue on with the same amortization schedule.Change the date to May 20th 2001 and click on OK. If you wanted all subsequent dates to be on the 20th of the month you would also select the Change all Subsequent Dates before clicking on OK.

Inserting Extra Payments

Assume a $150,000 mortgage at 8% with monthly compounding, amortized for 30 years and monthly payments of $1,100.65 The Principal was given to you on 02/21/2001 (advance date) and thus the first monthly payment is due on 03/21/2001.

In this example we will assume the lender is using the 365 day year, exact day monthly interest factor method to calculate the interest, that is, .. the interest factor used to calculate the interest is directly related to the number of days since the last payment and/or calculation of interest. Some lenders use the 360 day method and interest for extra prepayments is prorated, therefore calculating different results. Both methods are handled by the program and you should be aware of the differences.

One Extra Payment

What if you planned on making a prepayment of $100 along with your regular 6th payment of $1,100.65 on 08/21/2001 (yellow line) but forgot, because your regular monthly payments were being taken from your account automatically. The next day (08/22/2001) you remembered and went down to the Bank and gave them a cheque for $100. How would you handle this extra payment.

Using your mouse, place the cursor anywhere on the line below the 08/21/2001 payment (burgundy colour)

and click on the yellow pencil icon on the Toolbar. An extra payment line will be inserted as shown below

Left double click on the 2nd 08/21/2001 payment (the one you inserted) and change it to 08/22/2001 as follows;

Click on the OK button and type 100 in the prepay column and you have the results below;

You will note it does not matter where you put the inserted payment of $100, that is, in the prepay column or the payment column (shown below)

the balance after the 8th payment is the same, $149,147.55.

By definition, any amount of money paid in excess of the interest is applied to reducing the principal owing. If the extra $100 would have been paid along with the regular 6th payment as initially planned, all of the $100 would be applied to reducing the principal, and interest for the next period would be based upon 31 days (08/21/2001 to 09/21/2001). Because the extra $100 is paid one day later interest is owed for one day ($32.63) until the extra $100 is received, however the interest for the next period is calculated on 30 days instead of 31 days.

Many Extra Payments

What if you had planned on making 3 extra payments in between 08/21/2001 and 09/21/2001.

08/22/2001 (one day later)

08/25/2001

09/05/2001

Place the cursor anywhere under the yellow line (as before) and click the yellow pencil on the tool bar three times and the result is below;

Now change the three extra 08/21/2001 dates to the appropriate dates as shown below;

32.63 + 97.92 + 359.59 + 524.58 = 1,014.72

Notice immediately above, that the total of the interest portions (light green) add up to $1,014.72, the same as above in (Screenshot 3), which they must. This is a result of using the exact day 365 day method.

Type in the three $100 prepayments in the prepay column and the results are below;

Top