Excel formula for Direct Debit calculator - Help Please!

I work for a general insurance company and have been tasked with producing a Direct Debit (DD) calculator in excel which will show the 1st DD collection date from our customers' bank account. It takes 14 days from the day the policy is sold (not start date) to setup a DD on our systems, so dependent on whether the preferred collection date is within or outside of 14 days from the sold (not start date) date of the policy will determine when the first DD is taken out of the customers account.

The data that will be entered by the user will be..

Cell A. Date of policy sold (DD/MM/YYYY)
Cell B. Start date of policy (DD/MM/YYYY)
Cell C. Preferred direct debit collection day (DD)

Entering this information will result in Cell D. showing the date of the 1st DD collection (DD/MM/YYYY)

Example 1:
Cell A. Date of policy sold: 01/01/2013
Cell B. Start date of insurance policy: 01/01/2013
Cell C. Preferred direct debit collection day: 15
Cell D. 1st DD collection: 15/01/2013

Example 2:
Cell A. Date of policy sold: 01/01/2013
Cell B. Start date of insurance policy: 06/01/2013
Cell C. Preferred direct debit collection day: 8
Cell D. 1st DD collection: 14/01/2013 (being the first possible date we can collect the premium. Each premium thereafter will be collected on the 3rd of each month)

Preferably I'd like to use a cell formula as I am not very 'techy' and not familiar with VBA. Is there a formula I could use in the results cell that will provide the correct 1st collection date?

Thanks in advance!

Milly

Hi,

Thanks for this, it works perfectly for the examples I'd given but there are a couple of scenarios where this formula wont work.

There are 2 exceptions to the formula you've provided:
1. If the preferred collection day is lower number than the day of the start date it will be taken in the next month.

Example of this:
Cell A. Date of policy sold: 01/01/2013
Cell B. Start date of insurance policy: 17/01/2013
Cell C. Preferred direct debit collection day: 8
Cell D. 1st DD collection: 08/02/2013

2. If the preferred collection day is after the start date but before the 14 days after the sold date, the DD will be taken on the next available day of the month after the 14 days.

Example of this:
Cell A. Date of policy sold: 28/01/2013
Cell B. Start date of insurance policy: 01/02/2013
Cell C. Preferred direct debit collection day: 5
Cell D. 1st DD collection: 10/02/2013 (the 14th day after the sold date)

Really hope this makes sense, here is the spreadsheet if you want to have a play about with it.. Attachment 237226

Your help is greatly appreciated,

Milly

Hi,

Thanks again for your help, I've tried entering this formula but it just comes up with '#NAME?'. No doubt it's something I'm doing wrong!

Here's the attachment (hopefully this time it works). I've used your example on tab 2, let me know what you think.

DD Calculator.xls

Thanks,

Milly

Hmm.. I just tried this but nothing? Could it be because I'm using the 2003 version??

you can replace EOMONTH with

Please Login or Register  to view this content.

making the whole code

Please Login or Register  to view this content.

Wow! I think you've almost cracked it.. but I've tried another example where it's not quite right..

Cell A. Date of policy sold: 15/06/2013
Cell B. Start date of insurance policy: 20/06/2013
Cell C. Preferred direct debit collection day: 1
Cell D. 1st DD collection: Should be 01/07/2013 but it's showing as 28/06/2013?

Hi,

Right. So I've had a meeting with the head of our operations department to clarify exactly how many different possibilities there are for our DD collections and there are 3. But just to clarify first of all; a policy can be sold months in advance of the start date - in which case we will have had time to setup the DD so can start collecting premiums as soon as the policy starts on the customers preferred collection date (PCD)

Also in response to your questions, if the PCD is inside the 14 day window, we will take the 1st DD on the first possible day (day 15) after the sold date, so not the next month. So yes, we would therefore collect the 1st DD on 14/01/2013 and the second on 08/02/2013.

Possibilities:
1. If the PCD is after the start date but before the 14 days after the sold date, then we will collect the DD on the first possible day (day 15) after the sold date.
2. If the policy is sold months in advance of the start date, we can collect the first DD on the first PCD after the policy starts.
3. If the PCD is before the start date (i.e. start date: 2nd of the month / PCD: 1st of the month) then we will collect the first DD on the 2nd of the next month. This is based on the assumption the 14 day window has passed and the DD has been fully setup by this stage.

So ideally the formula would incorporate all of the above 3 'IFs'.. I've tried your formula however it wouldn't work for all 3 possibilities.

Really sorry to keep coming back to you, I'm completely stumped and in need of your help!

Thanks again, really appreciate your help.

Milly

Also here is the template I've been asked to use.

You can ignore the bit about commission, I've managed to figure out the formula for this based on whatever the 1st DD date is.

Just so you can see what I'm working with...

DD Calculator V2.xls

Hi,

Sorry I should have clarified this previously but the PCD is only available been 1-28 (which would eliminate the 29th,30th,31st problems).

Which would also resolve the issue you raised regarding possibility 1. But hypothetically, in your example where the sold date is 17/01/2013, start date is 31/01/2013 and the PCD is 1st then the first DD would be the 01/02/2013 because the DD has been fully setup.

With possibility 3, you're absolutely right! I meant that the 1st DD would be the 01/02/2013. Really sorry for the confusion.

Wow! You've only gone and cracked it!!

I've sent it on to our head of operations for them to test it so I'll let you know what they say. But I've tried several examples and every one of them works.

I'll post back on Tuesday to let you know. Thanks so much, you're a life saver.

Milly

Originally Posted by loginjmor

Glad I could help Milly - Have a great weekend!

Just received a response from our Head of Operations 'Bang on. Works like magic'

Thanks again for your help, couldn't have done it without you.

Yey!

Milly