rent escaltion

[shamsu203]

Hi
I am trying to put together a worksheet to track rent increase . the lease can be for any no of years . The escalation may be 12 ,36, 48 months interval.at say 10%. eg if the rent is $1000 start of lease date is 1-1-21, increase % 10%, increase interval is 36 months ,. then on 1-1-24 (36months from the lease date,) the worksheet should show rent increase date and new rent which would be 1100, then on 1-1-27 the new rent increase date will be shown as 1-1-27 and rent as 1210 so on until expiry of the lease agreement. ANy help will be appreciated
Regards
Shamsu

 

[johnnyL]

Here is a quick version I whipped up that can be expanded upon:

Book2
	A	B	C	D	E	F	G
1	Rent Increase
2			Increase every 3 years
3			10%
4	Lease Date	12 month	36 month	48 month	60 month	72 month
5	1/1/2021		1/1/2024			1/1/2027
6	$1,000.00		$1,100.00			$1,210.00
7
Sheet1

Cell Formulas
Range	Formula
C5	C5	=DATE(YEAR(A5) + 3, MONTH(A5), DAY(A5))
C6	C6	= A6 + A6 * C3
F5	F5	=DATE(YEAR(A5) + 6, MONTH(A5), DAY(A5))
F6	F6	= C6 + C6 * C3

 

[shamsu203]

Here is a quick version I whipped up that can be expanded upon:

Book2
	A	B	C	D	E	F	G
1	Rent Increase
2			Increase every 3 years
3			10%
4	Lease Date	12 month	36 month	48 month	60 month	72 month
5	1/1/2021		1/1/2024			1/1/2027
6	$1,000.00		$1,100.00			$1,210.00
7
Sheet1

Cell Formulas
Range	Formula
C5	C5	=DATE(YEAR(A5) + 3, MONTH(A5), DAY(A5))
C6	C6	= A6 + A6 * C3
F5	F5	=DATE(YEAR(A5) + 6, MONTH(A5), DAY(A5))
F6	F6	= C6 + C6 * C3

Here is a quick version I whipped up that can be expanded upon:

Book2
	A	B	C	D	E	F	G
1	Rent Increase
2			Increase every 3 years
3			10%
4	Lease Date	12 month	36 month	48 month	60 month	72 month
5	1/1/2021		1/1/2024			1/1/2027
6	$1,000.00		$1,100.00			$1,210.00
7
Sheet1

Cell Formulas
Range	Formula
C5	C5	=DATE(YEAR(A5) + 3, MONTH(A5), DAY(A5))
C6	C6	= A6 + A6 * C3
F5	F5	=DATE(YEAR(A5) + 6, MONTH(A5), DAY(A5))
F6	F6	= C6 + C6 * C3

hi
Tks for your help it works fine. my concern is that as the period increases the worksheet would be lagre. I want to know if the data in the c column could reflect the new date and amount that is when it is 72 months then c 4 should show 72 months and increase as the months increase and cell c5 and c6 will also be reflecting the new date and amount.
Regrds
Shamsu

 

[Dave Patton]

An example with Annual increases

Rent_Escalating1.xlsm
	A	B
1		Amount
2	1	1,000.00
3	2	1,000.00
4	3	1,000.00
5	4	1,000.00
6	5	1,000.00
7	6	1,000.00
8	7	1,000.00
9	8	1,000.00
10	9	1,000.00
11	10	1,000.00
12	11	1,000.00
13	12	1,000.00
14	13	1,100.00
Detail1

Cell Formulas
Range	Formula
B2	B2	=Amount
B3:B14	B3	=B2*(1+(MOD(A2,12)=0)*Escal)

Named Ranges
Name	Refers To	Cells
Amount	=Total!$B$4	B2