Calculation Issues

Calculate Loan Interest with a Balloon Payment in Excel

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Calculate monthly payments and interest for a loan with a balance due at the end of its term.

Calculate Loan Interest with Monthly Payments and a Balloon Payment in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Calculate the monthly payment

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.

2
Set up the schedule

In cells A1 through F1, enter Month, Opening Balance, Interest, Payment, Principal, and Closing Balance.

3
Enter the first month

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.

4
Fill the remaining months

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.

5
Total the interest

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.

Build a Monthly Balloon Loan Amortization Schedule in Excel
Balloon payment and rounding: After the 120th regular payment, the closing balance should be approximately $20,000. Pay that balloon amount separately to clear the loan. If actual payments are rounded to cents, adjust the final regular payment or balloon amount for any rounding difference.
Calculate in WPS Spreadsheets

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. 1. Open a workbook: Open WPS Spreadsheets and select a blank workbook.
  2. 2. Calculate the payment: In an empty cell, enter =PMT(5%/12,120,-100000,20000) to calculate the regular monthly payment.
  3. 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. 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.
Calculate monthly payments and interest with spreadsheet formulasTrack the balloon balance across the loan termOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface for free
QA img-9

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.