Create a Loan Amortization Schedule with PMI and Extra Payments in WPS Spreadsheets
Question details
Create a monthly loan amortization table that includes scheduled principal and interest, private mortgage insurance (PMI), payment changes, and optional extra principal payments.
- Product
- WPS Spreadsheets or Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to model a mortgage or other loan over time and see how PMI and extra payments affect the ending balance.
- Observed behavior
- The worksheet should calculate each month's interest, principal, PMI, total payment, and remaining balance, with PMI ending when the balance reaches the selected equity threshold.
Gather the original loan amount, annual interest rate, loan term, PMI rate or monthly PMI amount, and the balance at which PMI ends. Decide whether extra payments are applied entirely to principal, as they commonly are for mortgage loans.
Build a monthly amortization table with PMI and extra-principal columns
Use this method to create a flexible schedule in which each monthly row calculates the balance after the scheduled payment and any optional extra payment.
Set up loan inputs above the schedule, such as annual interest rate, term in years, original balance, PMI annual rate, and the PMI cancellation balance. A common cancellation balance is 80% of the original loan amount, but use the terms provided by the lender.
Use a separate Extra Payment column so you can enter a different additional principal amount for any month without changing the scheduled loan payment formula.
In cells B2:B7, enter labels and values for Annual Interest Rate, Loan Term in Years, Original Loan Amount, PMI Annual Rate, PMI Cancellation Balance, and Monthly Payment. In the Monthly Payment cell, enter =-PMT(B2/12,B3*12,B4) to calculate the standard principal-and-interest payment.
Starting in row 10, create columns for Payment Number, Beginning Balance, Scheduled Payment, Interest, Scheduled Principal, PMI, Extra Payment, Total Payment, and Ending Balance.
In the first payment row, set Beginning Balance equal to the Original Loan Amount input. Set Scheduled Payment equal to the Monthly Payment input. Calculate Interest with =BeginningBalance*$B$2/12 and Scheduled Principal with =MAX(0,ScheduledPayment-Interest). Calculate PMI with =IF(BeginningBalance>$B$6,BeginningBalance*$B$5/12,0). Enter any optional amount in the Extra Payment cell, then calculate Total Payment as =ScheduledPayment+PMI+ExtraPayment and Ending Balance as =MAX(0,BeginningBalance-ScheduledPrincipal-ExtraPayment).
In the next row, set Beginning Balance equal to the previous row's Ending Balance. Copy the payment, interest, principal, PMI, total payment, and ending balance formulas down for the number of months in the loan term.
For a payment change in a specific month, replace that row's Scheduled Payment formula with the new scheduled payment amount. Keep the Interest formula based on that row's Beginning Balance, and let Scheduled Principal remain =MAX(0,ScheduledPayment-Interest) so the balance continues to calculate correctly.
Use PMT, IPMT, and PPMT for a fixed-payment baseline schedule
Use this approach when the scheduled principal-and-interest payment remains constant and you want to use dedicated financial functions for each payment period.
Create and customize a PMI loan amortization schedule in WPS Spreadsheets
WPS Spreadsheets provides PMT, IPMT, PPMT, IF, and other formulas needed to calculate a loan schedule. Create a monthly table, enter PMI rules and extra payments, then fill formulas down to track the balance through payoff.
- 1. Open a blank spreadsheet: Launch WPS Office, select Spreadsheets, and choose Blank to start a new loan calculation workbook.
- 2. Enter loan assumptions: Add cells for the loan amount, annual rate, term, PMI rate, and PMI cancellation balance at the top of the worksheet.
- 3. Create the amortization columns: Add monthly columns for beginning balance, payment, interest, principal, PMI, extra payment, total payment, and ending balance.
- 4. Add formulas and fill down: Enter PMT, IF, interest, principal, and ending-balance formulas in the first payment row, then drag the fill handle down through the required number of months.
Frequently Asked Questions
How do I calculate the monthly loan payment before PMI?
Use the PMT function for the principal-and-interest payment: =-PMT(annual_rate/12,term_years*12,loan_amount). PMI should be added separately because it is not part of the standard PMT calculation.
How can I stop PMI when the loan reaches 80% loan-to-value?
Enter 80% of the original loan amount in a PMI Cancellation Balance cell. In the PMI column, use an IF formula such as =IF(BeginningBalance>CancellationBalance,BeginningBalance*PMIRate/12,0) so PMI becomes zero once the balance reaches that amount.
Should an extra mortgage payment reduce interest or principal?
An optional extra payment is usually applied to principal. In the ending-balance formula, subtract both Scheduled Principal and Extra Payment from Beginning Balance; the lower balance then reduces interest in later months.
What should I do if my scheduled payment changes during the loan term?
Replace the Scheduled Payment formula in the affected monthly row with the new payment amount. Calculate that row's principal as Scheduled Payment minus Interest, then continue the next row from the newly calculated Ending Balance.
Can WPS Spreadsheets open an Excel loan amortization template?
Yes. WPS Spreadsheets can open and edit .xlsx files and supports common financial functions including PMT, IPMT, and PPMT. Check formula references after opening any template with custom named ranges or lender-specific rules.
