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