Calculating quarterly, semi-annual and annual amounts based on monthly amounts

Hi Excel champions,

I would greatly appreciate your help on this as I can’t seem to get my head around it….please help me.
I have a monthly analysis of account payable balances for a particular product from one supplier. At each anniversary of the onset date for the supplier relationship, we have to pay a “holding fee” of 1.5% on the outstanding balance, so for example,
Supplier A contract start date 1-Oct-14
Supplier A contract end date 31-May-19
So we pay a 1.5% holding fee on the balance on 1-Oct-15, 1-Oct-16, 1-Oct-17, etc until the contract end date is reached.
While I have been able to do this on a monthly basis, I need to do same dynamically on a quarterly, semi-annual and annual basis and this is where I am having a hard time. For example, the first anniversary, 1-Oct-15, will fall within the quarter starting 1-Oct-15 and ending 31-Dec-15. I need to ensure that the fee is calculated on the supplier's balance at exactly 1-Oct-15 and placed in exactly the cell within the column that contains the quarter into which the anniversary date falls. I need a formula that can do several things:

1. Identify that a particular "quarter" or "semi-annual" column, etc contains an anniversary date
2. Once it identifies that the column contains an anniversary date, I need a formula that will automatically identify the supplier balance at the exact anniversary date, 1-Oct, in this case, from the corresponding monthly balances
3. Finally I need to have excel calculate the holding fee of 1.5% on that identified balance and place the calculated fee amount in the exact "quarter" or "semi-annual" column, etc that contains the anniversary date.

The sample spreadsheet is attached. In this spreadsheet, the row colored green shows that I have been able to calculate the fee. The row shaded yellow is where I am having a hard time and I need excel to calculate the fee and place the calculated amounts in the columns with the red-lined oval shapes......any help will be greatly appreciated. Thank you all very much.

Xiedwo

"It always seems impossible until it is done!"

Excel experts,
I was really hoping to obtain help from this forum. I still have not been able to get my head around my problem. Can anyone please help me, please!!! Please if anyone requires clarification, I will definitely provide. Thank you and I look forward to your assistance.

xiedwo