Calculate Loan Interest with a Balloon Payment in Excel
Question details
Calculate monthly payments and interest for a loan with a balance due at the end of its term.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A $100,000 loan has a 5% annual interest rate, a 10-year term, monthly payments, and a $20,000 balloon balance.
- Observed behavior
- The desired schedule shows each month's interest and principal, annual interest totals, and the remaining balloon balance after the last regular payment.
Confirm the loan amount, annual rate, payment frequency, term, and balloon amount. This example assumes a fixed nominal annual rate, payments at the end of each month, and no fees.
Build a Monthly Balloon Loan Amortization Schedule in Excel
Use PMT to calculate the regular payment, then track interest and the remaining balance for each month.
In an empty cell, enter =PMT(5%/12,10*12,-100000,20000). The result is approximately $931.86 per month, displayed to two decimal places.
In cells A1 through F1, enter Month, Opening Balance, Interest, Payment, Principal, and Closing Balance.
Enter 1 in A2 and 100000 in B2. Enter =B2*5%/12 in C2, =PMT(5%/12,120,-100000,20000) in D2, =D2-C2 in E2, and =B2-E2 in F2.
Enter =A2+1 in A3 and =F2 in B3. Copy the formulas from C2:F2 into C3:F3, then fill A3:F3 down through row 121 to complete 120 months.
Enter =SUM(C2:C13) in an empty cell for the first year's interest. Sum each subsequent block of 12 interest cells for later years, or use =SUM(C2:C121) for the full term.

Create a Balloon Loan Schedule in WPS Spreadsheets
Use PMT and a monthly amortization table in WPS Spreadsheets to calculate payments, interest, and the remaining balloon balance.
- 1. Open a workbook: Open WPS Spreadsheets and select a blank workbook.
- 2. Calculate the payment: In an empty cell, enter =PMT(5%/12,120,-100000,20000) to calculate the regular monthly payment.
- 3. Create the schedule: Add Month, Opening Balance, Interest, Payment, Principal, and Closing Balance headers. For month 1, calculate interest as the opening balance multiplied by 5%/12, principal as payment minus interest, and closing balance as opening balance minus principal.
- 4. Fill and total the schedule: Use the first closing balance as the next opening balance, then fill the formulas down through month 120. Use SUM on the interest column to calculate yearly or total interest.

Frequently Asked Questions
Why is the monthly rate 5% divided by 12?
The example assumes a nominal annual rate with monthly periods. Divide 5% by 12 to match the rate to the monthly payment frequency.
Does the $931.86 payment include the balloon payment?
No. It is the approximate regular monthly payment. After 120 regular payments, the remaining balance is approximately $20,000 and must be paid separately.
How do I calculate interest for a specific year?
Use SUM on the 12 monthly interest cells for that year. For example, if interest starts in C2, enter =SUM(C2:C13) for year one and =SUM(C14:C25) for year two.
Why is my final balance slightly different from $20,000?
Rounding each actual payment to cents can change the final balance. Check whether the schedule uses the full PMT result or a rounded payment, and adjust the final payment or balloon amount as needed.




