Rental calculation for a list of shops

[fillofit]

Hi all, I am preparing a rent roll that listing all different shops with their respective rental rate in a particular time. sample is attached for easy understanding.

Many thanks in advance!

[bebo021999]

The rent fee in column I:J confuse me.
Start 2020-12-16 End 2020-12-15? (-1 days)
2018-07-20 vs 2018-09-19 (only 2 months?)
How do they participate in calculation?
It is better to input manually your expected results for at least 1 row.

[fillofit]

sorry for the confusion. it should be one month to 2021-01-15, the rent free refers to the period granted to the tenant for fitting out purpose, so it should be return to zero in these particular months. In reality, some sizeable shops may need longer fitting-out, so they can enjoy longer "rent free".

yes, i agree this could be input manually if this complicated the issue by a lot.

here's the revised file.

Many Thanks.

[bebo021999]

OK. I got it.
For 1st row (row 4) , the rent fee start from 16-Jan-21, right?
So, in K4, for Jan-21 period, is the rental for full month, or 16/31 of month? what is the math: $100 or $100*16/31?
We do need your manual calculation from some cells in row 4.

[fillofit]

OK. I got it.
For 1st row (row 4) , the rent fee start from 16-Jan-21, right?
So, in K4, for Jan-21 period, is the rental for full month, or 16/31 of month? what is the math: $100 or $100*16/31?
We do need your manual calculation from some cells in row 4.

Manual calculation mean some cells have to be update manually? No exception? Thanks

[Gregb11]

What he's asking is for YOU to enter what you EXPECT the answers to be in the sheet so we know that we are creating the right formula.

[fillofit]

Sorry for my misunderstanding. here's the revised file with my expected return.

Many Thanks !!!

[kvsrinivasamurthy]

In K4 then copied across and Down.

Please Login or Register  to view this content.

[josephteh]

Cell K4=$H4*MAX(0,MIN(MAX($G4+1,$J4),EOMONTH(K$3,0)+1)-MAX($F4,$J4+1,K$3))/DAY(EOMONTH(K$3,0))

[Gregb11]

@fillofit - I believe in K4 the answer should be 52, not 48, correct? 15 days in Jan are free, so you would pay for 16 days. 16/31*100=52 (it looks like you want to round to the nearest dollar)

[josephteh]

The example by fillofit contains a bit of an error. Tenant ABC Period 1 only got 30 days and ended exactly on the Rent Free period. So the rent for Jan-21 is based on Period 2 Rental of RM150. So, K4 should be 16/31*150=77 (or 77.42 in 2 decimal places).

And, given that subsequent periods may require tenant to renovate again and involving Rent Free period, it's better to show the monthly rental payable on the same row as the period.

Rental Calculation.png

[Gregb11]

@josephteh - yep, agree 100%.

[fillofit]

It is 99% perfect, but if the rent free period is not start on the same day as the respective lease start date (e.g. if an additional rent free was given to Tenant DEF in March 2021 during period 3), the respective rental from Jan to March will become nil. would it be possible to address this?

Thank you all the experts!!

[kvsrinivasamurthy]

In K4 and copied as required.

Please Login or Register  to view this content.

[josephteh]

Try this formula=$H4*MAX(0,MIN($G4,EOMONTH(K$3,0))-MAX($F4,K$3)+1-MAX(0,MIN(EOMONTH(K$3,0),$J4)-MAX($I4,K$3)+1))/DAY(EOMONTH(K$3,0))

[fillofit]

That's fantastic! Thank you for everyone!!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[fillofit]

When I further testing the formula, I found that if an additional rent free was add in the middle of the lease, the subsequent rental will become wrong, attached is the problem highlighted yellow.

[kvsrinivasamurthy]

IN K4 then copied

Please Login or Register  to view this content.

[josephteh]

Just found errors in my formula when rent-free period is blank. Will amend and post later.

[josephteh]

Amended formula=$H4*MAX(0,MIN($G4,EOMONTH(K$3,0))-MAX($F4,K$3)+1-MAX(0,MIN(EOMONTH(K$3,0),IF(ISBLANK($J4),0,$J4))-MAX(IF(ISBLANK($I4),0,$I4),K$3)+1))/DAY(EOMONTH(K$3,0))

[fillofit]

Thanks all. Problem solved!!

[josephteh]

If that takes care of your question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.