Calculation Cash flow

[AaruJaan]

Hi Experts,
I'm looking formula for cashflow, there are 2 logic, as mentioned below. (file and screenshot attached)

1-LOGIC: (highlighted in yellow)
When new annual rent (B12) kicks in after years of current lease left (B15) expires, the new rent (B12) kicks in. In this example in Year2.
Whenever a new rent kicks in, a compounded growth formula factor is applied to the previous rent base (eg. G4 & G7) and kept constant for another 3 years (currently defined in B14)

2-LOGIC: (highlighted in green)
As gross rental is for the whole year, some years could be made up of a combo of old and new lease rents, For eg. In Year2, as old lease expires in 0.5 yrs, would need a weighted average of 0.5*previous annual rent + 0.5*new annual rents (eg. H4 & H7), Otherwise, constant implied growth rate * Base rent.

Thanks in Advance.
gul

[AaruJaan]

any expert can help?

[Pepe Le Mokko]

Be patient my friend. This forum is manned by volunteers working for FREE on their spare time. You cannot expect to have answers as fast as with commercial forums.
It is recommended to wait about 24 hrs before bumping.

[AaruJaan]

I know that

[AaruJaan]

I have put formula but that's not working.

[AaruJaan]

any expert?

[JeteMc]

This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
I4:I10 are populated using: =IF(MOD(SUM(I3,1),B$14)=0,3,MOD(SUM(I3,1),B$14))
J4:J10 are populated using: =IF(I4>I3,J3,J$3+3*COUNTIFS(I$4:I4,3))
Note that J3 is populated with the min exponent (1.5)
G4:G10 are populated using: =(1+$G$2)^J4
H3:H10 are populated using: =IF(I3=1,(F2*G2+F3*G3)/2,F3*G3)
Let us know if you have any questions.