Can I make this spill?

Gregg Wolin

Member
See attached. I'd like the formula in the outlined row to spill like the date row that it refers to so they are all dynamic.

Attachments

• chandoo_leasebump-spill.xlsx
20.4 KB · Views: 10

=F5*(1+G5)^INT(YEARFRAC(EOMONTH(+J4#,-1),C5))*(J4#>C5)*(J4#<=E5)

Attachments

• chandoo_leasebump-spill.xlsx
20.8 KB · Views: 15

=F5*(1+G5)^INT(YEARFRAC(EOMONTH(+J4#,-1),C5))*(J4#>C5)*(J4#<=E5)
This is perfect!
I'm curious as to why your formula spills and mine didn't? What structure makes the magic work?

To perform the calculation as an escalation rather than by applying multiple factors using an exponent requires more than a tweak. Multiplying the previous element of an array by a factor normally creates a circular reference because the array is referencing itself. Your relative reference to cell I5 does not achieve anything within an array formula because the array formula is in cell J5 and every term would be calculated using I5 (it would not scan across). A formula that will perform the calculation is
Code:
``````= 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)
))``````
Apologies for removing your direct references. I understand them well enough to decode them one at a time and replace them by a defined names, but I cannot read a formula that contains them (my short-term memory is not up to it!)

Attachments

• chandoo_leasebump-spill.xlsx
23.5 KB · Views: 4
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.
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)
))``````
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.

Attachments

• chandoo_leasebump-spill.xlsx
23.5 KB · Views: 9
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.
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)
))``````
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.
Follow-Up Question:
In your SCAN formula, the escMonth sets up the model to escalate the rent during a certain month of the year. How would I modify it to deal with a case where there is an adjustment every 60 months (rather than on a specific month annually)?

It is a case of replacing the 'MONTH(p)=escMonth' which, by definition, has a periodicity of 12 months. I have based the new calculation on the number of months elapsed since the start date. The additional lines of code were to help me debug the code because I did not get it right the first time!
Code:
``````= 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)
))``````

Attachments

• chandoo_leasebump-spill (1).xlsx
23 KB · Views: 4