How to Create an Excel Loan Amortization Schedule with Extra Payments
Question details
The user needs to build a loan amortization schedule in Excel that correctly calculates balances when irregular, one-time extra principal payments are made.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking a loan payoff where the borrower makes random or one-time additional principal payments without treating them as recurring payments.
- Observed behavior
- The goal is a dynamic schedule that applies one-off additional payments to the principal balance accurately using functions like PMT, SUMIFS, and EDATE.
Gather your loan details, including the total principal amount, annual interest rate, number of payment periods, and the exact start date of the loan.
Build the Amortization Schedule with a Separate Extra Payments Table
Use a dedicated table to record irregular payments and apply them to the main schedule automatically using the SUMIFS function.
To properly handle irregular extra payments, it is best to separate the one-time payments from the main schedule. You can create an 'Extra Payments' table and use the SUMIFS function to pull those payments into the correct billing period on the main schedule based on the payment date.
In your worksheet, create cells for Principal, Interest Rate, Periods (in months), and Start Date. Calculate the regular monthly payment using the formula: =PMT(Interest_Rate/12, Periods, -Principal).
Create a separate small table with two columns: 'Payment Date' and 'Amount'. Whenever an irregular extra payment is made, enter the date and the amount in this table.
Create headers for the main amortization table: Date, Opening Balance, Scheduled Payment, Extra Payment, Interest, Principal Reduction, and Closing Balance.
In the Date column, generate the sequence of payment dates using the EDATE function. For example, use =EDATE(First_Date, SEQUENCE(Periods)) to automatically spill the dates down the column.
In the Extra Payment column, use SUMIFS to sum any extra payments made during that specific period: =SUMIFS(Extra_Amount_Range, Extra_Date_Range, ">="&EDATE(Current_Date,-1), Extra_Date_Range, "<="&Current_Date).
For each row, calculate Interest as Opening Balance * (Interest Rate / 12). Calculate Total Principal Reduction as (Scheduled Payment - Interest) + Extra Payment. The Closing Balance will be Opening Balance - Total Principal Reduction.
Create Dynamic Loan Schedules in WPS Spreadsheet
WPS Spreadsheet fully supports advanced financial functions like PMT, EDATE, and SUMIFS, making it easy to build dynamic loan amortization schedules. It handles array formulas and complex calculations smoothly, allowing you to track extra payments efficiently.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank workbook to set up your loan variables.
- 2. Input formulas: Use WPS's built-in formula auto-complete to insert the PMT, EDATE, and SUMIFS formulas accurately into your schedule.
- 3. Save and share: Save the file in .xlsx format to ensure seamless sharing and formatting compatibility with other spreadsheet users.
Frequently Asked Questions
How does an extra payment affect my loan amortization schedule?
An extra payment goes directly toward reducing the principal balance. This decreases the amount of interest calculated in subsequent periods, ultimately shortening the overall loan term and reducing the total interest paid.
Why use a separate table for irregular extra payments?
Irregular payments don't happen every period. Logging them in a separate table allows you to keep the main schedule clean. You can then use a SUMIFS formula to automatically pull the extra payment into the correct period on the main schedule based on the date.
What does the EDATE function do in the SUMIFS formula?
The EDATE function calculates a date a specified number of months before or after a starting date. In the SUMIFS formula, it helps establish a date range (e.g., the previous month up to the current payment date) so that any extra payments made within that specific billing cycle are summed together.
How do I handle the final payment if the balance drops below the regular payment?
Wrap your scheduled payment formula in an IF or MIN statement. For example, using =MIN(Regular_Payment, Opening_Balance + Interest) ensures you only charge the remaining balance plus interest, preventing the closing balance from going negative in the final period.
