Spreadsheet: 221723d1363647549-how-to-automatically-populate-dates-and-reflect-increase-in-the-right-month-excel-question-some-more-answers
Sheet: Sheet1

 |  |  |  |  |  |  | 1ST Review |  |  |  | 2ND Review |  |  |  | 3RD Review |  |  |  | 4TH Review |  |  |  | 5TH Review |  |  |  |  | 2013/14 |  |  |  |  |  |  |  |  |  |  |  | 2014/15 |  |  |  |  |  |  |  |  |  |  |  | 2015/16 |  |  |  |  |  |  |  |  |  |  |  | 2016/17 |  |  |  |  |  |  |  |  |  |  |  | 2017/18
Tenant | Tenancy Type | NLA  Area m2 | Lease Commencement Date | Lease [NAME] | Current Rent |  | 1ST Review |  | 1ST Review Rent/Sqm | 1ST Review Monthly Rent | 2ND Review | 2ND Review Type | 2ND Review Rent/Sqm | 2ND Review Monthly Rent | 3RD Review | 3RD Review Type | 3RD Review Rent/Sqm | 3RD Review Monthly Rent | 4TH Review | 4TH Review Type | 4TH Review Rent/Sqm | 4TH Review Monthly Rent | 5TH Review | 5TH Review Type | 5TH Review Rent/Sqm | 5TH Review Monthly Rent |  | 2013-07-01 00:00:00 | 2013-08-01 00:00:00 | 2013-09-01 00:00:00 | 2013-10-01 00:00:00 | 2013-11-01 00:00:00 | 2013-12-01 00:00:00 | 2014-01-01 00:00:00 | 2014-02-01 00:00:00 | 2014-03-01 00:00:00 | 2014-04-01 00:00:00 | 2014-05-01 00:00:00 | 2014-06-01 00:00:00 | 2014-07-01 00:00:00 | 2014-08-01 00:00:00 | 2014-09-01 00:00:00 | 2014-10-01 00:00:00 | 2014-11-01 00:00:00 | 2014-12-01 00:00:00 | 2015-01-01 00:00:00 | 2015-02-01 00:00:00 | 2015-03-01 00:00:00 | 2015-04-01 00:00:00 | 2015-05-01 00:00:00 | 2015-06-01 00:00:00 | 2015-07-01 00:00:00 | 2015-08-01 00:00:00 | 2015-09-01 00:00:00 | 2015-10-01 00:00:00 | 2015-11-01 00:00:00 | 2015-12-01 00:00:00 | 2016-01-01 00:00:00 | 2016-02-01 00:00:00 | 2016-03-01 00:00:00 | 2016-04-01 00:00:00 | 2016-05-01 00:00:00 | 2016-06-01 00:00:00 | 2016-07-01 00:00:00 | 2016-08-01 00:00:00 | 2016-09-01 00:00:00 | 2016-10-01 00:00:00 | 2016-11-01 00:00:00 | 2016-12-01 00:00:00 | 2017-01-01 00:00:00 | 2017-02-01 00:00:00 | 2017-03-01 00:00:00 | 2017-04-01 00:00:00 | 2017-05-01 00:00:00 | 2017-06-01 00:00:00 | 2017-07-01 00:00:00 | 2017-08-01 00:00:00 | 2017-09-01 00:00:00 | 2017-10-01 00:00:00 | 2017-11-01 00:00:00 | 2017-12-01 00:00:00 | 2018-01-01 00:00:00 | 2018-02-01 00:00:00 | 2018-03-01 00:00:00 | 2018-04-01 00:00:00 | 2018-05-01 00:00:00 | 2018-06-01 00:00:00
Tenant A | OFF | 654.5 | 1993-11-01 00:00:00 | 2013-10-31 00:00:00 |  |  |  |  |  |  |  |  |  | 0 |  |  |  | 0 |  |  |  | 0 |  |  |  | 0
Speculative renewal A | OFF | 654.5 | 2014-05-01 00:00:00 | 2019-04-30 00:00:00 |  |  | 2014-05-01 00:00:00 |  | 637.5 |  |  | 0.04 | 663 |  |  | 0.04 | 689.52 | 37607.57 |  | 0.04 | 717.1008 | 39111.8728 |  | 0.04 | 745.784832 | 40676.347712
Tenant B | OFF | 268 | 2010-07-01 00:00:00 | 2013-06-30 00:00:00 |  |  |  |  | 0 |  |  |  |  |  |  |  |  | 0 |  |  |  | 0 |  |  |  | 0
Speculative renewal | OFF | 268 | 2013-07-01 00:00:00 | 2018-06-30 00:00:00 |  |  | 2013-07-01 00:00:00 |  | 637.5 |  |  | 0.04 | 663 |  |  | 0.04 | 689.52 | 15399.28 |  | 0.04 | 717.1008 | 16015.2512 |  | 0.04 | 745.784832 | 16655.861248
Tenant C | OFF | 1000 | 2008-01-01 00:00:00 | 2016-12-31 00:00:00 | 500 |  |  |  |  |  | 2014-01-01 00:00:00 | 0.04 |  |  |  | 0.04 |  |  | 2016-01-01 00:00:00 | 0.04 |  |  |  |  |  | 0
Speculative renewal | OFF | 1000 | 2016-01-01 00:00:00 | 2025-06-30 00:00:00 |  |  | 2015-07-01 00:00:00 |  | 680 |  |  | 0.04 | 707.2 |  |  | 0.04 | 735.488 | 61290.66666666666 |  | 0.04 | 764.9075200000001 | 63742.29333333334 |  | 0.04 | 795.5038208000001 | 66291.98506666668
 | I need to do the 5 years rent forecast for 100ish tenants. we assume on the anniversary day the rent will increase by 4% until the lease expiry date. What formula I could use to reflect the rent increase?
 | For example, tenant C has a fixed in crease on 01/01/2014 where the rent increased from 500/sqm to 520/sqm. What formula can calculate the rent on 500/sqm from Jun13-Dec13 and calculate the rent on 520/sqm from Jan14 until lease expiry?
 | Also currently I mannually populate the rent review date for each tenant. Is there any quick way to automatically populate the rent review date which is 5 years anniversary dates?
 |  |  |  |  |  |  |  | 0.04 |  |  |  | 0.04 |  |  |  | 0.04 |  |  |  | 0.04
Tenant A | OFF | 654.5 | 1993-11-01 00:00:00
Speculative renewal A | OFF | 654.5 | 2014-05-01 00:00:00
Tenant B | OFF | 268 | 2010-07-01 00:00:00
Speculative renewal | OFF | 268 | 2013-07-01 00:00:00
Tenant C | OFF | 1000 | 2008-01-01 00:00:00 |  | 500
Speculative renewal | OFF | 1000 | 2016-01-01 00:00:00
 |  |  |  |  | should this be