PPI Compound Interest Spreadsheet - Monthly Card payments

[nortie]

Hello all,

 

I am attempting to calculate the compound interest due on the PPI payments I have made back to 1994. The FOS has told me my card company have agreed to make me an offer to settle, but they refuse to tell me how much they'll offer until I sign a F & F settlement document.

 

I've made it clear that I'm not prepared to do that until I've seen the offer, and so we're at stalemate.

 

In the meantime, I'd like to start calculating the compound interest on these figures, and while the online calculator at EGALegal is very useful, if/when I get the actual interest rates the card company charged, I'll need to recalculate every one of the 194 months' worth of figures, so I hoped I could set up a spreadsheet.

 

However, try as I might, I can't find one which deals with this specific calculation. I'd like to be able to just enter the PPI premium paid, the date it was paid, and the relevant interest rate at the time, and let it calculate each compounded payment to a specified date (e.g. 1st December 2010).

 

Is this beyond a spreadsheet's capabilities? I'd happily pay for it if there is one that works.

 

While we're on the subject, please can someone confirm my initial calculations using the compound interest calculator and simple interest calculators mentioned elsewhere? (I can't link to them here - the forum won't let me )

 

Am I right that if I paid £18.76 PPI on 1st Feb 1997, on a card with 17% contractual interest rate, that by 1st December 2010 that should result in a repayment due of £200.37, and then if 8% S69 simple interest is added, it would be £422.19?

 

And, by the way, has anyone checked the accuracy of these calculators? If I go to court and quote them, will they be accepted as correct?

 

Thanks to everyone who contributes to this forum - what a superb resource. I'll keep you informed about my case as it progresses.

[rebel11]

Hi nortie

 

You want Advanced Credit Cards speadsheet. - http://www.consumeractiongroup.co.uk/forum/content.php?559-Interest-Calculation-Spreadsheets.

 

Stick to your guns, you want to know how much they are going to compensate before you sign anything.

[noomill060]

DO NOT SIGN until you know how much they want to settle for.

[nortie]

Thanks for this rebel11, but it looks much more complicated than I thought it would be.

 

Reading Livingdeadguy's guide to how to calculate compound and simple 8% interest (on the PPI forum sticky threads) the whole process seemed quite simple, but the advanced spreadsheet seems to want to know account balances and penalty charges, etc. Is it easy (ish) to construct the spreadsheet as I outlined above, or does that give a false idea of what's owing?

 

As for signing before knowing the offer, it's just never going to happen. To me, that's like writing the opposite of a blank cheque, and it smacks of the credit card company wanting to attempt a cheap fix, and that is not on my agenda. Who in their right minds would sign? It's like being offered the chance to buy a house, but not being allowed to see the house, or know its price, until you've signed a legally-binding document to do so. Absurd, and actually quite insulting.

 

My FOS adjudicator says they try to sort it informally if they can; I have said that if the credit card company believes its offer is a fair one, it should tell me BEFORE I sign, and it shouldn't worry about the document. Only if they're up to no good should they worry about me rejecting it.

 

If they say "no" to an offer before F&F signature (which I fully expect), it then goes to an Ombudsman or court, whichever is quicker. I just bought CAG's User-Friendly County Court Guide, just in case...

 

So, if anyone can verify my calculations outlined in the first post, or is a whizz on Excel to help with the spreadsheet I'm looking for, please get in touch - and thanks to all for their help so far.

Edited November 1, 2010 by nortie
typo

[steven4064]

Am I right that if I paid £18.76 PPI on 1st Feb 1997, on a card with 17% contractual interest rate, that by 1st December 2010 that should result in a repayment due of £200.37, and then if 8% S69 simple interest is added, it would be £422.19?

Monthly interest rate is 1.31%, 01/02/97 to 01/12/10 is 154 months. £18.76 with 1.31% interest per month over 154 months is

 

18.76*(1.0131)^154 = £140.69

 

So, no , your calculations are not right. The 8% interest is even more complicated as you need to do it for each monthly interest charge. This is why you need the spreadsheet. There is an explanation of how to use it in the 'Interest Turorial' link in my signature.

[nortie]

Hello Steven4064,

 

Thank you so much for your message, and for the interest tutorial which I'd love to read... but can't find. I clicked on your signature link and had a nose around but can't (yet) see it.

 

As for the calculations outlined, I followed livingdeadguy's instructions to the letter - and got completely different figures compared to yours, so surely his post should be amended or removed to stop other caggers making the same mistake? The online calculators linked to on his post are deceptively easy to use... but it appears they're just deceptive.

 

Hopefully I'll be able to read your tutorial soon and get on the right road.

 

Many thanks again,

 

Neil

[rebel11]

Hi nortie

 

http://www.consumerwiki.co.uk/index.php/Interest_Tutorial

 

Thanks Steven

[nortie]

Hello again,

 

I notice trying to use the complex credit cards spreadsheet that the calculation does NOT compound the interest. Surely when the credit card company charged me interest, they absolutely compounded the interest?

 

Now I am totally confused!

 

p.s. Thanks to Steven for the link to the tutorial - got it!

[rebel11]

Hi nortie

 

Generally when people have claimed back Penalty Charges, PPI, they have used the existing Contractual Interest rate. So they have used one rate throughout the years. Thats what you should do. Maybe work out the average.

[nortie]

OK - as I don't have the statements back to 1994, I am proposing to use the APR on the T&Cs they sent when I did the SAR, which is 18.9%. Incidentally, these were not the T&Cs from when the card was issued (naughty!) but the current T&Cs.

 

