Hello! Calculating Effective Rent using Excel

**gilbrock** · 2011-11-09, 12:20 AM

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):

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: Effective Rent Calculator.xls

**gilbrock** · 2011-11-09, 09:22 PM

Had someone create a macro for me that does it:

Code:

Sub Macro1()
'
' Macro1 Macro
'
Dim i As Integer
Dim j As Integer
Dim years As Variant
Dim yearsb As Long
Dim start_rent As Currency
Dim free_rent As Long
Dim square_feet As Long
Dim increases As Variant
Dim term As Long
Dim partial As Variant
 
 
For i = 289 To 459
start_rent = Cells(i, 1).Value
free_rent = Cells(i, 2).Value
square_feet = Cells(i, 3).Value
increases = Cells(i, 4).Value
term = Cells(i, 5).Value
years = term / 12
yearsb = WorksheetFunction.RoundUp(years, 0) - 1
rent = 0
 
    For j = 0 To yearsb
        rent = rent + (12 * start_rent * (1 + increases) ^ j)
  
 
    Next
partial = years - WorksheetFunction.RoundDown(years, 0)
 
        If partial > 0 Then
 
            rent = rent - (((1 - partial) * 12) * start_rent * (1 + increases) ^ yearsb)
 
        End If
       
        rent = rent - free_rent * start_rent
   
 
    Cells(i, 6).Value = (rent * square_feet)
    Cells(i, 7).Value = rent / term
   
 
 
Next
'
End Sub

**Ken Puls** · 2011-11-10, 05:24 PM

Hi there,

Sorry we coudln't help you out with this one in time, but I'm glad you got it sorted! I really appreciate you're looping back to let us know it is complete.

Thanks also for posting the solution.