Once you have created your master template, you're ready to create your first loan.
You don’t need to calculate balloon payments, pre-paid interest or last payment dates. It is all done for you!
The images shown below are taken from the filled-in example Mortgage Sample v2.0.xlsx.
As this is the template file you should complete all the relevant boxes in the LENDER DETAILS section on the Setup worksheet.
The example shows the Lender's details as well as where to enter payment addresses and bank information.
The last section of Lender Details is ONLY for if you will be receiving a direct bank deposit for your monthly payment and you want to reflect that on your Payment Coupons. Leave this section blank if you do not require it.
Learn to how add direct deposit information to your Payment Coupons.
Open mortgage.xlsx which should be in the folder you created.
Click on the Setup worksheet.
Enter the borrower name, address etc. into the light blue cells. It is very important to enter the cells correctly. Hover your mouse over the cells with a red corner in the top right to show helpful comments.
Here you enter the borrower details, their names, addresses, social security numbers etc.
Enter the company name if there is one in cell B24.
You can alter the salutation for the letter templates in Cell B25. You may want to be formal and say ‘Dear Mr. and Mrs. Smith’ or perhaps ‘Dear John and Jane’.
Enter the property address.
If you enter the property value in Cell B38, the Loan to Value (LTV) is shown. You can update this at anytime by over-writing the date and changing the property value. You do not need to enter a value if you don't want to show the LTV.
The Loan Details section includes everything you need to setup your loan. The example below is fully completed for a loan of $125,000.00 originated on January 5 2021, with a loan term of 15 years interest only. The Loan Summary section on the left in grey and is automatically calculated based on the figures you enter in the blue boxes on the left and middle columns.
We will now look at each section within the Loan Details individually.
Firstly, enter your basic loan details.
In our example the loan is originated on Jan 5 2021, the interest rate is 7% and there are 180 months (15 years) before the balloon is due. The amount of the loan is $125,000.00.
Loan Origination Date
This spreadsheet is designed to automatically calculate pre-paid interest based on you receiving payments on the 1st of the month, as is USA mortgage standard. Thus if you originate a mortgage on January 15, 2021, the first payment is due on March 1, 2021 and you collect 27 days pre-paid interest at closing. Enter your Date of Loan in cell B41.
Enter the Initial Annual Interest rate in cell: B43. You can alter the interest rate in the future on the int Rate & Escrow Changes worksheet. The one defined here as the initial rate should never be changed. Learn how to change the Interest Rate.
Enter the Amount of Loan in cell D41.
Overwrite First Due Date
The first due date is calculated as Mar 1 2021 in our example, but you can overwrite this by entering a date in the Overwrite first due date box in cell D42, but only fill this in if you need to change it. Some private lenders originate a mortgage mid-month, and collect their payments on the SAME day the following month. If this is the case, then this applies to you.
Months Before Balloon
Enter how many months before the balloon or term of loan in cell B45. E.g. 360 for 30 years and 180 for 15 yr. etc.
The next section configures Fees and Charges and Amortization Months.
In our example, there is a grace period of 10 days before a payment is late, the late charge is 5.00% and there is a bad check charge of $25.00.
The Amortization months is set to 5000 which means the loan is interest only. You can adjust this to whatever your loan is.
Days before late
Enter how many days before a payment is late in cell B47. In our example it is 10 days, so on the 11th day the payment would be late.
Late Charge Percent or Late Charge daily rate ($)
For the next section you have a choice of EITHER calculating a late fee based on a percentage of the monthly payment OR a daily amount for every day during the payment month that a payment is late.
Late Charge Percent: In our example, we have set the late fee to be 5.00% in cell B48 of the initial payment amount.
Late Charge Daily Rate
If you want to use a daily rate, clear the 5.00% in cell B48 and instead insert a number into cell D48. You will receive a warning if you haven't cleared cell B48 first as you CANNOT have both a % charge and a daily charge.
For example, you could charge a $10 fee per day for any late payments or whatever your agreed legal amount is. For example, if your grace period is 10 days and you received your payment on the 12th of the month, there would be a late fee of 2x your daily fee for being 2 days late.
Learn more about Daily Late Charge.
Enter the amortization period in Cell B50. This would be 360 for 30 yr. amortization, 180 for 15 yr. etc. and enter 5000 for interest only mortgages.
If the loan is fully amortizing, there is no balloon. Then enter the same number in Cell B45 that you entered in Cell B50 for the amortization months.
Bad check Charge
Enter your fee for a returned 'bad' check in cell D47. In our example it is $25.00.
Days in year
Enter either 365 or 360 in cell D50 depending on your loan.
Points Paid %
If you are charging any points enter them in cell D51. If you are not, leave this cell blank or enter zero.
Origination Fee %
If you are charging any points for loan origination enter them in cell D52. If you are not, leave this cell blank or enter zero.
Price Paid for Note
If you bought the mortgage at a discount enter the price you paid in cell B53. Leave blank if this is not relevant.
Other Finance Charges
If you have any other finance charges that are applicable, enter them in cell D53.
If you are planning on collecting escrow on a monthly basis, enter the initial amount in cell B56. If you are not collecting any escrow then leave this section blank.
If you are planning on collecting escrow with your monthly payment, then enter an escrow deposit in cell B57 if there is one. If not leave as zero.
If you need to alter the amount escrowed each month, you change that on the Int Rate & Escrow Changes worksheet. Only the initial escrow amount is set on the Setup worksheet and that should not be changed in the future. Learn how to change the escrow payment.
Enter Loan Reference
If you have a loan reference number, you can enter it in cell B59. It is optional to have a loan reference and it will not be shown on any reports if it is blank.
The last section is the calculated Loan Summary section.
You will see that the Monthly Payment in our example is calculated as $729.17 per month and that the first payment is due on March 1 2021.
The Balloon amount is shown as $125,000.00 as this is an interest only mortgage.
You will also see the Total Payments term of loan and other information on the amount financed etc.
In our example, the loan origination date was January 5 2021 which means there were 27 days between the origination date and the end of January 2021. We automatically calculate that you are owed prepaid interest for 27 days and that the amount in our example is $647.19. Learn how to enter Prepaid Interest.
Your pre-paid interest should be shown on your closing statement and should match what we have calculated for you.
No Prepaid Interest
The above prepaid interest treatment is standard for USA lenders. However some lenders may have originated mortgages where the payments start exactly one month later and there is NO pre-paid interest.
Example: Mortgage funded January 15, 2021, first payment February 15, 2021.
To correctly handle this, just enter the first payment due date in cell D42 to override the first due date on the Setup worksheet. This will now adjust every future payment due date.
Annual Percentage Rate (APR) is a calculation used by lenders that is designed to help borrowers compare different loan options. Learn about APR and how we calculate it.
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.