Can I make this spill?

[Gregg Wolin]

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.

 

[Excel Wizard]

Please try

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

 

[Gregg Wolin]

Please try

=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?

 

[Peter Bartholomew]

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

= 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!)

 

[Peter Bartholomew]

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.

= 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.

 

[Gregg Wolin]

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.

= 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)?

 

[Peter Bartholomew]

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!

= 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)
   ))