# Thread: Hello! Calculating Effective Rent using Excel

1. ## Hello! Calculating Effective Rent using Excel

Register for a FREE account, and/
or Log in to avoid these ads!

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  Reply With Quote

2. 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```  Reply With Quote

3. 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.   Reply With Quote

4. My pleasure!

Have a great day,
Eli  Reply With Quote

5. nice solution  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•