For a Excel Genius...Step up rents, accurately (!)

[ps65]

Guys

I have an incredibly complicated situation I need to model, and I cannot make my formula accurate or a reasonable in length.
I believe the solution may be in using an array, but I can’t seem to solve it.

Here’s the problem.

I am modelling 2 tenancies – imagine a single office, where one tenant may end their lease, and a new tenant then takes occupation.

If the tenancy is between or equal to the dates of the cash flow, you will receive rent (this should be clear from my IF/AND/OR logical statements).
I have created a formula which does this nicely, and apportions the rents too. No problems here…

But…
Some tenants may have fixed uplifts. So, every 6 months, the base rent may change from £10,000, to £12,500. Later in their tenancy, it may change to £13,500.

I am modelling the base rent separately and as per above, this works fine. I now want to model the uplifts OVER and ABOVE the base rent to the exact amount, including the correct apportionment. So, if rent goes from 10,000 to 12,500, you would get the additional 2,500 show up. But, it also needs to show the correct uplift if the uplift was to occur half way through a month (so, imagine the rent went from 10,000 to 15,000 in the middle of January, I should get half of January at 10,000 and another half at 15,000). In addition, it still needs to be wrapped up in the contraints of the tenancy (ie. if the tenancy ends, then no base rent or uplift should be received after expiry).

The attached spreadsheet should make it clear and also show my attempt, but would welcome an excel genius to get this working correctly (as maybe in a cleaner fashion)?

Thanks!