This scenario is for when MORE than the Correct Payment is received.
Any payment that is received that is for more than the amount required to clear the month's interest will automatically be applied to Principal.
In this scenario, the Normal Payment including Escrow should be $917.29, but the borrower actually sent $1,000.00.
Click on the Record Payments worksheet.
In our example, the first payment is due March 1 2021.
We received this payment on March 8 2021. Enter 3/8/21 in Column B.
Note that you can now see the Amount Due in Column C as soon as the date is entered into Column B.
The Amount Due is shown as $929.17 which includes $200.00 to escrow and is the normal payment amount. But, you received more than the total amount due including escrow, as you received $1,000.00.
Enter $1,000.00 in Column E.
Now enter the Total to Apply this month in Column M which is $1,000.00.
You can also enter the Check Number in Column H and the Cleared Date in Column I.
As soon as you Apply the Payment, you will notice that in Column F it says that $70.83 extra to principal has been applied to Principal. This is because you have applied MORE than the usual payment.
You will also note that the $200.00 escrow payment has been Applied to Escrow in Column N and the Total in Escrow is shown in Column O.
You have now entered your payment!
To check how this payment has been applied, scroll across to the right to Columns V, W and X which show you how the $1,000.00 payment has been applied.
What happens next month?
Our example is an Interest Only loan and you have now made a payment to Principal in the first payment. This now changes the following payments, as the amount of Interest each month is now less, as the Principal is now Less than before.
To understand how this works, enter another normal payment for the usual amount of $929.17.
Enter the payment as shown below with a Received Date of 4/1/2021 in Column B and you will notice that the amount due is still $929.17 as that is the Usual Payment Amount.
To check how this payment has been applied, scroll across to the right to Columns V, W and X which show you how the payment AFTER the larger payment to Principal has been applied.
Because the loan was Interest Only, first look at Column AA and you will see that the Principal Balance at the start of the 2nd payment period is $124,929.17, as $70.83 was applied from the previous payment.
The interest due for the 2nd payment you just entered is based on the Principal Balance from the previous payment, which means that slightly less interest is now due.
Column W - To Interest
The Interest now due is $728.75 and NOT $729.17
Column X - To Principal
Because slightly less interest was due, you can see that $0.42 has now been applied to Principal as that is the difference.
What if my loan is NOT Interest Only
If your loan is Amortizing and you apply a larger payment, then more will go to Principal, which will alter future payments as the Principal remaining is less. This is automatically calculated for you.
Get in touch if you’re having problems, need something specific or have questions about our spreadsheet.
Existing customers of Lender Software Pro v1.7.x claim a FREE upgrade to v2.0.x
Get started for FREE Today. Register and Download NOW!
Want to Upgrade from Lite to PRO?. Upgrade and Unlock for $79.99
Microsoft® Windows® and Microsoft® Excel® are registered trademarks of Microsoft Corporation in the United States and other countries. Mac® and macOS® are trademarks of Apple Inc., registered in the U.S. and other countries.