Need to solve for the total value of a commercial lease

[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 ???

 

[Bernard Liengme]

"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

 

[JasonConroy]

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!

 

[Tyro]

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)

 

[Dana DeLouis]

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

 

[JasonConroy]

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?

 

[Dana DeLouis]

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)

 

[JasonConroy]

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.

 

[Dana DeLouis]

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