Rent Bumps

[chet645]

I am putting together a cash flow schedule for a lease in a retail building. Per the lease, the tenant's rent escalates every 60 months. What I want is to create a reference in excel where every 60 months the formula will return a 1, otherwise the formula returns a 0. Does anyone know of an efficient way to do this? It would be especially helpful if the formula is dynamic enough to work when the escalation month changes (i.e. from every 60 mos. to every 12 mos.)?

Your help would be much appreciated.

Bryce

 

[Biz]

Hi,

I played around and derived the formula below for B4


=IF(TODAY()<EDATE(B3,(B1*B2)),ROUNDUP(B1-((B1*B2)-DATEDIF(TODAY(),EDATE(B3,(B1*B2)),"M")) p 12,0),?Lease Expired?)<>
Biz

=IF(TODAY()<EDATE(B3,(B1*B2)),ROUNDUP(B1-((B1*B2)-DATEDIF(TODAY(),EDATE(B3,(B1*B2)),"m"))/12,0),"Lease Expired")

 

[Biz]

Hi,

Having problems posting formula. Please try link below
Biz

 

[Biz]

Sorry I am having problems with normal formula post.

Please find try the link below:
http://www.megaupload.com/?d=UHP2KYNO

Biz

 

[jasonb75]

The formula works for me and the table in post #5 shows that it gives a result, so it's something you're doing wrong, if you're trying to do something similar try starting your own thread with some information about what you're doing.

The main aim of this thread is to answer chet645's question, to which an answer has been provided which I will revise if needed, as I'm sure will JoeMo if chet645 indicates preference the solution they offered instead.

 

[Biz]

I tried your formula and it does not work in Excel 2003.
=DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2)))),"m")

Are you using Excel 2007 or 2010?


Biz

 

[jasonb75]

I'm using 2007 but that's irrelevant, the formula is compatible with 2003, maybe it's just not compatible with what you're trying to do.

The formula works for me and the table in post #5 shows that it gives a result, so it's something you're doing wrong, if you're trying to do something similar try starting your own thread with some information about what you're doing.

The main aim of this thread is to answer chet645's question, to which an answer has been provided which I will revise if needed, as I'm sure will JoeMo if chet645 indicates preference the solution they offered instead.