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:

    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
    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
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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,
    Eli

  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
  •