ENTERING PAYMENTS, NON-PAYMENTS AND BAD CHECKS

The following can happen after you originate a mortgage:

  1. You receive the correct payment on time
  2. You receive the correct payment late including late charge
  3. You receive the usual payment late without late charge
  4. You receive a partial payment on time
  5. You receive a partial payment late
  6. You receive MORE than the correct payment
  7. You receive a LOT more than the correct payment
  8. You receive nothing
  9. You receive a bad check
  10. Catch-up payments

This software will correctly handle all the above scenarios, with a little guidance from you.

Only enter in the colored cells. Everything else is calculated.

Don’t worry if you don’t find the instructions below to be clear, there is a step-by-step  example of each of the scenarios.

ENTER A PAYMENT

Click on the Record Payments sheet.

When you receive a payment you should enter the date the payment was received in Column B. Dates should be entered like 10/09/12 and will appear like Oct-09-12.

You will see the payment expected for the current month in Column C AFTER you enter a date in Column B

or enter a date in Column F if no payment is received for that month. See below.

If the payment date is after the due date, the late charge is automatically added on.

If your mortgage provides for a monthly escrow payment, this is also included in the payment expected.

Enter the amount received in column D.

We recommend you enter the check number in Column G.

Then enter the payment to allocate to that month into Column I. This is almost always the same amount as the

Amount Received (Column D).

Once the check clears your bank account, enter the date it clears in Column H.

Tip: You are not required to enter the check number in Column G or the date it clears in Column H. However, we recommend you do to aid later queries and lost check problems.

ESCROW PAYMENTS

If you are escrowing for taxes and insurance the amount to Escrow will be automatically calculated and deducted as a prior expense.

You should plan on escrowing enough every month to have enough money in the account to pay the property tax and insurance bill when it is due. But be careful about escrowing too much. There are government regulations against escrowing too much and a partial refund may be needed.

The monthly amount to escrow will initially be set in Cell G41 on the Setup sheet. If it changes enter the date the change applies from and the new amount in the lilac boxes from Cell L42.

When you disburse money from the Escrow Account, enter the Date, Purpose and Amount in Columns L, M and N with a Lilac background on the Record Payments sheet.

SCENARIO 1: You receive the correct payment on time

First enter the Payment Received Date against the Sep-01-12 Due Date on the Record Payments sheet:

Situation 1 when the correct payment is received on time

We received this payment on September 8, 2012. Note the Amount Due is now filled in. Enter the same amount in the Amount Received Cell:

Situation 1: Enter the amount received for payment received on time

Since this is correct, we now enter the same amount in the Total to Apply Cell. Also the Check Number (if you wish):

You will note that the $200 escrow payment has been Applied to Escrow. (if you are not escrowing for Taxes and Insurance, nothing will appear here.)

Situation 1: Enter check number for payment cleared that arrived on time

The check clears on 9/15/12 and we recommend you enter the Cleared Date, but it is not essential.

Situation 1: Enter amount to apply for check received on time

You have now entered the payment.

LATE CHARGES AND UNDER-PAYMENTS

If the borrower pays late, a late charge is added automatically to the amount owed.

If the borrower pays less than owed, the difference is added to the Amount Due the following month (Column C) when you enter a date in Column B and/or F on the Record Payments sheet.

FORGIVE LATE CHARGE

We do not recommend forgiving late charges. However, if you wish to do so then change the Date Received in Column B to a date before the Late Payment date.

Example: You have a 15 day late period and received the payment on the 17th of December 2012.

Your spreadsheet will show a late charge. You want to forgive this as it is Christmas.

Enter the Date Received in Column B as 12/15/12, and no late charge will be added.

OVERRIDE LATE CHARGE

The late charge is displayed in Column Q. This cell has a complicated formula and you should usually never have any need to change it. The cells in this column have been unlocked, so you have the option to override the calculated amount.

WARNING: Once you overwrite Column Q’s late charge, the hidden formula will be deleted and you will NOT be able to put it back how it was. You would then have to enter the amount manually.

SCENARIO 2: You receive the correct payment late including late charge

Payment received late on 9/15/12 including the late charge.

Note the payment expected is increased by the amount of the Late Charge when you enter the Received Date.

Situation 2: Entering the correct payment received late including late fee

Since you received the correct amount, we now enter the same amount in the Total to Apply Cell. Also the Check Number (if you wish):

You will note that the $200 escrow payment has been Applied to Escrow. (if you are not escrowing for Taxes and Insurance, nothing will appear here.)

Situation 2: Applying the correct payment received late

SCENARIO 3: You receive the usual payment late without late charge

Payment received on 9/15/12 without the late charge.

