Hi, I work in commercial real estate and we often need to calculate EFFECTIVE RENT which is basically an average rent over the life of a lease, which takes into consideration free rent (represented as months free), which, for this purpose, will always be assumed to come out of the first year's rent.
When calculating effective rent, I usually use a spreadsheet which I have attached below.
That being said, I was wondering if any of you know how to create a formula which calculates a simple effective rent (start rate (in months, per SF) x square feet x annual increases x lease term (in months) - free rent (in months) = total consideration (the total rent paid over the term of the lease). Then, the consideration is divided by the SF and term to get the effective rent) that will fit in a single cell in excel?
I’m not sure such a formula is possible, but I thought I’d ask you guys. It will be very complicated with conditional/If-Then statements, I would think.
This is the sheet I’m using as a starting point (rents are quoted monthly here):
Here is the typical sheet we use: View attachment Effective Rent Calculator.xls
When calculating effective rent, I usually use a spreadsheet which I have attached below.
That being said, I was wondering if any of you know how to create a formula which calculates a simple effective rent (start rate (in months, per SF) x square feet x annual increases x lease term (in months) - free rent (in months) = total consideration (the total rent paid over the term of the lease). Then, the consideration is divided by the SF and term to get the effective rent) that will fit in a single cell in excel?
I’m not sure such a formula is possible, but I thought I’d ask you guys. It will be very complicated with conditional/If-Then statements, I would think.
This is the sheet I’m using as a starting point (rents are quoted monthly here):
START RENT (monthly) | FREE RENT (months) | SF | INCREASES (annual) | TERM (in months) | EFFECTIVE RATE (in monthly rent) | TOTAL CONSIDERATION |
$ 3.00 | 1 | 64,000 | 3% | 120 | $ ? | $ ? |
$ 2.85 | 1 | 3,796 | 4% | 36 | $ | $ |
$ 2.30 | 1 | 7,814 | 4% | 38 | $ | $ |
$ 2.85 | 1 | 2,835 | 3% | 30 | $ | $ |
$ 1.65 | 1 | 22,867 | 3% | 60 | $ | $ |
$ 2.35 | 1 | 7,000 | 4% | 30 | $ | $ |
Here is the typical sheet we use: View attachment Effective Rent Calculator.xls