# Excel Rent formula

#### awm786

##### New Member
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?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

Thank You!

Map1
BCDEFGHIJKLMNOPQRST
22020202120222023202420252026202720282029203020312032
34/06/20224/06/20251.0004/07/20254/07/20281.250007.00012.00012.00013.50015.00015.0008.7500000
43/05/20193/05/20221.5003/06/20223/06/20251.87518.00018.00020.62522.50022.50011.2500000000
Cell Formulas
RangeFormula
H3:T4H3=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

Map1
BCDEFGHIJKLMNOPQRST
22020202120222023202420252026202720282029203020312032
34/06/20224/06/20251.0004/07/20254/07/20281.250007.00012.00012.00013.50015.00015.0008.7500000
43/05/20193/05/20221.5003/06/20223/06/20251.87518.00018.00020.62522.50022.50011.2500000000
Cell Formulas
RangeFormula
H3:T4H3=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!

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

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.

Replies
5
Views
272
Replies
1
Views
115
Replies
2
Views
160
Replies
0
Views
125
Replies
10
Views
332

1,203,249
Messages
6,054,378
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back