WPS Spreadsheet: Create an Amortization Schedule for a Promissory Note
Question details
The user needs to create an amortization schedule for a promissory note funded through two separate disbursements and repaid through interest-only payments, monthly payments, and a final balloon payment.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Track a $500,000 advance on October 13, 2023, a $1,500,000 advance on October 17, 2023, interest-only payments from November 2023 through March 2024, $100,000 monthly payments starting in April 2024, and maturity on October 12, 2025.
- Observed behavior
- The schedule needs to calculate interest, principal reduction, remaining balance, and the final payment while accounting for the different advance dates and the day-count method required by the note.
Confirm the note’s annual interest rate, payment dates, final maturity date, and day-count convention before entering formulas. Decide whether the $100,000 installments are total payments or principal payments, because that changes the principal and interest calculations.
Build a promissory note amortization schedule in WPS Spreadsheet
Use this method to track both advances, calculate interest and principal for each payment, and determine the final balloon amount at maturity.
Create one row for every advance, payment, and maturity event, and sort the rows by transaction date. This ensures the balance reflects when each amount was actually funded.
For a monthly convention, calculate interest as the applicable outstanding principal multiplied by 12% divided by 12. If the note requires actual-day accrual, replace that calculation with the note’s day-count formula, such as principal × annual rate × days ÷ 360 or 365.
Open WPS Spreadsheet and create columns for Date, Payment Type, New Advance, Payment Amount, Opening Principal, Interest, Principal Paid, and Ending Principal. Format Date as a date and the monetary columns as currency.
In the schedule, enter October 13, 2023 with a New Advance of 500000 and October 17, 2023 with a New Advance of 1500000. Label both rows as Advance and leave Payment Amount at zero.
Enter the note’s payment dates from November 2023 through March 2024 and label them Interest-only. In the Interest column, use a formula such as =OpeningPrincipal*12%/12 when the monthly method applies; set Principal Paid to 0 and set Payment Amount equal to the calculated interest.
Beginning with the April 2024 payment row, enter 100000 in Payment Amount if that figure is the total scheduled payment. Calculate Principal Paid with =MAX(0,PaymentAmount-Interest), then calculate Ending Principal with =OpeningPrincipal+NewAdvance-PrincipalPaid. Copy the formulas through the remaining payment dates.
On the October 12, 2025 maturity row, calculate the final amount as the remaining principal plus any accrued interest, for example =EndingPrincipal+Interest. Verify that the ending principal becomes zero after the balloon payment.
Create and calculate the promissory note schedule in WPS Spreadsheet
WPS Spreadsheet provides formulas, date handling, currency formatting, and XLSX compatibility for building a clear amortization schedule with separate advances, interest-only periods, regular payments, and a final balloon payment.
- 1. Open WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and choose Blank Workbook to start the schedule.
- 2. Set up the transaction table: Enter columns for Date, Payment Type, New Advance, Payment Amount, Opening Principal, Interest, Principal Paid, and Ending Principal.
- 3. Enter advances and payments: Add the October 13 and October 17 advances, the November through March interest-only rows, the $100,000 payments beginning in April 2024, and the October 12, 2025 maturity row.
- 4. Add and copy formulas: Use cell formulas to calculate interest, principal paid, and ending principal, then drag the formulas down through each transaction row.
Frequently Asked Questions
How should the two promissory note advances appear in the schedule?
Enter each advance as a separate dated row: 500000 on October 13, 2023 and 1500000 on October 17, 2023. Add both amounts to the principal balance before calculating later payment rows.
How do I calculate monthly interest at 12% in WPS Spreadsheet?
For a monthly convention, select the Interest cell and enter a formula such as =OpeningPrincipal*12%/12, replacing OpeningPrincipal with the cell reference for that row. Use the note’s required day-count formula instead if interest accrues by actual days.
How should interest-only payments be represented?
Enter the calculated interest as the Payment Amount, set Principal Paid to zero, and carry the full principal balance into the next row. This applies to the interest-only period from November 2023 through March 2024.
How is the final balloon payment calculated?
On the maturity row, add the remaining principal to any interest accrued for the final period. A formula such as =EndingPrincipal+Interest can be used when the balance and interest cells refer to that row.
What if the $100,000 payments are principal-only rather than total payments?
Change the schedule logic so Principal Paid equals 100000 and Payment Amount equals Principal Paid plus Interest. Confirm the payment definition in the promissory note before finalizing the workbook.
