Excel Contractual Interest Spreadsheet

[lou5357]

Thank you for replying Fred, I was beginning to wonder if I write in a way that puts folk off from answering my threads!

The different rates were applied simply by downloading the spreadsheet twice, entering 28% in one and 15.9 in the other. (I need not have bothered though.)

I've now, as a result of manual help, inserted all the end of month interest charges too. The spreadsheet then picked up those where I had gone outside my overdraft and it's given me another £130.00 or so, which is great.

After a couple of glasses of dutch courage I phoned the bank last night and asked them what rate of interest they apply to my account: 2.20%. Stupidly (the penny hadn't dropped) I then asked what it changed to for going outside of my arranged o/d. 'It's the same rate', she said.

That's strange, I thought. I looked on their website and realised that this is equiv' to 28.8%! The cheeky so n so's treat my overdraft as though there is no arrangement, hence no interest change. I do wish I'd got my head around all of this sooner!

I'm not sure whether to write to the court now and tell them that I've made an error with the figures, or press on.:o

[suebrugge]

Hi there,

 

Hope someone can help me with my query before I submit my preliminary letter to Natwest. I want to be 100% sure that what I'm claiming is correct and that I'm happy with how I've reached the figure before I start anything off.

 

I have used Mindzai's v1.9 spreadsheet calculating the compound interest at a rate of 29.69% (which is Natwest's current unauthorised borrowing rate according to their web site).

 

The spreadsheet shows be a total of £1,748 in charges and £3,102.13 in interest up to today. However - and this is the bit where I get confused - the cell at the top of the sheet which shows "Daily Rate (For Claim Form)" displays a sum of £1.26. However when I change the end date of the claim period for test purposes I find that the total inc. interest amount increases by around £3.40.

 

I have posted this concern on my own thread in the Natwest section and someone has told me already that after having checked the interest added on a few sample lines that my figures are correct. However, I would really like to be able to understand why there is the discrepency between the actual increase of around £3.40 per day when I change the dates on the form - and the sated Daily Rate of £1.26.

 

Hope someone can help shed some light on this.

 

Thanks very much for your help.

 

Sue

[MgintyMIA]

Hi Sue,

Have a look at my question here:

 

http://www.consumeractiongroup.co.uk/forum/general/6964-spreadsheet-interest-73.html

and then Fred Funks response on the next page - he gives another link which I looked at.

 

Hope it's some help. If not, please post again and I'll try and explain.

[Fred_Funk]

suebrugge

 

Hi! I wasn in the same position as you - in actual fact, if you read back a few pages on this thread you'll see me pose exactly the same question.

 

The answer, in essence, is that the two figures should indeed be the same. The £3.40-ish figure is, as you suggest, the correct one.

 

Lucid has suggested that when revising his otherwise excellent spreadsheet Mindzai has omitted to take account of some additional cells he has added - hence the discrepancy.

 

It oughtn't to be a problem though - you simply need to amend the daily rate figure to agree with your calculations.

 

Hope this helps?!

Fred_Funk

[gandolfi]

First of all I'd like to say a HUGE THANKYOU to Mindzai and Lucid for this thread and for the fantastic spreadsheet. As a previously Excel-illiterate user I've found it surprisingly simple and straight forward.

 

However, I have a couple of questions which I hope you can help me with....

 

My thread

http://www.consumeractiongroup.co.uk/forum/natwest-bank/81525-gandolfi-natwest.html

gives details of my fairly complex and sizeable claim against Nat West for both business and personal accounts.

 

I want to claim for the interest charged by the bank on my business O/D and Business Loan. Both sets of interest were debited from the business account.

 

My question is how to enter both interest figures so that the spreadsheet can best calculate the correct proportion of each interest charge in relation to the accumulated penalties...?

 

(1) If my O/D balance is £4000 and the charges are £7000 the spreadsheet will automatically calculate that I can claim 100% of the interest.

 

(2) If I also enter the loan balance as £14000 the spreadsheet will calculate that I can claim back 50% of the interest.

 

In (1), £4000 of the bank charges have been set against the O/D interest.

So, in (2), shouldn't I only be using the remaining £3000 of charges to set against the loan interest (rather than the full £7000)???

 

This would seem more proportionate....but how do I do this on the spreadsheet? Or should I not...?

 

I read somewhere else that I should just set the full amount of charges against both items and let the bank work it out....but I want to be as accurate as I can be.

 

Please help.

 

 

:-?

[Fred_Funk]

Apologies for interrupting but this seems as appropriate a place as anywhere.

 

I'm on the verge of putting in a court claim including contractual interest (compounded). With this in mind I started my own thead...

 

