Loan Amortization: Interest, Principal and the Balloon
Compute the monthly payment from the amortization period, split it into interest and principal in the first and the last month of the term, follow the interest, principal and balance year by year, and see the balloon left at maturity.
Licensed under CC BY 4.0. Share and adapt it, commercially too, as long as you credit Globalbiz CRE Academy. What is open and what is not: License.
Learning objective
Learners will compute the monthly payment on a commercial loan from its amortization period and split it into interest and principal in the first and last month of the term. They will follow the interest, principal and balance year by year and determine the balloon balance due at maturity.
This is the complete model, not a sample or a preview. Every step, input, formula and download on this page works on its own, and nothing here requires an account or a paid subscription.
1. The Loan
The four facts that set every payment: how much, at what rate, over what schedule, and for how long.
The principal borrowed at closing.
Annual note rate, fixed for the whole term.
Years over which the payments would fully repay the loan. Sets the size of each monthly payment. At least 10, so it is never shorter than the term.
Whole years until the loan matures and the remaining balance falls due, from 1 to 10. Years after maturity carry zero.
This model runs in your browser. No spreadsheet software of any kind is required. The .xlsx download is optional, and it opens at no cost in LibreOffice Calc and in Google Sheets.
Download as Excel (.xlsx)
Every download uses exactly the numbers on screen, and the cell addresses are the same in all three files. Values are stored at full precision and only displayed rounded.