Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active June 15, 2023 09:41
Show Gist options
  • Save ncalm/df3ff859473bea58c3160aac693eddf7 to your computer and use it in GitHub Desktop.
Save ncalm/df3ff859473bea58c3160aac693eddf7 to your computer and use it in GitHub Desktop.
/*
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
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment