This is perfect!Please try
=F5*(1+G5)^INT(YEARFRAC(EOMONTH(+J4#,-1),C5))*(J4#>C5)*(J4#<=E5)
= SCAN(0, periodEnd#, LAMBDA(acc,p,
IF(AND(p>=Start,p<=End),IF(MONTH(p)=Month,IF(acc>0,1+Amount,1),1)*IF(acc<>0,acc,Rate),0)
))
= SCAN(0, periodEnd#, LAMBDA(acc,p,
IF(AND(p>=startDate,p<=endDate),
IF(MONTH(p)=escMonth,IF(acc>0,1+rate,1),1)*IF(acc<>0,acc,amount),
0)
))
Follow-Up Question:I have just noticed that the Amount and Rate annotation was switched so when they got used in the defined names the resulting formula was somewhat incomprehensible, despite giving the same results.
What is difficult with these array formulas is to generate results for additional leases without using relative referencing. The correct answer should be an array of arrays but modern Excel will not accept them in order to maintain compatibility with legacy Excel.Code:= SCAN(0, periodEnd#, LAMBDA(acc,p, IF(AND(p>=startDate,p<=endDate), IF(MONTH(p)=escMonth,IF(acc>0,1+rate,1),1)*IF(acc<>0,acc,amount), 0) ))
= SCAN(0, periodEnd#, LAMBDA(acc,p,
LET(
active?, AND(p>=startDate,p<=endDate),
escalate?, IF(active?, MOD(DATEDIF(startDate,p,"m"),escMonth)=0),
newAmt, IF(active?,
IF(acc=0, amount, IF(escalate?,acc*(1+rate), acc)),
0),
newAmt)
))