rent increase

[shamsu203]

I have in b5 an amount 1450 b3 start date 1-dec-21,b4 increase interval b5 %. i want to show in b 6 b7 b8 b9 the followingb6 to show the amount beforw the new rent b7 the date of increase b8 the increase interval b9 the new rent . any hlep will be appreciated
shamsu

29/12/2024
Current Rent	1450	Expected result
Start Date	01/04/2021	01/04/2022	1522.5
Rent Increase Interval	12	01/04/2023	1598.625
% increase	5%	01/04/2024	1678.556
Ren before new increase	1600	formula required
increase due date	01/04/2024	formula required
interveal no	no of years completed(3)
New Rent	rounded to 1680	formula required

 

[Kaper]

Basic formula is
=initial_val*(1+increase_percent)^number_of_periods

so for instance if in D8 there is 3 years*

=B2*(1+B5)^D8

this can be rounded to full tens with

=ROUND(B2*(1+B5)^D8,-1)

*) 3 years can be calculated either as:

=DATEDIF(B3,B7,"y")

or

=DATEDIF(B3,TODAY(),"y")

if the second approach (with TODAY() reference, not B7 - BTW. one can also refer to cell A1 where is yesterday date) was used B7 can be calculated as:

=DATE(YEAR(B3)+DATEDIF(B3,TODAY(),"y"),MONTH(B3),DAY(B3))

Book2
	A	B	C	D	E	F
1	29.12.2024
2	Current Rent	1450	Expected result
3	Start Date	01.04.2021	01.04.2022	1522,5
4	Rent Increase Interval	12	01.04.2023	1598,625
5	% increase	5%	01.04.2024	1678,556
6	Ren before new increase	1600	formula required	1600
7	increase due date	01.04.2024	formula required
8	interveal no	no of years completed(3)		3	or may be	3
9	New Rent	rounded to 1680	formula required	1680
Sheet1

Cell Formulas
Range	Formula
D3	D3	=B2*(1+B5)
D4	D4	=B2*(1+B5)^2
D5	D5	=B2*(1+B5)^3
D6	D6	=ROUND(B2*(1+B5)^(D8-1),-1)
B7	B7	=DATE(YEAR(B3)+DATEDIF(B3,TODAY(),"y"),MONTH(B3),DAY(B3))
F8	F8	=DATEDIF(B3,TODAY(),"y")
D8	D8	=DATEDIF(B3,B7,"y")
D9	D9	=ROUND(B2*(1+B5)^D8,-1)

 

[shamsu203]

Hi Kaper tks for your reply it works fine for rent increase after 12 months but when the interval in b 4 is changed to say 24 or any other interval months the amounts are not correct. the increase should take place after completion of the 24 months. so the intreval no will be 1 eg b3 is1-4-21 b4 is 24 then b7 will be 1-4-23 and the interval no will be 1 . pls suggest an amended formula
shams

 

[Kaper]

See formulas in B7 and below (and descriptions in column A):

Book1
	A	B
1
2	Current Rent	1450
3	Start Date	01.04.2021
4	Rent Increase Interval (months)	24
5	% increase	0,05
6
7	no of PERIODS completed	1
8	previous increase date	01.04.2023
9	Rent after this date	1520
10
11	increase due date	01.04.2025
12	New Rent after this increase	1600
Sheet1

Cell Formulas
Range	Formula
B7	B7	=INT(DATEDIF(B3,TODAY(),"m")/B4)
B8	B8	=DATE(YEAR(B3),MONTH(B3)+B4*B7,DAY(B3))
B9	B9	=ROUND(B2*(1+B5)^B7,-1)
B11	B11	=DATE(YEAR(B3),MONTH(B3)+B4*(B7+1),DAY(B3))
B12	B12	=ROUND(B2*(1+B5)^(B7+1),-1)

Note that B11 and B12 are almost the same formulas as B8 and B9. The only difference is number of periods (B7+1 or just B7) - see again the basic at the beginning of post #2

 

[shamsu203]

See formulas in B7 and below (and descriptions in column A):

Book1
	A	B
1
2	Current Rent	1450
3	Start Date	01.04.2021
4	Rent Increase Interval (months)	24
5	% increase	0,05
6
7	no of PERIODS completed	1
8	previous increase date	01.04.2023
9	Rent after this date	1520
10
11	increase due date	01.04.2025
12	New Rent after this increase	1600
Sheet1

Cell Formulas
Range	Formula
B7	B7	=INT(DATEDIF(B3,TODAY(),"m")/B4)
B8	B8	=DATE(YEAR(B3),MONTH(B3)+B4*B7,DAY(B3))
B9	B9	=ROUND(B2*(1+B5)^B7,-1)
B11	B11	=DATE(YEAR(B3),MONTH(B3)+B4*(B7+1),DAY(B3))
B12	B12	=ROUND(B2*(1+B5)^(B7+1),-1)

Note that B11 and B12 are almost the same formulas as B8 and B9. The only difference is number of periods (B7+1 or just B7) - see again the basic at the beginning of post #2

tks works just right. happy new year to you
Shamsu

 

[Kaper]

Glad we could help, and thanks for acknowledgement