Loan Payment Schedule and Balance Tracking Tool (Updated Oct 2022)
A childhood friend called me this week and asked for a favor. He is the lender on a private real estate loan and was wanting a way to personally service the loan. Namely, he needed a tool to issue monthly invoices, track the principal vs interest paid in each month, and calculate the current loan balance.
Now there are far more sophisticated ways to service a loan; hiring an escrow company for instance or purchasing loan servicing software. However, he was looking for a poor man’s solution (i.e. a DIY solution) and so I offered him the following option.
I built a quick loan payment and a balance tracking tool in Microsoft Excel (download below), and then referred him to a free cloud-based accounting software with the option to send recurring invoices. After finishing the Excel tool, I thought I’d share it with the A.CRE community.
How the Loan Payment Schedule and Balance Tracking Tool Works
The tool is built in Microsoft Excel. It includes two tabs plus a versions tab. The first tab, entitled ‘Actual’, is where all inputs are entered. The inputs are Starting Balance, Annual Interest Rate, Term (in months), Inv. Name, Loan Funding Date, and then the actual payments as they’re made each month.
Inputs are entered into the cells with blue background and blue font. The required payment each month (i.e. minimum payment) is calculated in cell D7.
The model uses a basic amortization schedule, assumes 30/360 interest calculation, and allows for a loan term of 360 periods. The tool assumes the loan is amortizing (no interest only), and automatically calculates the proportion of payment allocated to interest vs principal in each month.
The ‘Projected’ tab shows the expected payment schedule assuming the borrower pays the minimum payment on time each month. The loan fully amortizes as of the end of the loan term.
In essence, this tool is two basic amortization tables with a slight adjustment on the Actual tab to allow for tracking. If you spot an error or have an enhancement, you’d like me to make, please let me know.
Need a More Robust Amortization Table?
I recognize this tool is simplistic. Most commercial loans in real estate are not fully amortizing loans, and many loans have at least some interest-only period. This tool was built for a specific situation, which likely doesn’t match yours.
So, if you need a more robust amortization schedule, I suggest you check out the other amortization and debt models we’ve shared on A.CRE.
Download the Loan Payment Schedule and Balance Tracking Tool
To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Version Notes
Version 1.1
- Added new Version tab
- Fixed issue where in certain cases the ‘Current Balance’ formula was not calculating correctly
- Fixed issue where there was a ‘Starting Balance’ greater than 0 in period 0 on the Projected tab
- Changed number formatting to ‘Accounting’
- Removed blue background on input cells
- Set every ‘Actual’ date cell an input to allow user to enter the actual date when the payment was made
- Added ‘Loan Origination Date’ input to ‘Actual’ tab
- Added ‘Pmt #’ column to track number of payments, independent of dates
- Added ’30/360 Interest Calc’ and ‘Fully amortizing loan’ notes to the ‘Projected’ tab
- Renamed ‘Inv. Name’ to ‘Loan Name’ on the ‘Projected’ tab
- Renamed ‘Interest Rate’ to ‘Ann. Interest Rate’ on the ‘Projected’ tab for consistency
- Misc. formatting improvements
Version 1.0
- Initial release
