Overdraft Interest rates in Excel

da8iwr

Hi There,
I have been trying to make an excel sheet for the past 24/48hrs now, and im stuck on one little bit - over draft interest charges.

If i have 4 boxes
A1 - Amount your into overdraft
A2 - Overdraft EAR Rate
A3 - Overdraft % per month
A4 - Result - How much it costs per month in interest

Using rates from places like this
http://www.lloydstsb.com/rates_and_c...onaloverdrafts

I need box A4 to show how much it costs per month in interest to add as a costing to a business plan to add to monthly outgoings.

Does anybody know the excel maths i would need to add in box A4.

I'm not even really sure if i need other info, or this is the correct rates to use. I know that it would be slightly wrong as your balance will change all month, but im assuming for the maths of this, that the account will be the same balance all month.

Any help would be very appreciated, as every time i type in interest rate calculation or overdraft calculator or anything at all in Google, it just brings up the bank charges websites, on how to work out what the banks owe you.

CLAPTON

not quite sure exactly what you are trying to calculate but

EAR usually means the interest rate on savings...is that what you mean
'overdraft % per month ' ...what does this mean ... interest rate on borrowing?


but if A1 is the (constant ) OD for the full month and
A3 is the monthly interest rate charged on the OD

and if
A4 contains =A1*A3 this will be the interest charged per month

so if A1 = 1000
and A3 = 1% per month
then A4 = 1000 x 1% = £10

da8iwr

Hi Clapton
I am building a website at the moment, that shows cash flow problems in business.

So if you buy a product from a supplier for £1000 and then sell it for lets say £2000 to your client, but dont get paid for 30 days, you have to either have the cash to pay for that £1000 out of your own pocket (to your supplier) or put it onto some form of credit agreement until it is paid, usually an overdraft.

So then once you are paid the £2000, 30 days later (assuming the customer pays you on time), you also then have to pay the interest on that £1000 for the time it was sat in your overdraft which eats into your profit margin.

I'm trying to make an Excel sheet that works this out and i can give it away for free, for people to use to work out this kind of maths and even compare different rates against each other, to work out what effect it will have on their profits.

I'm not sure if I'm making sense here lol
Regards
Ian

da8iwr

Hi there
Im not sure if this is right but found this formula, is it right?
http://www.newworlddesigns.co.uk/temp/paymentmaths/overdraftmaths.xls

All the best
Ian