What I'm most concerned about is the compound interest calculator linked to from LivingdeadGuy's thread in this PPI forum - is it wrong, because it seems to give a higher figure than Steven4064 got.

 

Hopefully will get to the bottom of it soon - thanks for all help offered.

 

Neil

[Bridgemaster]

Hi Nortie,

first off congrats on getting as far as you have with your claim, especially going back as far as 1994,

I am just about to start a similar claim with the same card provider back to 1993,

if you could possibly give a run down on exactly how you went about it to start with it would be most appreciated ,cos I don't want to miss anything out, only info I have so far are about 12 months worth of statements. any advice or info appreciated. I realize there are multiple threads re this on the site but with so many links it gets confusing as my concentration span is not what it was.

Thanks BM

[boss lady]

Hi All

 

I found the FOS a complete waste of time. I done a cliam for my son against Lloyds TSB and found the same thing, they wanted my son to sign as having agreed to the sum offered without actually being informed of the offer. I even amened their form and they would not accept it. I really do not know of anywhere else except the FOS where you sign an agreement without knowing the figures.

The FOS took 12 months to reach this point, what a complete waste of time, some intermediary. I have now issued a court summons on behalf of my son against Lloyds TSB.

[nortie]

Interesting, boss lady. It has taken the FOS since April 2008 to investigate my case... and then this happens. Well, I've asked them to go back to the card company and tell them I accept their offer (I suspect that will help in court) but I won't sign as F&F until I've seen the figures. I don't think that's unreasonable, and if the card company won't give me the figures, it'll be time for court.

 

I may have to wait for an Ombudsman to ratify the deal with my adjudicator before I go to court, I don't know. I would like to think that an FOS adjudicator's decision in my favour would be enough to persuade the court that I have a good case, but you never know.

 

Bridgemaster - I asked my card company for complete, total and unabridged details of my account from its start date to present day. They say they don't keep records that far back; the FOS say they do, so I have estimated my figures for the lost years, generously towards me. I guess that will force their hand; they'll either have to miraculously "find" the missing figures and disprove my estimates, or just swallow my figures. We'll see. Good luck anyway.

[boss lady]

Good luck with that nortie because when I said my son would not sign the form until figures were released the FOS said there was nothing more they could do. Great help, we waited a year for that answer. I really am going to complain.

[nortie]

Good luck with that nortie because when I said my son would not sign the form until figures were released the FOS said there was nothing more they could do. Great help, we waited a year for that answer. I really am going to complain.

 

Oh joy. Did you get to Ombudsman level or adjudicator?

[boss lady]

It was the Adjudicator. Just one section went as follows "Please note that by signing the form you are accepting the general approach to calculating the settlement on your complaint. Lloyds TSB will then write to you with the exact figures. If you believe that the firm has made an error in the way it has carried out the calculation please contact the firm initially to discuss this. If you are unable to resolve the matter with the firm, we will be happy to assist you". The FOS assist what they mean is wait another year for them to assist.

I really do not believe the FOS expect people to sign a form to agree to a settlement without the figures being produced. What planet are they on.

[nortie]

 

Without meaning to appear like a clever dick, I make it 166 months - is that right?

 

Also, I now know the "standard interest rate"[as it is listed on the T&Cs] is 18.9%, not 17%. So please could you verify the correct compounded interest due to me on £18.76 from 17/2/1997 to 17/12/2010.

 

The EGALegal compound interest calculator says Interest accrued is £207.26.

 

If this isn't correct, should CAG not stop promoting this link?

[noomill060]

I make interest accrued as £241.80 for those dates.

 

(Compounded monthly with a 360 day year, as used by the banks.)

[steven4064]

OK, lets start again!

 

1997 - 2010 inclusive is 14 years, 168 months. We need to lose Jan 97, which gives 167 months. I obviously lost a year in my previous calculation

 

18.9% APR is 1.01453% per month so we have 18.76*(1.01453)^167 = £208.67, which is very close to the EGALegal compound interest calculator result. Using the monthly value of 1.0145% gives the same answer as them - so we are the same to within rounding errors.

[noomill060]

Its a shed load of cash which you are owed however you calculate it, but dont be over-awed by the magnitude of it.

[steven4064]

Its a shed load of cash which you are owed however you calculate it, but dont be over-awed by the magnitude of it.

That at least we agree on

[nortie]

Thank you for putting my mind at rest - and for your patient guidance.

 

It IS a lot of money, but they've been fleecing me for years, so I won't shed a tear putting the claim in.

 

More when I get it - and thank you all again.

[noomill060]

They've been fleecing ALL of us, nortie

 

(And we the CAGers have been forcing them to pay it back (with interest) for the last four- nearly five years now.)

 

They are well used to humiliating climb downs, but you need to let them know that YOU know.

[Smoothound]

Bridgemaster - I asked my card company for complete, total and unabridged details of my account from its start date to present day. They say they don't keep records that far back; the FOS say they do, so I have estimated my figures for the lost years, generously towards me. I guess that will force their hand; they'll either have to miraculously "find" the missing figures and disprove my estimates, or just swallow my figures. We'll see. Good luck anyway.

 

This is where I am currently Nortie. Still waiting for the full figures going back to '94 and am about to send a LBA letter giving them 7 days as they have had around 3 months from the original SAR and then another 40 days that they gave themselves which has also now expired. I'm quite looking forward to guesstimating some figures to put in writing to them

[Smoothound]

I make interest accrued as £241.80 for those dates.

 

(Compounded monthly with a 360 day year, as used by the banks.)

 

I'm now looking at this for myself and I made it the same as you! It's a bit confusing when people are getting different amounts from the same figures and calculator. Guess it must be just poor data entry then?