Excel Rent formula

[awm786]

Hello,

I am trying to put together a revenue sheet for rent from a commercial property with different starting and ending lease dates. The problem is shown below. How do I calculate the different rental rates for each year in one cell?

 

[HighAndWilder]

This data really needs to be normalised to enable you to add to it without creating more columns and changing the formula.

Create a worksheet for the raw data and have one for the summary.

For each period have a separate row.

Columns

Tenant ID
Start Date
End Date
Monthly Rental

Then you can use the standard SUMIF and SUMIFS Excel Worksheet functions.

 

[awm786]

Thank You!

 

[BSALV]

Map1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2							2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032
3	4/06/2022	4/06/2025	1.000	4/07/2025	4/07/2028	1.250	0	0	7.000	12.000	12.000	13.500	15.000	15.000	8.750	0	0	0	0
4	3/05/2019	3/05/2022	1.500	3/06/2022	3/06/2025	1.875	18.000	18.000	20.625	22.500	22.500	11.250	0	0	0	0	0	0	0
Blad1

Cell Formulas
Range	Formula
H3:T4	H3	=MAX(0,MIN((H$2)*12+13,YEAR($C3)*12+MONTH($C3)+1)-MAX(H$2*12+1,YEAR($B3)*12+MONTH($B3)))*$D3 +MAX(0,MIN((H$2)*12+13,YEAR($F3)*12+MONTH($F3)+1)-MAX(H$2*12+1,YEAR($E3)*12+MONTH($E3)))*$G3

 

[awm786]

Map1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2							2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032
3	4/06/2022	4/06/2025	1.000	4/07/2025	4/07/2028	1.250	0	0	7.000	12.000	12.000	13.500	15.000	15.000	8.750	0	0	0	0
4	3/05/2019	3/05/2022	1.500	3/06/2022	3/06/2025	1.875	18.000	18.000	20.625	22.500	22.500	11.250	0	0	0	0	0	0	0
Blad1

Cell Formulas
Range	Formula
H3:T4	H3	=MAX(0,MIN((H$2)*12+13,YEAR($C3)*12+MONTH($C3)+1)-MAX(H$2*12+1,YEAR($B3)*12+MONTH($B3)))*$D3 +MAX(0,MIN((H$2)*12+13,YEAR($F3)*12+MONTH($F3)+1)-MAX(H$2*12+1,YEAR($E3)*12+MONTH($E3)))*$G3

Thank you so very much! You are a life saver!

 

[HighAndWilder]

Do you ever have more than two sets of start , end, rent data for each tenant?

 

[awm786]

You could potentially if your were to project so far into the future that you would need to assume a third/fourth ect contract renewal.