Pro Rate Rental Property

[Drvortex]

Hello Everyone,

I've come across a challenging problem to solve and unable to successfully put this together. I'll do my best to explain this.

The date fields I have are:

A2 = Start Date of Lease
B2 = End Date of Lease
C2 = Rent
D2= Daily Rate based on 30 days (I may need to adjust based on Feb being 28 or 29 days)

G2:G13 = EOM calculation [=EOMONTH(DATE($I$2,H2,1),0)]
H2:H13 = Month #s from 1 to 12
I3 = Year

I'm attempting to calculate what is owed for the tenant.

For example, the tenant moves in 1/1/19 and leaves on 1/15/20.

Additional information is that each calendar year is a separate workbook dated for the year.

So...what I did was the following and it works; however, I need to figure out how to combine these equations to one or maybe do the calculations elsewhere and then choose which one to post in appropriate cell.

The equation for pro rated rent based on start date. The answer is 1000 because it is way before 1/31/20. The equation below also determines if the calculation is over 1000, just put down 1000.
=IF((($G$2-$A$2)*$D$2)<0,0,IF((($G$2-$A$2)*$D$2)>$C$2,$C$2,($G$2-$A$2)*$D$2))

The equation for pro rated rent based on end date. The answer is 466.6667 because it is halfway through the month of Jan 2020 and based on a 33.3333 daily rate.
=IF((($G$2-$B$2)*$D$2)<0,0,IF((($G$2-$B$2)*$D$2)>$C$2,$C$2,C2-($G$2-$B$2)*$D$2))

My question is how can I combine those two equations into one or do I need to calculate the pro rate for start/end date then choose one? I'm not sure how to even ask this question.

I want to calculate the rent being charged based on start date and end date. If the start date is in another year, then the focus will be on the end date. If the end date is "blank", then the rent is the max amount. If the start date is in the middle of the year, say June, I want to ensure that row will not be charged rent from Jan - May. Then from June going forward, enter the rent amount being charged. Each month is represented in separate tabs. Does this make sense? Any guidance/assistance would be great.

Thanks,
Jason

 

[jasonb75]

Not sure if I'm following correctly, my results are different to yours, but this is how I've resolved similar queries previously.

Book123.xlsm
	A	B	C	D	E	F	G	H	I
2	01/01/2019	15/01/2020	1000			483.87	31/01/2020	1	2020
3						0.00	29/02/2020	2
4						0.00	31/03/2020	3
5						0.00	30/04/2020	4
6						0.00	31/05/2020	5
7						0.00	30/06/2020	6
8						0.00	31/07/2020	7
9						0.00	31/08/2020	8
10						0.00	30/09/2020	9
11						0.00	31/10/2020	10
12						0.00	30/11/2020	11
13						0.00	31/12/2020	12
Sheet19

Cell Formulas
Range	Formula
F2:F13	F2	=$C$2/DAY(G2)*MAX(0,MIN($B$2,G2)-MAX($A$2,EOMONTH(G2,-1)+1)+1)
G2:G13	G2	=EOMONTH(DATE($I$2,H2,1),0)

 

[Drvortex]

Looks good. I will try it but I noticed you have the day first then month. I would like it to be month/day/year. I could see if this works though. Thanks so much for quick reply.

 

[jasonb75]

Dates have to be entered in the correct format to match your regional settings otherwise there will be errors in the formulas, for me that is UK format of dd/mm/yyyy
If you use US settings then you will need to enter your dates in mm/dd/yyyy format.