calculation of non utilisation fees for numerous bank accounts

Evening guys,

I am looking for a solution to an excel problem I have. I think a macro is required and although I have bought Excel VBA programming for dummies I haven't got round to getting through it in enough detail to solve my problem.

Basically I want to calculate a non-utilisation fee for a bank account (i.e. if i have a loan for £100 - but i only withdray £75, I pay a fee on the remaining £25). Sounds simple enough but I have a lot of accounts and want to calculate this fee on a daily basis for a specified period based on the following inputs:

Account number - e.g 12345678
Account Limit - e.g £100 in example above
Fee - percentage that is charged i.e. 1% per annum
Dates - e.g 1/1/10 to 31/3/10

Now this data needs to pick up the account number (in column B of another worksheet) then scan along the daily balances to pick up the dates (added complication is that balances will only be available for workind days) and the appropriate balance in the rows. It then needs to calculate the applicable non utilisation fee for each day and sum this total for the specified dates above.

If anyone could help me with this I would be very grateful, I always thought I was a good on excel but when it came to this problem I was stumped and feel that a macro is required which is definetly not my strong point!!

If anyone needs more information please let me know and all replies are much appreciated.

Fonzie

Thanks for the response guys.

I have attached a basic template. I am not sure how the weekday element will work (i.e. sat and sun are not included in the dates but should still be charged interest) but can crosss that bridge when we come to it.

Let me know if you need any further information.

Fonzie

thanks foxguy,

Your contributions are much appreciated and it looks like it works.

I will look into it more when i am at home tonight, however is it possible to amend dates on the sheet so that I could work out what the quarterly fee would be (or any fee between 2 dates)? That is quite important.

I was also wondering how the fee would work on a Monday - as it would have to charge 3 days interest. However I am sure the formula could be amended to subtract the dates and divide by days in year to ensure that interest is calculated for the full year.

Thanks again for your help and like i say i will have a proper look when i am at home

Thanks foxguy - i have had a chance to have a good look at it and i'm very impressed. There are on or two tweaks which I would like your help with if you are able to help me again.

The weekend formula works - i see the N/A covers the weekend however the interest still needs to be calculated for the weekend (on the balance on the friday). I tried to amend the formula to pick up the previous days balance if the cell shows NA, however this then calculates the fee for the periods post the specified dates as these are N/A also! Is there anyway round this?

I think i would be best to populate the limit and the fee %age in the data sheet also to fully automate the process. However using what you have provided I am sure I could add this in.

Thanks again for your help and any input around the weekend calculations would be great.