Spreadsheet with interest

[bram stoker]

OK - I'm thoroughly confused now. So the spreadsheet I was using (the one downloaded from the library here for complex bank charges england) doesn't calculate compound interest?

 

Do the google ones calculate compound? I'm hoping to send my letter to the bank tomorrow!

Yes. Just select the one called compound. Have you been into Chambers? There are many different scenarios.

 

You'll need to change the rate to the one your bank charges you.

[bram stoker]

The calculations use APR. You have to enter the APR rate you wish to use.

[bram stoker]

The interest is calculated on each individual charge from the date of each charge, and no averaging is necessary.

 

Please feel free to take a look at the calculations within the spreadsheets and send any suggested amendments with explanations to me at the Vamp gmail address.

 

I am currently looking at a couple of others, and should be able to deal with most of them this weekend.

[bram stoker]

Peter,

 

Did you receive my email? I'm trying to work out what you're trying to tell us here. Exactly how and why would this formula be necessary within the spreadsheet?

 

Can you send me an example of a spreadsheet using it in a way you're trying to explain please, then I can understand better?

[bram stoker]

In the previous excel spreadsheets you had to enter the maximum overdrawn figure for the month that the interest referred to.

I notice on the Google spreadsheet that it now says enter an average overdrawn figure. Is this correct? So if I was overdrawn (in the cleared for interest column of my statement) for the balances of -£550, -£650, -£800, -£825 and -£300, the average to enter in the cell would be -£625?

I don't recal it saying maximum. The figures will give estimates. The arguement is stronger than the calculations. If you have accumulated £300 worth of charges, any overdrawn balance of £300 or less will be made up of charges and interest on the overdraft will be interest on charges.

[bram stoker]

The instructions on the spreadsheet state to enter the balance at the date of interest so I entered my balance for the date the interest was deducted. I hope thats correct??!?!?!

I advised that in the beginning when I was novice. I don't support those spreadsheets anymore, hence updated versions are now in place after having spent more time thinking about the scenarios involved.

 

To be honest it's not a major issue, put whatever figure gives a sensible answer keeping the principle and argument in mind.

 

Blueskies and WifeandI are working with the old spreadsheets, so please divert your questions to them. I'll keep the Google ones updated.

[bram stoker]

Re: Danmarton Against NatWest! Can anyone help me with the spreadsheets??? I am stuck with what to put in the interest rate section, having looked on the NatWest site for the rates regarding my overdraft, I have come up with these:

Current EAR variable 17.95%, Current nominal 16.62%, Current monthly nominal 1.38

Typical 18.86% EAR. Rates from 17.95% to 18.86% EAR.

Updated September 2006.

EAR stands for Effective Annual Rate - the real cost of an overdraft, stated as an annual fee, which takes into account how often interest is charged to the account. All other charges, such as arrangement fees, will be shown separately from the EAR.

 

So my question is which figure do I put in the spreadsheet, I am using Vamps (thanks Vamp) Throughout claim with contractual interest simple one, but unsure what figure I put in place of the 8.00%

Please someone help so I can get cracked on with it!

Thanks In Advance

Have you any recent Natwest documents / letters stating which interest they apply to your account?

[bram stoker]

Did you arrange for them to take this money? I'd claim 29.6% personally.

[bram stoker]

I know it sounds like a lot, but they chose the rate in the first place. You are creating a list of penalty charges. Some of the interest you have been charged will be on those charges because some of your overdraft is made up of those charges. You should enter the interest on a separate line.

 

The advanced spreadsheet estimates how much of interest you can claim, but often this is all of it.

[bram stoker]

I've done the simple one, it worked fine, I wanted to try thr advanced one to see if it worked out any higher on interest.

I seem to have found a bug in it.

I'm now using the advanced one and it makes very little sense to me right now.

If i recall its number 5 the recommanded one if you have details which i have

If you go to my Chambers I've added some guidance notes. These might help.

 

Google takes some getting used to, so you may prefer to export the spreadsheet into Excel format, although I think you'll find the same "bugs".

 

What happens is that it tells you there is an error in your spreasheet until you have completed entering the data for the particular calculation it's saying there is an error for.

 

You may need to check your dates are correct too.

[bram stoker]

Oh, and the only difference is that you can estimate more accurately the interest that they charged to you that relates to the penalty charges they charges you. Again, see notes.

[bram stoker]

Ben - I didn't feel criticised. I put "bugs" like that, because they kind of are, but aren't if you know what I mean.

 

G16 is any interest the bank charged you, and H16 is your average overdrawn balance during that period where interest has been charged.

 

Good luck.

[bram stoker]

Don't worry - Vamps likes testing us don't you Vamps ?

 

Actually, whilst I have your undivided attention on one of my older credit card spreadsheets I have a whole list of charges each showing totals along the columns we are to expect - except the last 2. on these I had charges of £50 on /09/06/04 and on the next line £25 09/07/04 - as the account closed the card company did not charge me interest for those two months but under the 'interest on Penalties column despite the formula being correct theres no figures showing. I can understand no interest on interest but no interest on the charges?

 

Explain yourself!

It's a compete mystery. You say the formula is correct! Stranger and stranger. Can you send it to me or share it with me please and I'll take a look?

 

 

Explained by email.

[bram stoker]

I have used two alternative spreadsheets for different reasons.

 

1. I used the spreadsheet ~ Throughout claim with compounded contractual interest ~ however rather than take an average balance for the month ~ I took the account balance at the date interest was charged ~ is this likely to make a major difference?

 

