Rent Bumps

chet645

Board Regular
Joined
Nov 10, 2010
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

chet645

Board Regular
Joined
Nov 10, 2010
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABC
1EscalationPeriod12months
2Term5years
3StartDate3/23/2007
4Segment4.00
5RefFlag1
Input 1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,026
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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"))
 
Last edited:

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
Hi,

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

got #NUM!

Biz
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,026
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,026
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 18, 2009
Messages
1,677
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

Well-known Member
Joined
May 18, 2009
Messages
1,677
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top