# Rent Bumps

#### chet645

##### Board Regular
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

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
ABC
1EscalationPeriod12months
2StartDate3/23/2010
3RefFlag1
Sheet1

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.

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.

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
ABC
1EscalationPeriod12months
2Term5years
3StartDate3/23/2007
4Segment4.00
5RefFlag1
Input 1

<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"))

Last edited:
Hi,

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

got #NUM!

Biz

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!

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")

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

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

Last edited:

Replies
0
Views
124
Replies
8
Views
367
Replies
1
Views
373
Replies
3
Views
1K
Replies
3
Views
413

### Forum statistics

1,203,127
Messages
6,053,667
Members
444,677
Latest member
LWilliamsAtchristies

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back