Need formula for total value of a commercial lease
 

I think I'm pretty good with Excel but I couldn't come up with what
looked like a pretty easy fomula to me. I could get close but never
the right answer.

Here is the set up:
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 - the annual rent for the first year.
D3: 10 years - term of lease
E3: 5% - this is the percentage that the lease amount
increases at the interval stated below. This should be compounded.
F3: 2 years - this is the interval at which the increase happens. In
this example, the first increase happens after 2 years (starts in year
3) and the next increases happen 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 for any help!

Need formula for total value of a commercial lease
 

See answers in your other post.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
ps.com...
I think I'm pretty good with Excel but I couldn't come up with what
looked like a pretty easy fomula to me. I could get close but never
the right answer.

Here is the set up:
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 - the annual rent for the first year.
D3: 10 years - term of lease
E3: 5% - this is the percentage that the lease amount
increases at the interval stated below. This should be compounded.
F3: 2 years - this is the interval at which the increase happens. In
this example, the first increase happens after 2 years (starts in year
3) and the next increases happen 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 for any help!