Allocation of Licence Fee based on escalation date

Dear All,

Looking for solution to allocate the licence fee month wise base on the escalation date. I have given the following example where allocation should be through start date to end date keeping in mind the middle escalation dates and escalated amounts.


LF Start Date LF End Date Base LF 1st Escalation Effective from 2nd Escalation Effective from
15-Jul-15 14-Jul-18 100 115 07-08-2016 120 26-01-2017

1 LF Start Date is 15th July 2015
2 LF End Date is 14th July 2018
3 Base agreement LF is Rs. 100/- pm
4 So till for 06th Aug 16, LF will Rs. 100 pm -- ( for August 2016 LF will be for 5 days @100 & for rest @ 115 pm till next escalation trigger)
5 From 07th Aug 16 till 26th Jan 2017, LF will at Rs. 115 each months
6 From 26th Jan 17 onward, LF will at Rs. 120 each months


Tabular Data will be

FY 15-16 Moth Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 Feb-16 Mar-16
LF Amount 50 100 100 100 100 100 100 100 100

FY 16-17 Month Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 Jan-17 Feb-17 Mar-17
LF Amount 100 100 100 100 16.67 + 91.99 115 115 115 115 92.74 + 23.22 120 120

….. and son till LF end date i.e. 14th July 2018

Note: LF for Aug 16 : 06 Days @ 100 & Rest 24 days @115 since escalation date change on 07th Aug. 2016
LF for Jan 17 : on same principal as for Aug. 16


Thanks in advance

Regards

Hi JeteMc

Superb.. Thanks for your valuable help....works absolutely fine. You really shared my tremendous workload since I have to process more than 2000 line items.. Thanks a lot...

Can you add 3rd escalation to the given formula ?
Column I Column J
Sr. No. LF Start Date LF End Date Base LF 1st Escalation Effective from 2nd Escalation Effective from 3rd Escalation Effective from
1 15-Jul-15 14-Jul-18 100 115 07-08-2016 120 26-01-2017 130 06-06-2017
2 18-Jul-15 04-Jul-17 100 100 04-04-2016 105 06-02-2017 110 05-05-2017

Thanks in advance.

Hi Daddylonglegs,

Work superb.. I can put as many as escalation without any hassle. Thanks for valuable support.

Hi Daddylonglegs,

Formula works fine with following exception.

1) It works only when I put figures and date in each escalation column (date and amount field). However there may be data for one escalation field and other two escalation data field may empty. In that case it shows value zero in months column instead of considering the 1st escalation data and spreading the same over months till LF date. I need where if escalation data are empty or with no data, spread in months column should be from LF start date to LF end date.

2) If I put 1st escalation data i.e. amount and date (and same date in 2nd and 3rd escalation column just to keep alive formula else it shows 0 value), amount spread over months columns till the escalation date only and not till LF end date. However spread over months columns should till LF end date after taking the 1st or 2nd or 3rd escalation criteria. Same time I need not to put same date in 2nd or 3rd column to keep the formula alive if these fields are empty.

Looking after your valuable support.

Thanks in advance

Hi Daddylonglegs,

I have tested the formula and everything works correct except the redundent entries (marked in red font) for the date and amount in escalation columns to work to work the formula correct. Is it possible to avoid the same by changing the formula or some alternate way ? I have attached file for better understanding.

Looking forward your valuable help.


Thanks & Regards,