shorting formula

shamsu203

Board Regular
Joined
Jun 12, 2014
Messages
70
Office Version
  1. 365
Platform
  1. Windows
Hi
I have this worksheet which column d12 is the monthly rent and which increases at the rent increase rate @d5,at the specified perid@d7. I wanrt to shorten the formula and also eliminate the amounts shown in column c12 and down. pls let me have any suggestions
REgrds
Shamsu
MenuLease Term
96​
Rental Rate
₹ 16,500.00​
Lease Date
10-09-20​
First Payment Date
01-10-20​
Current Date
08-06-21​
Rent Increase %
10.00%​
Increase @ Period No
154​
Increase Interval
12​
Increase Due
01-10-21​
New Rent
₹ 19,965.00​
Period NoMonthRentRent Increase
01-01-09​
Montly Rate
142​
01-10-20​
₹ 16,500.00​
₹ 16,500.00
143​
01-11-20​
₹ 16,500.00​
₹ 16,500.00
144​
01-12-20​
₹ 16,500.00​
₹ 16,500.00
145​
01-01-21​
₹ 16,500.00​
₹ 16,500.00
146​
01-02-21​
₹ 16,500.00​
₹ 16,500.00
147​
01-03-21​
₹ 16,500.00​
₹ 16,500.00
148​
01-04-21​
₹ 16,500.00​
₹ 16,500.00
149​
01-05-21​
₹ 16,500.00​
₹ 16,500.00
150​
01-06-21​
₹ 16,500.00​
₹ 16,500.00
151​
01-07-21​
₹ 16,500.00​
₹ 0.00
152​
01-08-21​
₹ 16,500.00​
₹ 0.00
153​
01-09-21​
₹ 16,500.00​
₹ 0.00
154​
01-10-21​
₹ 16,500.00​
₹ 0.00
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try FV

T202106a.xlsm
B
1
216,500.00
310%
42
519,965.00
6
3a
Cell Formulas
RangeFormula
B5B5=FV(B3,INT(B4),0,-B2)
 
Upvote 0
Hi there
Sorry I think I was not clear in my requirements. apologies for the same. What I am trying to achive is that 1 year from the date of the first payment date (10 10 20) cell d 4 inthe worksheet row d24 on 1-10-21should show 18150, then this amount in the next year should show 19965 in row d 36 and so on each year increasing by 10% from the previous year. F and the amount between each year should be the new amount ie from row d 24 to d3 5 18150 will be shown . for ur information the data from this sheet is used in another work sheet to record the receivable amount due from the tenenat date wise. I hope this is clear now .
 
Upvote 0
Try using the suggestion.
A post with XL2BB and a clear explanation of your question can be very useful.

T202106a.xlsm
CD
11-Oct-20
21-Oct-2116,500.00
3110%
4
518,150.00
3a
Cell Formulas
RangeFormula
C3C3=DATEDIF(C1,C2,"y")
D5D5=FV(D3,DATEDIF(C1,C2,"y"),0,-D2)
 
Upvote 0
hi
sorry if I am not clear . I do not know how to upload using xl2bb . I am attaching another file which shows what is required. u will see that in 1-10-20 the amt is 16500. this is until 1-9-21,the from 1-10-21 to 1-9-22 the new amount of 18150,then from 1-10-22 to 1-9-23the amount increases to 19965 and contuniously the tiem period. the time may be 1 year 2 year or max 3 years gap between each increase. I aplologize for being not clear and not being able to upload amy worksheet,.The formula i was using=
IF(B154>$B$5,0,IF(A154>=$D$6,IF(MOD(A154-$D$6,$D$7)=0,FV($D$5,INT((A154-$D$6)/$D$7)+1,0,-C154),FV($D$5,INT((A154-$D$6)/$D$7)+1,0,-C154)),C154))
If u can expalin to me how attach the worksheet i will forward the same to u. sorry for being dumb on this
a b c d
1MenuLease Term
96​
2Rental Rate
₹ 16,500.00​
3Lease Date
01-10-19​
4First Payment Date
01-10-19​
5Current Date
01-11-21​
Rent Increase %
10.00%​
6Increase @ Period No
154​
7Increase Interval
1​
8Increase Due
01-10-21​
9New Rent
$18,150.00​
10Period NoMonthRentRent Increase
153
01-01-09​
Montly Rate
154​
01-10-20​
$16,500.00​
155​
01-11-20​
$16,500.00​
156​
01-12-20​
$16,500.00​
157​
01-01-21​
$16,500.00​
158​
01-02-21​
$16,500.00​
159​
01-03-21​
$16,500.00​
160​
01-04-21​
$16,500.00​
161​
01-05-21​
$16,500.00​
162
01-06-21​
$16,500.00​
163
01-07-21​
$16,500.00​
01-08-21​
$16,500.00​
153​
01-09-21​
$16,500.00​
154​
01-10-21​
$18,150.00​
155​
01-11-21​
$18,150.00​
156​
01-12-21​
$18,150.00​
01-01-22​
$18,150.00​
01-02-22​
$18,150.00​
01-03-22​
$18,150.00​
01-04-22​
$18,150.00​
01-05-22​
$18,150.00​
01-06-22​
$18,150.00​
01-07-22​
$18,150.00​
01-08-22​
$18,150.00​
01-09-22​
$18,150.00​
01-10-22​
$19,965.00​
 
Last edited:
Upvote 0
D2=16500
D4=1-10-20
D5=10%

A12=142, B12=1-10-20, C12=IF(B12=$D$4,$D$2,$C11*(1+$D$5*(MOD(YEARFRAC($D$8,B12),1)=0)))
A13=!43, B13=1-11-20, C13= formula in C12 pulled down
Pull down formula till required.
 
Upvote 0
Hi
The formula provided works great. (y):)
The formula given works as wanted tks. for ur help. can the formula be modified so that the amount in c column is 0 until the date shown in b is equal to or greater than todays date shown in b5.
If this is problem pls ignore
Tks once again
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I do not know how to upload using xl2bb .
Look here: XL2BB
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top