Results 1 to 5 of 5

Thread: Hello! Calculating Effective Rent using Excel

  1. #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?

    Im not sure such a formula is possible, but I thought Id ask you guys. It will be very complicated with conditional/If-Then statements, I would think.

    This is the sheet Im 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

  2. #2
    Had someone create a macro for me that does it:

    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)
    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
    End Sub
    Last edited by Ken Puls; 2011-11-10 at 06:23 PM. Reason: Added code tags

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    My pleasure!

    Have a great day,

  5. #5
    nice solution

Posting Permissions

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