Enter the Received Date. Note the payment expected is increased by the amount of the Late Charge. Since the borrower did NOT add the late charge, you enter what you did receive in the Amount Received.

Situation 3: Enter a late payment received late without the late charge

Now enter the Check Number (if you like) and the Total to Apply (this month). $200 was first deducted to Apply to Escrow.

We recommend you enter the Check Cleared Date later, but it is not essential to the workings.

Situation 3: Apply late payment received late without late charge

LATE NOTICE

If you wish to send a Late Notice for the unpaid Late Charge, just click on the Late Notice sheet. When you create your Late Notice, you should enter how many days they have to “pay up” in Cell H12 before you file a default.

SCENARIO 4: You receive a partial payment on time

Enter the Received Date and the Amount Received which is $1,451.36 and not $1,651.36 that was expected.

Situation 4: Enter partial payment received on time

Enter the Check Number (if you wish) and Total to Apply (this should never be more than the Amount Received).

Situation 4: Enter the check number for a partial payment

When you enter next month’s payment, the shortfall will be added to the Payment Due.

Situation 4: Apply the  partial payment received on time

SCENARIO 5: You receive a partial payment late

This is entered exactly the same way as the payment above (Situation 4) except that the Received Date is late and the Late Charge will be added on.

When a payment is next received, it is automatically applied first to the Accrued Interest/Late Charge and then to the Current Interest and Principal.

SCENARIO 6: You receive MORE than the correct payment

Any payment received above the amount required to clear the current month’s interest will automatically be applied to Principal.

If the payment received is greater than the payment shown as due in Column C. You will see a warning of this in Column E in Red text  AFTER you enter the amount in Column I. Example “200.00 applied to principal

You will usually want to just leave this to automatically apply to principal.

In the example below, the borrower over paid by $200 which you enter in the Amount Received.

Situation 6: Enter more than the expected payment

When you enter 1851.36 in the Total to Apply Cell, you will see a warning that $200.00 extra is being applied to principal.

Situation 6: Enter more than the expected amount to Principal

SCENARIO 7: You receive a LOT more than the correct payment

Perhaps a double payment to apply to two months.

In this case you should break the payment down into the various months. And then enter them in Column I.

Example: The monthly payment is $1,651.36. On September 6, 2012 you receive $3,302.72 to cover the September 1 and October 1 payments.

In Column B row for September 1, 2012, enter a Received Date of September 6, 2012, in Column D an amount of $3,302.72.

Situation 7: Enter a double payment

Then in Column I (of the same row) an Amount Cleared of $1651.36.

AND in Column B row for October 1, 2012, enter a Received Date of September 6, 2012 and in Column I (of the same row) an Amount Cleared of $1651.36. See mortgage sample and see comments by hovering your mouse over the cell.

Situation 7: Apply a double payment received

SCENARIO 8: You receive nothing

If NO payment is received, enter a date later than the LATE PAYMENT DUE date in the “Late Notice Date” cell in Column F.

Situation 8: Enter when no payment has been received

These cells have an Orange background. This will add on the accrued interest and late charge for the month. You can see that when the October payment is added, the interest is accrued from the missing September payment and Late Charge.

Situation 8: Apply no payment received

This will also automatically calculate your Late Notice (click on the Late Notice sheet).

SCENARIO 9: You receive a bad check

You will already have entered the payment. When you get the check back from your bank, clear the Received Date from Column B and the amounts from Columns D and I (if you have shown it as cleared).

This will add on the accrued interest and Late Charge.

Enter the word “bad” (without the “ “) in the Cleared Date Column G. This will automatically add the bad check charge to the Late Fee.

Situation 9: How to enter a bad check

IMPORTANT: For correct functioning, it is very important to EITHER enter a Date Received and Payment Received in each month or a date in Column F to indicate non-payment, or interest owed will not accrue.

SCENARIO 10: Catch-up Payments

Example: You receive a payment of $1,500 and usually this will be applied $1,000 to interest and $500 to principal.

However there is $400 of Accrued Interest owing. So $400 is automatically applied in the “Apply to Accrued Interest” Cell. This will clear the Accrued Interest and reduce the amount applied to Principal to $100.

INTEREST RATE CHANGES

Change the interest rate ONLY in the correct row of Columns L and M on the Setup sheet. This has an orange colored background.

Remember that interest is charged in arrears. This will correctly calculate interest for the following month.

Note changing the interest rate will also change it for future months. If you change the interest rate mid-month, there is automatic compensation for the days at the previous rate.

How to change the interest rate

ESCROW CHANGES

As time passes you may want to increase or decrease the amount you hold in escrow. You do this on the Lilac background cells in Columns L and M on the Setup sheet.

How to change the monthly escrow amount