Lease Calendar

[priyapanchi]

Hi All,

Kindly find the attached file having the Sample Data Set.

Vendor Code	Lease Rental Commencement Date (dd/mm/yyyy)	Lease End Date (dd/mm/yyyy)	Percentage Increase	Increase per	Lease Escalations	Rent as per Agreement
V1	01-10-2015	30-09-2020	12	2	12% every 2 year	123633
V2	01-08-2018	31-07-2023	7.50	1	7.5% every year	23112

I need to Show Rent on monthly basis.

The Percentage Increase and Increase per year will be different according to the agreement.

Rent will be calculated basis  1st eg - the rent will increase by 12 % per 2 years .This calculation has to be shown for all the years till Lease End Date.

Any Suggestions,how we can achieve the same.

Thanks.

[lironbaram]

hi 

have a look at the attach example 

basically you use do while between your lease date and create a record for each month , for each month , 
you can know what should be the rent 

your script should look something like this :

[Table]:
LOad *,
date(addmonths([Lease Rental Commencement Date (dd/mm/yyyy)],IterNo()-1)) as MonthYear,
floor((iterno()-1)/(12*[Increase per])) as multiplier
while date(addmonths([Lease Rental Commencement Date (dd/mm/yyyy)],IterNo()-1))<=[Lease End Date (dd/mm/yyyy)];

LOAD
[Vendor Code],
Date(Date#([Lease Rental Commencement Date (dd/mm/yyyy)], 'MM-DD-YYYY') ) AS [Lease Rental Commencement Date (dd/mm/yyyy)],
Date(Date#([Lease End Date (dd/mm/yyyy)], 'DD-MM-YYYY') ) AS [Lease End Date (dd/mm/yyyy)],
[Percentage Increase],
[Increase per],
[Lease Escalations],
[Rent as per Agreement];
LOAD * INLINE
[
Vendor Code,Lease Rental Commencement Date (dd/mm/yyyy),Lease End Date (dd/mm/yyyy),Percentage Increase,Increase per,Lease Escalations,Rent as per Agreement
V1,01-10-2015,30-09-2020,12,2,12% every 2 year,123633
V2,01-08-2018,31-07-2023,7.50,1,7.5% every year,23112
](delimiter is ',');

[priyapanchi]

Thanks for your reply,

But i want to calculate the rent where the multiplier is 2 on basis of the
value calculated on the agreement value when the multiplier is 1.

Kindly suggest

[lironbaram]

hi 

if you need the calculation to be according for this example :

basis rent 100 and 10% bump every year 

so 

year 1 = 100

year 2 = 100*1.1=110

year 3 = 110 * 1.1 = 121 

then just change the expression to 

Sum([Rent as per Agreement])*pow((1+[Percentage Increase]/100),multiplier)