Particulars of Claim when asking for compounded contractual interest

 

... in a bid to clarify one or two things. Unfortunately, someone has come along and managed, albeit inadvertently, to cause me to question everything I thought I knew.

 

I would, therefore, be very grateful if someone who's got this all clear in their head can go and take a look and clarify that I do, after all, know what I'm talking about.

 

Thanks in anticiaption

 

Fred_Funk

[MgintyMIA]

Hi FF,

Not entirely sure what the issue is but I would say that you're original thoughts are correct. My personal opinion is that sometimes the debate complicates things sometimes!

My own thoughts are for your POC, in the brief description, keep it simple. Have a look here:

 

http://www.consumeractiongroup.co.uk/forum/bank-templates-library/681-4-particulars-claim-n1.html

 

Then, in the POC section, just write "see attached" and attach the actual POC.

 

The whole interest thing is also fairly strightforward. Remember that the original claim to the bank asked them to provide the means as to how they worked out your charges. They have not given you that so you have attempted, as a lay person, to work it out as accurately as possible. It will not be possible to get the figures exact. If they dispute them then they should provide the means so that you can calculate correctly - put the onus back on them.

I've got to be brief as I'm at work but I hope this allays your fears!

Cheers

[neilwoods]

The whole interest thing is also fairly strightforward.

To be quite honest, anyone who starts of by saying: "The whole interest thing is also fairly strightforward" clearly does not grasp the full implications of the problem.

 

As someone said earlier in this thread, "Judges want to know the correct figures, not your estimate".

 

The problem is not where the bank caves in at the last minute, it's where, as in the Lloyds case, they simply do not show up and the judge decides to have a look at the case rather than simply finding for the plaintiff by default.

 

It is in those cases that you could completely foul up your case if the judge does not agree with the way you have worked things out.

[MgintyMIA]

The "Interest thing" I was referring to was the different types of interest and how it is applied. In my opinion and after reading many posts about the contractual compounded interest, it is fairly straightforward. Note the fairly. I'll admit that it took a little bit of reading and asking questions (as I got confused from reading other posts) but on the whole I still think it is fairly straighfoward.

 

Given the information available, it is not possible to know the correct figures. You can only estimate. Unless the banks are forced to disclose exactly how they work out the interest this will remain. And the banks disclosing their calculations will probably only happen when a test case is taken to conclusion.

 

I do understand the full implications and am fretting about my claim that I have submitted. Looking back on it, if I could change anything then I would have submitted three spreadsheets.

 

Spreadsheet 1 would show when I was in unauthorised borrowing and thus be charged that rate.

Spreadsheet 2 would show when I was in authorised borrowing and show that rate.

Spreadsheet 3 would be the totals.

 

Mind you, most of my charges were when I was in unauthorised borrowing anyway so I don't think that would make much difference.

 

I understand your point about the judge wanting to know how you worked things out and it is up to the claimant to have this knowledge. This is why I have asked where the formulas Mindzai used to calcuate the interest are - see my post #313 and Mindzai's response post #315 on page 16 of this thread. I am in the process of understanding that formula precisley for the reason of explaining why I have estimated etc.

 

I hope this clarifies my position a little.

[neilwoods]

