In Lender Spreadsheet it is really easy to change the interest rate after you have originated the loan. It is also easy to change the amount of escrow.
For example, you agree a new interest rate with your borrower as part of some forbearance agreement.
For escrow, perhaps property taxes have increased and you need to escrow more each month to ensure sufficient funds to pay them.
All you have to do is go to the Int Rate & Escrow Changes worksheet and you will see there are two sections:
Firstly, to change the interest rate, look in columns D and E which are colored Blue and are editable.
All you have to do is enter a date for when you want your interest rate change to be effective and the new interest rate in columns D and E.
In our example, we have decided to reduce the interest rate from 7.00% to 6.00% effective from December 1 2021.
Enter a date of 11/1/2021 in Cell D8
Enter the interest rate of 6.00% in Cell E8
The reason 11/1/21 was added as the date is due to the payment being in arrears and therefore, in order to have the payment effective from 12/1/21 you need to enter the month before.
The new payment amount is shown as $625.00 per month and will be automatically due for the Dec 1 2021 payment onwards.
In order to see what the new payment amount should be, you will need to add 'dummy' payments temporarily up to and including the date you entered, which in our example is 11/1/21.
Click on the Record Payments worksheet.
Simply add a date into Column G for all unrecorded payments up to and including the 11/1/21 payment and then return to the int Rate & Escrow Changes worksheet and your new payment amount will be displayed.
In the example below, the dates 9/1/21 to 11/1/21 were entered in Column G for the 3 future payments. This tells the spreadsheet that a payment has been accrued and as it now knows what the Nov 1 2020 payment was, it can calculate the change from that date.
You can now see the new payment amount and late fee shown.
When you change the interest rate, if you have specified a % of the monthly payment as a late charge, when the payment changes the late fee will also be automatically recalculated.
In our example, the late charge which was set as 5% of the payment amount on the Setup worksheet has changed to $31.25 instead of $36.46 to take account of the reduced payment amount.
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.