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

 

[JoeMo]

In this example cell B1 is named EscalationPeriod and cell B2 is named StarDate. The values in these named ranges can be changed and the Reference Flag cell (B3) will adjust:

Excel Workbook
	A	B	C
1	EscalationPeriod	12	months
2	StartDate	3/23/2010
3	RefFlag	1
Sheet1

 

[chet645]

Thanks Joe Mo,

Can you think of an efficient way to make the formula work in the case that the lease has multiple escalations. For example, say it is a five year lease, with escalations every 12 months during the lease. The formula you gave is great but it only works for one escalation.

Your help is much appreciated!

 

[JoeMo]

Thanks Joe Mo,

Can you think of an efficient way to make the formula work in the case that the lease has multiple escalations. For example, say it is a five year lease, with escalations every 12 months during the lease. The formula you gave is great but it only works for one escalation.

Your help is much appreciated!

Maybe something like this. The Ref flag will be 1 whenever today is in the month of escalation, 0 otherwise unless today is more than the term (5 years in this example).

Excel Workbook
	A	B	C
1	EscalationPeriod	12	months
2	Term	5	years
3	StartDate	3/23/2007
4	Segment	4.00
5	RefFlag	1
Input 1

 

[jasonb75]

How about

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 210px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Escalation Period (Months)</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Term (Years)</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Start Date</TD><TD style="TEXT-ALIGN: right">23/03/2007</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Next Escalation Due In (months)</TD><TD style="TEXT-ALIGN: right">11</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4</TD><TD>=DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2)))),"m")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Counts down the months remaining until the next escalation, can also return the date of the next escalation by cutting it to

=LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2))))

Formula is dynamic with either result, updating to the next period when the existing one lapses.

As it stands the formula will return a #NUM! error if the lease has expired, could easily be wrapped to return "expired" or similar if it's of use.

i.e. =IFERROR(formula,"Expired")

To revert it to your original 0 / 1 request

=--NOT(DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2)))),"m"))

 

[Biz]

Hi,

I tried formula in B4
=DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2)))),"m")

got #NUM!

Biz

 

[jasonb75]

Hi,

I tried formula in B4
=DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&B2)))),"m")

got #NUM!

Biz

If you read the post where I provided that formula you would have seen

As it stands the formula will return a #NUM! error if the lease has expired, could easily be wrapped to return "expired" or similar if it's of use.

Meaning, based in the example 5 year term, if todays date is later than 5 years after the start date you will see #NUM!

 

[jasonb75]

One error I did notice in the formula was that it was moving the lease expiry point if the escalation period was anything other than 12 months.

Fixed this in the version below,

=DATEDIF(TODAY(),LOOKUP(EDATE(TODAY(),B1),EDATE(B3,B1*ROW(INDIRECT("1:"&(B2*(12/B1)))))),"m")

 

[Biz]

Hi Jason,

I don't believe formula new one or old is working at all as I change cell B3 23/03/2010 still I get #NUM!

Biz

 

[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"))/12,0),"Lease Expired")

Biz