Need to solve for the total value of a commercial lease

  • Thread starter Thread starter JasonConroy
  • Start date Start date
J

JasonConroy

I hope my grid below looks OK once it is posted. I need a formula to
solve for cell G3. I need a single cell formula to solve for the
total value of a commercial lease given the initial annual rent, the
rent increase percentage and how often (in years) that the rent is
increased. Any ideas?

A B C D
E F G
1
Total Value
2 Tenant SF $/SF Term (yrs) Increase
amount % Every
X years of Lease
3 Ross 30,000 7.00 10 5%
2 ???
 
"Grid" never work well. Please confirm:
B3: 30,000 Swiss francs is the annual lease payment
C3: 7.00 converts SF to $US
D3: Is term of lease (10 years)
E3: Is the amount the lease will increase by (5%) and this will be
compounded, or you add 5% of initial payment every X years?
F3: The lease payment will increase every x years (here every 2 years). So
in year 2 (or year 3 ?) the lease payment is 33,000? What is it in year 4 -

You want to know how much the lessee will part with over the life of the
lease?
Depending on how the questions are answered I get: 345,000SF, 347760SF,
375,000SF or 384621.3SF. Which (if any) is correct?


OR, (a bit more difficult) you want to know the present value of the lease?
In which case there are supplementary questions: (A) Is the payment made at
the start or the end of the year? (B) What value will be used for the bank
rate (the amount you could get on a savings deposit) during the life of the
lease?

best wishes
 
Thank you for your response!
SF in this case actually stands for square feet. Lets just say that
the annual rent for the first year is C3.
C3: $210,000 ($7*30,000).
D3: 10 years - Correct, term of lease
E3: 5% - Correct, this is the percentage that the lease amount
increases at the interval stated below. This should be compounded.
F3: 2 years - Correct, this is the interval at which the increase
happens, in this case every 2 years. In this example, the first
increase happens after 2 years (starts in year 3) and the next
increases start in years 5, 7, and 9.
G3: This is where I would like a formula that comes up with the total
*undiscounted* value of the lease. In this case the correct answer is
$2,320,765.

Here is the information that I am trying to capture in one cell:
Yr1 $210,000
Yr2 $210,000 0%incr
Yr3 $220,500 5%incr
Yr4 $220,500 0%incr
Yr5 $231,525 5%incr
Yr6 $231,525 0%incr
Yr7 $243,101 5%incr
Yr8 $243,101 0%incr
Yr9 $255,256 5%incr
Yr10 $255,256 0%incr
Total$2,320,765

I would like C3, D3, E3 and F3 to all be variables.
Thanks again for any help!
 
This array formula works for an increase in rent after 1 or 2 years. After
entering the formula, you must press Ctrl+Shift+Enter, not just enter.

G3: =(C3+SUM(C3*(1+E3)^ROW(INDIRECT("1:"&INT(D3/F3)-1))))*F3

I know Professor Liengme will come up with something better for other years.
I am no mathematician.

Tyro

P.S. (Tyro = beginner)
 
In this case the correct answer is $2,320,765.

Hi. Are you looking for something like this?

=(2*((ir + 1)^yr - 1)*s)/ir

where s = 210000, ir = 5%, and yr = 5.

Returns: $2,320,765
 
Thank you so much Dana!
That formula does indeed work for those variables and I imagine it
would in every case where each increase interval is fully captured in
the term of the lease. In this case, when I change the increase
interval to 3 years the formula produces a result that is $3,942.76
less than the matrix. Any ideas for how to adjust the formula
eliminate that variance?
 
Hi. Are you looking at 3 groups of 3 payments over 9 years, plus a single
$243,101 in year 10 for a total of 2,229,176 ? (10 Years stays constant)
 
Hi, Dana,
Yes, exactly as you described - $2,229,176.25 for 10 years. Ideally,
the formula would work with any lease term and increase interval or
piece thereof provided in the variables.
Thanks again.
 
Hi. I think this should work:

s: 210000 'Start Value
ir: 0.05 'Interest Rate
years: 10 'Years
Grp: 3 'Groups

Ng =INT(years/Grp) 'Number of Groups
Rg =years-Grp*Ng 'Remaining Group

Fx =Grp*(((ir + 1)^Ng - 1)*s)/ir + Rg * (1 + ir)^Ng*s

= $2,229,176
 
Back
Top