|
MeraWindows Forums - Get your Computer Problems Fixed |
| This is a site run by the community - for the community. At the site, we share and contribute our computer learnings with each other. We discuss about Windows, Office, Mobile, Gaming and other computer related topics. At our forums you can post your queries and our very helpful community members will respond quickly to your queries. We organize lots of competitions from time to time and award prizes to the winners.
You will need to register at the site before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
|
|
 | | |
| | |
| | |
| | |
 | | |
 |
|
|
 |
Joined: 12/18/2009
Posts: 3
|
|
|
I have a database which of around 32000 Rows which goes on adding month on month. Typically this is a database maintained in excel to provide for the differential between equalised rent and amount paid(As per Accounting Standard 19 - Leases of ICAI). Presently i m using a macro called rent function to compute the above but its taking a lot of time to compute such a huge database. Basically i need to compute the two;
1) Rent to be equalized over the tenure of lease period ( IN simple language how much will be the accrued rent i.e, total rent to be paid during the tenure of lease period after considering the base rent, increment %, change period , total lease period)
2)Actual Rent to be paid for a certain defined date ( IN simple language rent payout as on a certain date or a month)
Example:
A Company is engaged into a Infrastructure business ( mainly tower construction company providing service to Telecom Operators), takes space ( Land) on lease for some given tenure. Required to compute the rent capitalized portion for the entire lease period and actual rent paid as on a certain date:
Illustrative Example:
|
Total Rent capitalized
|
For Actual Rent Calculation
|
| Agreement Start Date |
1-Nov-09 |
| Rent Start Date |
1-Dec-09 |
30-Nov-14 |
| Lease Period |
20 Years |
| Change period |
5 Years |
| Escalation (Increment%) |
10% |
| Year |
Increment % |
Rent PM |
No of Months without rent free period |
No of Months with rent free period |
Total Rent Accrued/Capitalized without Considering One Month Rent free Period |
Equalised Rent without considering rent free period |
Total Rent Accrued/Capitalized Considering One Month Rent free Period |
Equalised Rent considering rent free period |
| 1-5 Years |
|
1,950 |
60 |
60 |
117,000 |
146,056 |
117,000 |
145,315 |
| 6-10 Years |
10% |
2,243 |
60 |
60 |
134,550 |
146,056 |
134,550 |
145,315 |
| 10-15 Years |
10% |
2,579 |
60 |
60 |
154,733 |
146,056 |
154,733 |
145,315 |
| 15-20 Years |
10% |
2,966 |
60 |
59 |
177,942 |
146,056 |
174,977 |
145,315 |
| |
|
|
240 |
|
584,225 |
584,225 |
581,259 |
581,259 |
Actual Rent to be paid as on a certain date
Assume the date as 30th November 2014
Actual Rent paid = ( As on date - Rent start date)* Rent PM = ( 30/11/2014 - 01/12/2009)* 1950
= 118,950/-
|
|
|
|
| |
|
|
|
Hi,
Suppose the Date of lease deed is in cell O5, rent start date in cell P5, Increment % in cell R5, tenure in cell S5, chenage period in cell T5 and rent per month in cell U5. The rent accrued/capitalised would be computed as follows
=SUMPRODUCT((($U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND($S5,0)))-1)/$T5))).
This formula will give you the answer as 584,225. Please note that fractional tenure would get rounded off i.e. if the tenure is 19.83, it will be treated as 20. If it is 19.3, it will be treated as 19.
For computing equalised rent considering rent free period, use the following formula
=(SUMPRODUCT(((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5))))+(MAX(INDEX((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5),,1))/12*(DATEDIF(O5,P5,"m")))
In the above formula as well, the rent free period would get converted to the lower who;e number i.e. if rent free period is 3 months and 20 days., it will be treated as 3 months.
Try these two formulas on 32,000 cells and let me know how it works. Do let me know if you face any performance hitches.
|
|
|
|
| |
|
|
|
Hi,
Not clear about how you got 118,950. Between the two dates, there are 60 months and not 61 months. Therefore, the rent paid should be 117,000. Anyways, this formula will get the answer as 117,064
=SUMPRODUCT(((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&DATEDIF(P5,$V$1,"y")))-1)/5)))+(INDEX(((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&DATEDIF(P5,$V$1,"y")))-1)/5)),DATEDIF(P5,$V$1,"y"),1)/365*(($V$1-P5+1)-(DATEDIF(P5,$V$1,"y")*365)))
The additional 64 could be because of error is number of days. I have assumed 365 days per year. Cell V1 has 30/11/2014
Do let know how this works.
|
|
|
|
| |
|
|
|
Joined: 12/18/2009
Posts: 3
|
|
|
The formula for Actual Amount paid is approximate and is working perfectly. But when ever i go to a particular cell where the formula is applied and press F2 the same is being converted to date format... Is there any particular reason for that???
And regarding Rent Equalisation, when i apply the formula
=SUMPRODUCT((($U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND($S5,0)))-1)/$T5))) the answer comes to 584225 which is perfectly ok. I have to remove from this the rental free amount.
whereas if i apply the second formula =(SUMPRODUCT(((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5))))+(MAX(INDEX((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5),,1))/12*(DATEDIF(O5,P5,"m"))) the answer comes to 554,567/- there is a variance of Rs. 30,000/- which deviates from my answer. and the same problem persists when i press F2 it returns to date format...
And the calculation of Actual Rent by me came to Rs. 118625 which was due to i have taken 30 days on an average which made the difference. The actual rent is 117000 only as correctly said by you..
Please do the needful.
Regards,
Pankaj
| |
|
|
|
|
| |
|
|
|
Hi,
Try this
=(SUMPRODUCT(((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5))))+(MAX(INDEX((U5*12)*(1+$R5)^INT((ROW(INDIRECT("1:"&ROUND(S5,0)-1))-1)/$T5),,1))/12*(12-(DATEDIF(O5,P5,"m"))))
This formula should give you the correct result.
Also, in my workbook, when i press F2 and Enter, it remains a number.. Ensure that the cells are all formatted as general.
Is the performance of the workbook better then the previous solution that you were using. I remember you tellign me that it takes 15 minutes for the workbook to calculate when you make any change or open the workbook.
Pleae post back and let me know.
|
|
|
|
| |
|
|
|
Joined: 12/18/2009
Posts: 3
|
|
|
The formula is working perfectly only there is one problem if the Date of Agreement is after the Rent Start date then the Rent Capitalized and Actual Rent is showing an error of #NUM.. So is there a solution to overcome this too in the Same formula...
And the Formula works faster than the Macro. Thanks for that. I regret for the Delay in replying..
Regards,
Pankaj
|
|
|
|
| |
|
|
|
HI,
Please be specific about the proble. When you say that you want a formula for the #N/A, do you simply want to conceal it. If not, then please take an example where agreement date is after the rent start date and give the expected output.
|
|
|
|
| |
|
|
|
Hi,
If the date of agreement is after the rent start date, then there is obvoiusly no rent free period. There simply put an IF formula around my formula, saying that if date of agreement is after rent start date, then show blank, else the formula itself.
|
|
|
|
| |
| | |
| | |
| | |
|
| |
|
| |
|
|
User Log In
|
 |
|
| |
|
|
Follow Us
|
|
|
|