Reading your earlier posts has now added a whole new layer of confusion for me (to add to that caused by the 'helpful' moderator who has just stated that they don't agree with CI and know of no legal reason for it ).

 

Are these posts refering to calculations of that actual interest payed?

 

Are you, like Fred Funk, claiming both contractual interest and actual interest payed.

 

I am (was) on the verge of claiming for the unlawful fees and the CI on those fees.

 

For the CI the calculation is very straightforward, being, for each charge, the amount of the charge multiplied by (( one plus the daily rate of CI) to the power of the number of days since the charge).

 

As has been the case so often with this site, everything is printed, in the envelope and ready to be posted when suddenly someone posts with an entirely new slant on things.

[MgintyMIA]

Hi Neil,

I'm sorry if I've confused you. What on my posts has confused you?

My posts quote letters I received from the solicitors and show that they have not read the spreadsheet properly as their replies demonstrate.

 

I am claiming:

charges

o/d interest

contractual interest

 

in my poc I asked for the higher unauthorised rate or, in the alternative, the authorised rate or, in the alternative, the stat 8% thereby giving the judge the options.

 

Stick with it - claim the CI. If you don't try, then you won't get it but make sure if your claim goes to court that you provide the alternatives. Look at mindzai/ lucid's thread.

 

Like I said, sometimes the debate confuses the issue!

[Fred_Funk]

The "Interest thing" I was referring to was the different types of interest and how it is applied. In my opinion and after reading many posts about the contractual compounded interest, it is fairly straightforward. Note the fairly. I'll admit that it took a little bit of reading and asking questions (as I got confused from reading other posts) but on the whole I still think it is fairly straighfoward.

 

Given the information available, it is not possible to know the correct figures. You can only estimate. Unless the banks are forced to disclose exactly how they work out the interest this will remain. And the banks disclosing their calculations will probably only happen when a test case is taken to conclusion.

 

I do understand the full implications and am fretting about my claim that I have submitted. Looking back on it, if I could change anything then I would have submitted three spreadsheets.

 

Spreadsheet 1 would show when I was in unauthorised borrowing and thus be charged that rate.

Spreadsheet 2 would show when I was in authorised borrowing and show that rate.

Spreadsheet 3 would be the totals.

 

Mind you, most of my charges were when I was in unauthorised borrowing anyway so I don't think that would make much difference.

 

McgintyMIA

 

Hi. And thanks for your response. At the risk of complicating things further, why would you want to sumbit three spreadsheets?!

 

If you're asking for contractual interest compounded - according to the principle of mutuality and reciprocity - then, surely, it's irrelevant whether your account was in authorised/unauthorised borrowing at the time any charge was levied.

 

All the charges that the bank has levied on your account are unlawful and, in effect, the equivalent of its 'unauthorised borrowing', therefore you are charging it contractual interest (compounded) on those charges using the argument for mutuality and reciprocity. This being the case, the state of your account at the time is, IMHO, irrelevant.

 

Am I making any sense?!

 

Fred_Funk

[neilwoods]

Hi Neil,

I'm sorry if I've confused you. What on my posts has confused you?

I cannot be sure whether they are questioning the CI calculation or the actual overdraft interest calculation.

 

If they are questioning the latter, I'm not surprised. There is virtually no way that any two people are going to agree on that can of worms.

 

If they are questioning the former, then it's a worry.

 

My claim fo CI is simply the sum of compound interest on each unlawful charge at the bank's current unarranged overdraft rate.

 

I'm minded to send it off anyway as every time I visit this site I get completely conflicting advice. Unless I actually make a decision and claim something I will, in about three months, exceed the basic 6 year window!

 

I think that the actual situation at the moment is that the banks will pay pretty much whatever you ask unless it is totally unreasonable.

 

Your only risk is that they goof up and forget to cave in at the last moment and a meddling judge decides to have a look at the case rather than awarding a default judgement. (As appears to have happened in the Lloyds cases).

 

Of course, if your claim was wholly unreasonable they might be able to get it struck out without ever examining the legality of the charges which is a course of action they would undoubtably take.

[MgintyMIA]

@ FF

The reason for 3 sheets would be as stated - to get more accurate figures in a way would be similar to the banks method.

 

Say for example you have an agreed limit of £1000

month 1 balance = -£500 thus charges = overdraft fee - CI charged at authorised borrowing rate

month 2 balance = -£1500 thus charges = overdraft fee + excee o/d fee - CI charged @ unauthorised rate.

 

This would lead, I believe, to more accurate figures of what was actually owed. That said, I do agree with your argument of unauthorised borrowing!

 

So let's just leave it at that. If I have to argue for it in court I will use the fairness & balance argument.

 

@ Neil

 

I don't beleive it matters what they are questioning - they are just stalling. I can provide the basis of my calculations however I'm not going to unless I'm in front of a judge. The bank will not disclose their calculations so I'm doing the best I can.

 

I would check your figures against the spreadsheet done by Mindzai. I can't comment on your calculations compared to his formula as I haven't interpreted it yet. If they match then all is well.

 

There is of course a risk going to court which is why you must be prepared and understand things. There have been a couple of 'wins' for the banks but if they were so certain, there wouln't be wuite so many 'wins' for the consumer.

 

At present I have two claims and have filed my AQ. One one claim, they have filed their AQ so I am awaiting directions whereas the other they have not filed an AQ so I am going to reming them 1st as I don't want to enter default judgement yet (although I am entitled to).

 

Now, I really must get on with some work!!

bye for now

[Volkswagen]

I am about to send off my first letter to the bank, however, I am confused. Can I reclaim the service fees given that banks are now abolishing them on new accounts.

 

Has anyone been repaid these fees.

[crystalcag]

Hello,

Having just tried to get my head round the last flurry of messages I think I've got a long way to go before I understand this CI business. (which is scary as I'm claiming it!).

