Subscription/Service Payment Calculator - Active/Cancelled and Pro-rata Payments

[Thompa121]

Hi All,

I'm new to the forum and thought i would register for some help please. I've actually browsed this forum as a guest for many years - but now im stuck and need assistance.

Basically, ive been asked at work to produce an excel spreadsheet to help work out payments for subscription services we offer.

The data shows the following:

Customer Name
Registration Date
No. Of Users (software license related)
No. Of Companies (as above)
Account Status (Active or Cancelled)
Cancellation Date

The above is the raw data that will be updated on a regular basis.

Now what i have so far for the formulas is:

1. It works out the payment due for the full month - depending on what their software license number is (users/companies)
2. It works out an initial pro-rata payment based on the above (1). For example - 1 full month fee is £10. If they start their contract mid-month - this would show as £5 etc...
3. It has flags to indicate if the unit is active or cancelled
4. If the unit is cancelled, it will show how many days into the month they cancelled
5. It works out a pro-rata style cancellation payment if cancelled mid-month
6. If the unit is active it will only show the pro-rata active payment and the normal full months active payment.

Now what i need help with is the following:

If a unit is cancelled, i would love it to show the following:

1. What the initial pro-rata active payment is (only if the unit cancels in the same month it was sold)
2. If a unit cancels the following month - i only need it to display what the pro-rata cancellation payment is (eg unit was sold in July but cancelled the following month)

I would love to attach my template but as per forum rules i cannot.

Here is a table of how things look:

Customer Name	Registration Date	Users	Companies	Account Status	Cancelleation Date	Days In Month Activated (incl Registration date)	End Of Month When Unit Activated	Start Of Month When Unit Cancelled	Active Flag	Cancelled Flag	In-Month Cancelled Days	First Month's Active Pro-Rata Payment	First Month's Cancelled Pro-Rata Payment	Full Month Loyalty If Active	Full Month Loyalty If Cancelled	In-Month Cancellation Payment	Total Payment Due If Active
Customer A	03/01/2017	1	1	Active	01/01/2099	29	31/01/2017	01/01/2099	1	0	0	£6.20	£0.00	£6.50	£0.00	£0.00	£12.70
Customer B	13/01/2017	5	1	Active	01/01/2099	19	31/01/2017	01/01/2099	1	0	0	£13.80	£0.00	£22.10	£0.00	£0.00	£35.90
Customer C	14/01/2017	2	1	Active	01/01/2099	18	31/01/2017	01/01/2099	1	0	0	£6.15	£0.00	£10.40	£0.00	£0.00	£16.55
Customer D	04/02/2017	3	3	Active	01/01/2099	25	28/02/2017	01/01/2099	1	0	0	£18.16	£0.00	£22.10	£0.00	£0.00	£40.26
Customer E	04/02/2017	1	1	Active	01/01/2099	25	28/02/2017	01/01/2099	1	0	0	£5.34	£0.00	£6.50	£0.00	£0.00	£11.84
Customer F	04/02/2017	5	1	Active	01/01/2099	25	28/02/2017	01/01/2099	1	0	0	£18.16	£0.00	£22.10	£0.00	£0.00	£40.26
Customer G	07/02/2017	5	1	Active	01/01/2099	22	28/02/2017	01/01/2099	1	0	0	£15.98	£0.00	£22.10	£0.00	£0.00	£38.08
Customer H	18/02/2017	5	1	Active	01/01/2099	11	28/02/2017	01/01/2099	1	0	0	£7.99	£0.00	£22.10	£0.00	£0.00	£30.09
Customer I	11/03/2017	5	1	Active	01/01/2099	21	31/03/2017	01/01/2099	1	0	0	£15.26	£0.00	£22.10	£0.00	£0.00	£37.36
Customer J	12/03/2017	5	1	Active	01/01/2099	20	31/03/2017	01/01/2099	1	0	0	£14.53	£0.00	£22.10	£0.00	£0.00	£36.63
Customer K	19/03/2017	5	1	Active	01/01/2099	13	31/03/2017	01/01/2099	1	0	0	£9.45	£0.00	£22.10	£0.00	£0.00	£31.55
Customer L	16/04/2017	5	1	Active	01/01/2099	15	30/04/2017	01/01/2099	1	0	0	£10.90	£0.00	£22.10	£0.00	£0.00	£33.00
Customer M	18/04/2017	5	1	Active	01/01/2099	13	30/04/2017	01/01/2099	1	0	0	£9.45	£0.00	£22.10	£0.00	£0.00	£31.55
Customer N	27/08/2017	5	1	Active	01/01/2099	5	31/08/2017	01/01/2099	1	0	0	£3.63	£0.00	£22.10	£0.00	£0.00	£25.73
Customer O	18/05/2017	1	1	Active	01/01/2099	14	31/05/2017	01/01/2099	1	0	0	£2.99	£0.00	£6.50	£0.00	£0.00	£9.49
Customer P	29/05/2017	1	1	Cancelled	16/06/2017	3	31/05/2017	01/06/2017	0	1	15	£0.00	£0.64	£0.00	£6.50	£3.21	£10.35
Customer Q	03/06/2017	1	1	Active	01/01/2099	28	30/06/2017	01/01/2099	1	0	0	£5.98	£0.00	£6.50	£0.00	£0.00	£12.48
Customer R	05/06/2017	1	1	Active	01/01/2099	26	30/06/2017	01/01/2099	1	0	0	£5.56	£0.00	£6.50	£0.00	£0.00	£12.06
Customer S	17/06/2017	2	2	Active	01/01/2099	14	30/06/2017	01/01/2099	1	0	0	£6.58	£0.00	£14.30	£0.00	£0.00	£20.88
Customer T	04/07/2017	2	2	Active	01/01/2099	28	31/07/2017	01/01/2099	1	0	0	£13.16	£0.00	£14.30	£0.00	£0.00	£27.46
Customer U	06/07/2017	2	1	Active	01/01/2099	26	31/07/2017	01/01/2099	1	0	0	£8.89	£0.00	£10.40	£0.00	£0.00	£19.29
Customer V	16/07/2017	1	2	Active	01/01/2099	16	31/07/2017	01/01/2099	1	0	0	£5.47	£0.00	£10.40	£0.00	£0.00	£15.87
Customer W	02/08/2017	6	1	Active	01/01/2099	30	31/08/2017	01/01/2099	1	0	0	£25.64	£0.00	£26.00	£0.00	£0.00	£51.64
Customer X	04/08/2017	3	4	Cancelled	20/08/2017	28	31/08/2017	01/08/2017	0	1	19	£0.00	£23.93	£0.00	£26.00	£16.24	£66.18
Customer Y	18/08/2017	1	1	Cancelled	28/08/2017	14	31/08/2017	01/08/2017	0	1	27	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
Customer Z	27/08/2017	5	1	Cancelled	30/08/2017	5	31/08/2017	01/08/2017	0	1	29	£0.00	£3.63	£0.00	£22.10	£21.07	£46.80

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I really hope someone can help me finish off this project, appreciated in advance.

Marc