How to change the Interest Rate

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:

  1. Monthly Escrow changes
  2. Interest Rate changes

Enter a new Interest Rate

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

Int Rate & Escrow Changes worksheet showing the initial interest rate and where to enter interest rate changes
Example on the Int Rate & Escrow Changes worksheet where the interest rate has been reduced from 7% to 6%

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.

Example showing blank payment amount and Late Charge due to not enough payments being entered yet

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.

Enter dummy Payments on Record Payments so that the new interest rate and Late Charge amount are shown on the Int Rate & Escrow Changes worksheet

You can now see the new payment amount and late fee shown.

Int Rate & Escrow Changes worksheet showing the Payment Amount and Late Charge amount after dummy payments added
Late Charges change too

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.

Late Charges change too when the payment amount changes, e.g. when the interest rate changes

Let Us Help

Get in touch if you’re having problems, need something specific or have questions about our spreadsheet.

Claim FREE upgrade

Existing customers of Lender Software Pro v1.7.x claim a FREE upgrade to v2.0.x

© 2023 Testament Research, Inc.

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.

By continuing to browse or by clicking 'Dismiss' you agree to the storing of cookies on your device to enhance your site experience and for analytical purposes. To learn more about how we use cookies, please see our Cookies Policy and Privacy Policy.