How to Generate Monthly Amortization Dates Using SEQUENCE and EDATE
Question details
The user needs to dynamically generate a list of monthly dates for a financial amortization schedule using dynamic array functions.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating an automated amortization or loan schedule that requires accurately spaced sequential monthly payment dates.
- Observed behavior
- The user is unsure how to correctly use the step argument in the SEQUENCE function to generate whole months, rather than relying on an inaccurate fixed daily step like 30.437.
Ensure you have the initial loan date and the total number of payment periods entered into distinct reference cells in your spreadsheet before building the formula.
Use SEQUENCE with EDATE for Standard Monthly Intervals
Combine EDATE and SEQUENCE to accurately add whole months to an initial date, properly handling the varying number of days in each calendar month.
The SEQUENCE function can generate the payment numbers (1, 2, 3, etc.), while EDATE treats those numbers as the exact number of months to advance from the starting date.
Locate the cell containing your starting date (e.g., D5) and the cell with the total number of periods/payments (e.g., H3).
Select the empty cell where your schedule should start and input the formula: =EDATE($D$5, SEQUENCE($H$3,,1,1)).
Press Enter. The formula will automatically spill down the column, creating an array of sequence values stepped by exactly one month.
Highlight the generated numbers, right-click, select 'Format Cells', navigate to the 'Number' tab, and choose 'Date' to display them correctly.
Force All Payment Dates to a Specific Day of the Month
Use this method if your amortization schedule requires every payment to fall on a strict monthly deadline, such as the 15th, regardless of the initial loan date.
Easily Manage Financial Schedules with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like SEQUENCE and EDATE, allowing you to build complex financial models and amortization schedules effortlessly with zero compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank workbook or open your existing financial template.
- 2. Input the loan parameters: Enter your loan start date, interest rate, and term length into dedicated cells.
- 3. Apply dynamic array formulas: Use the =EDATE() and =SEQUENCE() formula combination to instantly populate your entire schedule of payment dates.
- 4. Save and share: Save your document in the standard .xlsx format to share easily with colleagues or clients.
Frequently Asked Questions
Why are my formula results showing as 5-digit numbers instead of dates?
Spreadsheets store dates as sequential serial numbers to make calculations easier. To display them normally, select the cells, right-click, choose 'Format Cells', and pick your preferred 'Date' format.
What does the '1, 1' mean at the end of the SEQUENCE function?
The syntax is =SEQUENCE(rows, columns, start, step). The first '1' tells the function to start counting from 1. The second '1' tells it to increment the count by exactly 1 for each new row. This ensures EDATE correctly adds 1 month, then 2 months, then 3 months, and so on.
Can I use this formula if my software doesn't support dynamic arrays?
No, the SEQUENCE function relies on dynamic array spilling. If you are using an older version of your spreadsheet software, you will get an error. Instead, enter the first date manually, use =EDATE(previous_cell, 1) in the cell directly below it, and drag the fill handle down.
