Consideration of renewal term in formula

[beachdrop]

Hi all,

I have a contracts spreadsheet containing all our suppliers that contain contract renewal dates, contract duration and monthly cost. I'm trying to work out a formula to display the cost to terminate a supplier 18 months from now which takes into consideration the remaining term of the contract and therefore whether that contract will also need to be renewed within the 18 months.

As an example, if todays date is 01/01/20, a supplier has a monthly cost of $1000, a contract duration of 60 months and a renewal date of 01/01/2021, the cost to terminate that supplier between now and 02/07/2022 (18 months time) would be $12,000 + $60,000.

If the renewal date in the same example was instead 01/01/23, then the cost to terminate would be $18,000 (no renewal).

Any help would be massively appreciated - I think I need to use some IF statements and an array to find the possible renewal dates in the range but I just can't work out how to do it!

Many thanks,
BD

 

[jasonb75]

As an example, if todays date is 01/01/20, a supplier has a monthly cost of $1000, a contract duration of 60 months and a renewal date of 01/01/2021, the cost to terminate that supplier between now and 02/07/2022 (18 months time) would be $12,000 + $60,000.

Could you explain how you reach that figure please?

Assuming that your dates are in US format of mm/dd/yyyy, I'm coming up with a figure of $18,000 (5 months left on existing contract plus first 13 months of renewal) + $47,000 (47 months of renewed contract unused).

 

[beachdrop]

Sorry - I incorrectly converted from UK to US dates. It should have read:

As an example, if todays date is 01/01/20, a supplier has a monthly cost of $1000, a contract duration of 60 months and a renewal date of 01/01/21, the cost to terminate that supplier between now and 07/02/2022 (18 months time) would be $12,000 + $60,000.

That figure comprises 12 months remaining on the existing contract and than a commitment of 60 months to cover the remaining 6.

Hopefully that makes sense!

 

[jasonb75]

Something is still not adding up correctly, did you mean 07/02/2021 for the last date?

See if this helps, I've used long date format to try and reduce confusion.

Please note that this only allows for one contract renewal, if you had a 12 month contract which needed to be renewed twice in order to cover the 18 month period until termination then the formula will need to be modified to cater for that. I've tried to keep it simple to start with.

Book2
	A	B	C	D	E	F
1	Current Date	Renewal Date	Termination Date	Duration	Monthly Cost	Termination Cost
2	01 January 2020	01 January 2021	02 July 2021	60	1000.00	72000.00
3	01 January 2020	01 January 2023	02 July 2021	60	1000.00	36000.00
Sheet8

Cell Formulas
Range	Formula
F2:F3	F2	=DATEDIF(A2,IF(B2>C2,B2,EDATE(B2,D2)),"M")*E2

 

[beachdrop]

Thank you so much for this - that's amazing and yes that was a typo (the last date should have been 07/02/2021).

Given that a contract could be short and therefore need to be renewed at least once in order to cover the 18 month period until termination have you got any ideas how to go about amending this formula?

Thank you so much again! You have no idea how much head scratching I've been up to trying to resolve this.

 

[jasonb75]

I had a quick look and came up with this formula which appears to work with a quick test.

=DATEDIF(A2,IF(B2>C2,B2,EDATE(B2,CEILING(DATEDIF(B2,C2,"M"),D2))),"M")

One thing that I missed (which applies to this formula and the original one) is that a renewal could get missed if it is due less than one month before the termination date.

In such cases, should the renewal still be applied, or is there a point where termination should be brought forward instead of renewing?

 

[beachdrop]

That's awesome - thanks so much for this. The renewal should still apply if it's due less than one month before the termination date... presumably we can resolve that using an IF statement?

Thanks again.

 

[jasonb75]

Yes, that should be possible. I did try a couple of things but not working as expected. I'll have another look at it later to see if I can figure out what I'm missing, that might not be until tomorrow though.

 

[beachdrop]

Thank you so much - it's really appreciated.

 

[jasonb75]

I think that I have this right now, this is based on the same data layout as post 4.

=DATEDIF(A2,IF(B2>=C2,B2,EDATE(B2,CEILING(DATEDIF(B2,C2-1,"M")+1,D2))),"M")*E2

The part in bold calculates the end of contract date including any renewals, that can be entered as a formula on its own if you want to verify the dates.

The formula will include any renewals up to and including 1 day before the termination date.
I've assumed that contracts will expire at the end of the business day and that renewal on the termination date would not be necessary, if renewal is required in such cases then you will need this version instead.

=DATEDIF(A2,IF(B2>C2,B2,EDATE(B2,CEILING(DATEDIF(B2,C2,"M")+1,D2))),"M")*E2

I've done some testing with random dates on both versions of the formula and the results appear to be correct, hopefully I'm not missing anything.