excel-lambda-PMT.DATES.txt

/*
PMT.DATES

Creates a series of dates for a payment schedule.

Inputs:
  - start_date - the starting date of the payment term (typically the date the first payment is due)
  - term_years - the number of years over which the payment must be made
  - period_months - the number of months between each payment
  - endpoint_offset - OPTIONAL - the number of periods to include before the first payment date and after the last payment date

*/


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,1,MONTH(_osd)+1,_pm),
        0
      ),
    _s
  )
);