2. In an attempt to induce the bank to settle early I used the spreadsheet From claim date with compounded statutory interest and once again rather than take an average balance for the month ~ I took the account balance at the date interest was charged.

 

3. There was a difference of over a £1000.00 betwenn the two spreadsheets ~ don't suppose it will mean too much to the bank ~ but attempted offer to induce early settlement prior court action.

 

4. On both spreadsheets I have four totals:

 

[a] Penalty Charges

Interest on Penalties

[c] Bank Interest on Penalty Charges

[d] Interest on Bank Interest on Penalty Charges

 

However when one writes:

 

I calulate that you have taken £xxxxxx [is this just [a] the penalty charge?] I enclose a schedule of the charges which I am claiming with this letter. I request that you pay me interest on this amount at a rate of 17.8%; being £xxxx [is this b+c+d?] This is your rate for authorised borrowing as of today, which I believe to be of a reciprocal nature due to the terms of the contract. The total amount to be refunded to me is £[a+b+c+d] as of today’s date, and interest will accrue daily at the contractual rate.

 

Have I acted reasonably correctly or have I boo booed?

You must be clear as to what you are asking the court for. Firstly you are requesting your charges. That's fine. Anything over and above that you will need to reasonably explain, possibly to a judge.

 

You may be able to calculate the interest they charged you on those charges, and request they also refund that.

 

You have a choice of asking for statutory or contractual interest. Statutory interest is awarded by a judge and cannot be requested beforehand.

 

If you claim contractual interest you need to understand why you believe that they should pay this to you.

 

If you want to compound the contractual interest (statutory cannot be compounded) you need also to be able to explain why you believe you can do this.

 

Once you have made your decision about what you can or cannot claim, stick to that one claim. If you are not convinced about what you can claim, how can you convince a judge?

 

Do not give them a choice. There is no choice. You make one request, and if they make you a lower offer you can decide whether or not to accept it. Be absolutely clear about what you are asking for.

 

My spreadsheets are not set in stone, they are simply templates to assist you. You must understand fully before you make any request.

 

This goes out to all here. Sorry if it sounds like I've picked on you, it's not intended that way.

 

I hope I've helped and good luck.

[bram stoker]

Firstly you may have used one spreadsheet which compounds interest, and one which doesn't.

 

Regarding the balance to enter, have you read the notes in my Chambers? The calculation apportions the interest you were charged between penalties and "true" overdraft.

 

The correct approach would be to do this on a daily basis, but the calculations are extremely complex and I have tried to simplify this in my spreadsheets by calculating only when the interest is charged using the average overdrawn balance for the period of charge.

 

This will give a good estimate.

 

Please ensure you all read the notes in my Chambers. Thank you.

[bram stoker]

Ok, and to all out there. Be careful. If you haven't been charged interest, you haven't been charged interest on your penalties and have none to claim back.

 

This is not the same interest as the interest you add - statutory 8% or contractual.

[bram stoker]

Then I think the difference must lie in the other spreadsheet you used, which probably isn't a compounding one.

 

Good luck. Glad to see folk here being thorough before rushing into this.

[bram stoker]

G16 is the entire interest for the month charged by the bank?

H16 is you average overdrawn balance until the end of the month of until you were no longer overdrawn?

 

Sorry to be a persistent pain but I want to absolutley clear before I take them to the cleaners.

You are usually charged once a month for a period of one month. It is this interest and this period the interest covered that you are dealing with. If you don't know the peiod covered, the interest often hits your account about 2 weeks after the end of the period.

 

Does this help?

[bram stoker]

Here we go 3rd time lucky, I'm looking at credit card compound contractual also.

Cell F15 downwards, is this the interest from the following month's statement or the interest charged until today?

This interest is the interest they charged you

on the month's credit card you are looking at.

[bram stoker]

Vamp quick Q while ur here!

 

I've used your wonderful spreadsheet to calculate comp cont int & notice that the daily rate in pence doesn't match the actual amount it adds on when I change the claim date (ie add on one day?)

 

I'm prob just being a thicko but could u let me know if this is right ... simple explanation please!

Which number spreadsheet please?

[bram stoker]

errrmmm ... Number 16 I reckon (I'm sure it was this one I used)

There is no daily rate on this one. Maybe it was on an old version, I can't remember.

 

Compounding interest will not have a daily rate in pence. You'll just have to state the rate. The pence per day will increase each time interest is added - daily.

[bram stoker]

Jay,

 

Did you delete what was already in the cells? You should not be deleting anything that is a formula.

 

You can download the template and copy the formula back in.

[bram stoker]

I have been reading the formula in your sheets Vamp, good grief!!!!!!

You really know your stuff.

I have had a go at the advanced credit card sheet, I'm not convinced the last 2 columns are correct, but then I feel I am questioning the person who made the sheets, which convinces me that it is ME that is wrong.

How does this sheet look to you?

mint.htm

 

sorry the formatting was lost when it went to htm but it is readable

Think more about the logic behind this.

 

Roughly, you were charged £50 interest on a balance of £5,000. £200 out of the £5,000 was penalty charges i.e. 200/5,000 or 1/25. So 1/25 of the £50 interest you were charged relates to your penalties, i.e. approx £2.

 

Looks about fine and dandy to me.

[bram stoker]

Which number spreadsheet did you use please?

[bram stoker]

OK, It's not number 16.

 

I've just closed Chambers, and re-opened them thinking they were all wrong. I need a holiday.

 

Please re-check what you have done and come back if you still think something is wrong. I agree the compound contractual interest looks wrong on your schedule, but have checked SS 16 and it works there.

 

Unfortunately I can't see your workings.

Sorry.