excel-lambda-PMT.DATES – create a list of dates for a payment schedule in Excel

 The gist for this lambda function can be found here.

The goal

Inspired by Diarmuid Early‘s YT video “Debt 101” and Brent Allen‘s “Generating an Amortization Schedule” post on his blog, the goal here is

Simplify the creation of a series of dates to be used as payment dates for a debt instrument

This series of dates should:

1. start on or before the start date of the debt
2. finish at the end of or after the end of the term of the debt
3. separate each date by a parameterized number of months (the period)

A solution

Here’s a lambda function called PMT.DATES:

=LAMBDA(start_date,term_years,period_months,[endpoint_offset],
  LET(
    _rnd,LAMBDA(val,then,IF(NOT(ISNUMBER(val)),then,ROUND(val,0))),
    _sd,_rnd(start_date,NA()),
    _t,_rnd(term_years,NA()),
    _eo,IF(ISOMITTED(endpoint_offset),1,_rnd(endpoint_offset,1)),
    _pm,_rnd(period_months,3),
    _osd,EOMONTH(_sd,-(_pm*_eo)),
    _ppy,12/_pm,
    _s,DATE(
        YEAR(_osd+1),
        SEQUENCE(_t*_ppy+_eo*2,1,MONTH(_osd)+1,_pm),
        0
      ),
    _s
  )
)

The lambda function takes four parameters:

1. start_date – the starting date of the payment term (typically the date the first payment is due)
2. term_years – the number of years over which the payment must be made
3. period_months – the number of months between each payment
4. endpoint_offset – OPTIONAL – the number of periods to include before the first payment date and after the last payment date

This is how it works:

Let’s break it down

As usual, we use the LET function to define some names to use in the calculation. 

    _rnd,LAMBDA(val,then,IF(NOT(ISNUMBER(val)),then,ROUND(val,0))),
    _sd,_rnd(start_date,NA()),
    _t,_rnd(term_years,NA()),
    _eo,IF(ISOMITTED(endpoint_offset),1,_rnd(endpoint_offset,1)),
    _pm,_rnd(period_months,3),

• _rnd – this is an embedded LAMBDA function that we will use to apply error-handling logic. Put simply, we check if the value passed into the _rnd lambda is a number. If it isn’t, we return whatever the “then” parameter happens to be. If val is a number, then we round it to zero decimal places. Each of the parameters used in this calculation should be an integer. If for some reason the PMT.DATES function is called with a decimal, then we correct that here. 
• _sd – we use the _rnd function defined above to check if start_date is a number and if it isn’t, return the NA() error value. 
• _t – similarly, we check if term_years is a number and if it isn’t, return the NA() error value.
• _eo – first we check if a value has been passed to the optional paramter endpoint_offset. If it hasn’t (i.e. that parameter has been omitted), then we use a default value of 1. Otherwise, we use the _rnd LAMBDA to check if endpoint_offset is a number and return 1 if it isn’t. If it is a number, the logic inside the _rnd function is applied to round endpoint_offset to zero decimal places.
• _pm – apply _rnd to the period_months parameter and return a default value of 3 (quarterly) if period_months is not a number.

    _osd,EOMONTH(_sd,-(_pm*_eo)),
    _ppy,12/_pm,
    _s,DATE(
        YEAR(_osd+1),
        SEQUENCE(_t*_ppy+_eo*2,1,MONTH(_osd)+1,_pm),
        0
      ),
    _s
  )
)

• _osd – stands for “offset start date” – here we determine the actual start of the series of dates, when taking into account the endpoint_offset value and the period_months value. We use EOMONTH to subtract (period_months * endpoint_offset) months from the start_date.
• _ppy – the payments per year, which is just 12 / period_months 
• _s – here we are using the form DATE(year,SEQUENCE,day) to create the series of dates. 
  • The year is YEAR(_osd+1), which is to say the year of the day after the offset start date. We use the day after so we can use zero in the Day parameter of the DATE function to always get the correct “last day of the month” regardless of what year or month we’re in. In the example above, this evaluates to YEAR(2021-03-31 + 1) = YEAR(2021-04-01) = 2021
  • The month is SEQUENCE(_t * _ppy * _eo * 2, 1, MONTH(_osd) + 1, _pm). So:
    • Rows = _t * _ppy * _eo * 2 = term_years * payments_per_year + endpoint_offset * 2. For the example in the gif above, with a term of 20 years with 4 payments per year (period = 3) and an endpoint_offset of 1, this will create a sequence of 20 * 4 + 1 * 2 = 82 rows.
    • Columns = 1
    • Start number = MONTH(_osd) + 1 is the start number. If our “offset start date” is March 31st 2021, then MONTH(_osd) + 1 will be 4 (April)
    • Skip = _pm – we increment the sequence by period_months at a time. In the example above, this value is 3. So the SEQUENCE created will start from 4 and increment by 3 with each new item
  • The day is 0. This has the effect of going backward 1 day into the last day of the prior month. So, instead of the sequence {“2021-04-01″,”2021-07-01”,…}, we end up with the sequence {“2021-03-31”, “2021-06-30”, …}

Finally we return to the spreadsheet the variable _s, which contains the series of dates.

In summary

In this post we created a lambda function to simplify the creation of a series of dates for use as a payment schedule for a debt instrument. 

We used an embedded lambda as a way to handle invalid parameter values.

We embedded the SEQUENCE function inside the DATE function to create a list of dates.