I've just started a claim against A&L. I opened a parachute account with them at the beginning of the year (I also have a claim with LLoyds - taking N1 to court on Monday). They have managed to charge me £379 in that time. When I first opened the account I stupidly thought their internet site AND ATM machines would give accurate balance details. Unfortunately as a result there were a couple of times when I had insufficient funds to cover a some direct debits and cheques. They admitted themselves that the only way of really knowing what's happening to your account is to phone them or keep a manual record.

 

Anyway... I really want to understand how the spreadsheet works so that I can check what I've done to make sure it is correct.

 

I've noticed something strange - it's probably very simple but I'm confused

Over the 6 months I have been charged a massive 49p interest on my account. On the spreadsheet the interest paid on penalties is 0p. How can this be when the majority of my overdraft was due to charges? Perhaps my question will come as some light relief

thanks

[Josie8]

Can anyone point me in the direction of the spreadsheet that works out compounded contractual interest for credit cards?? My heads spinning.....can find those relating to banks but not credit cards......................

[MEOB]

Hi Mindzai,

 

Thanks for your excellent spreadsheet.

 

I am having the same problem as Fred_Funk.

 

I checked the spreadsheet and the daily interest formula covers rows 18 - 250. I only go to row 120, so it's not that the formula is wrong in this way (i dont think).

 

Any idea why the daily interest amount is lower than the actual increase per day?

 

It may be that lucid's original explination is correct, in that this figure is a more simple calculation of daily interest based on the total owed rather than a compounded daily figure. Can you confirm this?

 

Just confusing as I don't know what to put in my LBA!

[Fred_Funk]

MEOB

 

There's definitely a little glitch somewhere in Mindzai's otherwise excellent spreadsheet.

 

All I did was Tippexed out the figure in the 'Daily rate (for claim form)' box - which, by common consensus, is, in some instances, incorrect - and replaced this with a figure equating to the amount by which the total sum had risen between the day of my correspondence and the previous day.*

 

Hope this is some help

Fred_Funk

 

* Obviously, with CI involved, this figure will rise incrementally day on day but taking the figure by which it rose in the 24-hour period immediately before your correspondence will provide as accurate an estimate as is possible of how much it is going to rise by in the future.

[MEOB]

Hi Fred,

 

Thanks for responding.

 

I have looked at the formula in more detail, and to calculate the daily rate the spreadsheet adds all the penalties and all the interest on penalties, and then applies the daily rate of interest to this total (monthly rate/30.4).

 

Effectively you get the daily rate as if this amount was only outstanding from today as it does not compound from the date of charge for this figure.

 

I guess that the further back your charges go (mine go back to 1997) the more 'inaccurate' this figure becomes.

 

I think your approach is the right one, but I have already sent my prelim letter stating the 'wrong' figure, so I don't know what to do on my LBA now!

[Fred_Funk]

MEOB

 

Hi. Given you're just about to send your LBA, it seems safe to assume that, thus far, the bank have been unco-operative.

 

That being the case, I'd simply correct yourself in your LBA and go from there. The reality, I imagine, is that the bank will pay scant attention to either your prelim or LBA so I wouldn't be too deterred by having got the figure wrong initially.

 

Fred_Funk

 

PS Without wishing to cause you too many further complications, I assume you're aware of the following thread...

 

http://www.consumeractiongroup.co.uk/forum/halifax-bank-bank-scotland/97691-contractual-interest-precedent-lost-5.html

[MEOB]

I've seen it.

I will be claiming Authorised borrowing rate in the alternative, and statutory in the alt. to that.

I also will be arguing unfair enrichment in the alt. to mutuality. The Judge can then decide what rate is fair.

 

I have managed to fix the calculation on the spreadsheet, and it is now calculating a correct daily rate for me. I’d be happy to give details of how to anyone who is interested, but it is a bit involved, so only ask if you are confident with excel!

 

Mindzai/Lucid – if you want to know what I’ve done for an update, let me know and I’ll explain.

[MEOB]

Sorry, unjust enrichment is, I think, the correct term.

[Antoniab]

Hi, there. I 'm confused about the CI rates and what do do. I can't open the spreadsheet. Is there a Word Pad version?

 

Do we put the monthly charge figure in each time (for Bank Account Charges) then it calculates for you? And is it all at 8%? I've seen some other % figures and am really confused.

 

Yours

 

Antonia

[MEOB]

Hi Antoniab,

 

If you want to learn about CI, read here:

http://www.consumeractiongroup.co.uk/forum/general/18313-why-no-one-claiming.html

 

If you have any more questions, it is best to start your own thread as this one is about Mindzai's spreadsheet, and we shouldn't hijack it.