Thread: Help: Ppi Spreadsheet Required

Fingers crossed, some kind soul will be able to point me in the right direction... I'm after a spreadsheet which can deal with mis-sold PPI on a Natwest loan and the interest incurred as a result of this.

Futhermore, I'm also looking for compound interest, at the rate charged to me by NatWest, to compensate for their unjust enrichment and would, therefore, like a spreadsheet which can cope with this too.

I've spent the last couple of hours searching high and low on here but, alas, to no avail. The only thing I can find are the spreadsheets for current accounts and credit cards in the templates library and, try as I might, I simply can't get 'em to do the job.

Please, someone point me in the right direction!

As ever, thanks in anticipation
Fred_Funk

Similar Threads:

• help required for spreadsheet (halifax)
• spreadsheet glitch? help required please

Hi Fred. I discovered this interest calculator a couple of weeks ago and it's been invaluable - hope it's of some help to you.

New Square Chambers - Interest Calculator

Thanks deb4tlj but I don't think that's quite what I'm looking for.

I just want a spreadsheet which can calculate how much of the loan repayment I made every month was attributable to the mis-sold PPI and can then calculate the compound interest accordingly (as an equitable remedy to Natwest's unjust enrichment).

Anyone?!

It may just be me but, as far as I can see, there isn't a template available there which does compound interest (as Vamp's used to do).

The reason you are having difficulty is because there is no ready made spreadsheet to do this. You will have to use dualcalc I think. I was going to post a link but it seems the OFT server now wants a usernamd and password.

Try yourself later - Google 'dualcalc oft' to find the link. You can download it to your own PC. It is not very obvious how to use it but I think the way to calculate what you want is as follows:

Put in the total loan (loan+PPI), monthly repayments and number of repayments. Click 'calculate' and it should come up with the correct APR. All these figures should be on your loan agreement.

Repeat the calculation with just the loan, same number of repayments and then do several runs with different monthly repayments until you get the same APR.

The difference between the two monthly repayments is then the monthly chareg for PPI+interest

If you can't get your head round that (or can't download dualcalc) perhaps you can send me the figures and I'll have ago.

Originally Posted by Fred_Funk

Thanks deb4tlj but I don't think that's quite what I'm looking for.

I just want a spreadsheet which can calculate how much of the loan repayment I made every month was attributable to the mis-sold PPI and can then calculate the compound interest accordingly (as an equitable remedy to Natwest's unjust enrichment).

Anyone?!

You could always have a go at doing your own spreadsheet from scratch - found this great site to help do just that.

Excel Spreadsheets for Beginners: Financial Functions

The problem is that you have to use an iterative solution method to solve the equations - there is no formula to do it

I have actually done a spreadsheet, which gives a fairly accurate figure and helped me with my sucessfull calim with First Plus.

It's not perfect, but my figure was very close to what First Plus refunded (<1%)

I have sent it to a couple on Mods for review and upload to the site, but no one has come back to me

Originally Posted by steven4064

The problem is that you have to use an iterative solution method to solve the equations - there is no formula to do it

?????

If you are guessing your figures then yes, but if you do your SAR and get the actual figures from the lender, then it is possible to calculate!

I would be interested to see it Bog - if you want, send it to my CAG e-mail

The SAR won't give the figures in the detail Fred needs for what he istrying to do, which is to separate out the PPI from the loan.

I'll send it through to you, as I would welcome the feedback. I'm far from an expert on these calculations, but if between us we can create a spreadsheet which other CAG users can use (If relevant to their circumstances)

Thanks to everyone who's taken the time to contribute to this thread.

I think I've finally managed to get my brain into gear and sort things out - though if someone can confirm my figures sound about right it would be much appreciated.

Fortunately, I have my old statements from which I deduced I had a loan for just over 4k - of which £553 was, IMO, mis-sold PPI (or PLP as Natwest prefers to call it) - and made 36 repayments of £140.19.

By my reckoning that means I repaid a shade over 5k in total and, according to a loan calculator I found online, the APR was 15.94% or thereabouts.

My old statements also detail the Breakdown of my 36 monthly repayments - in June 1998, £50.14 of the £140 was interest by May 2001 it was just £1.70 - and using them I've come up with some figures to input on a version of Vamp's compound interest spreadsheet for current accounts.

That tells me the £553 I paid for my mis-sold PLP in 1998 with compound interest at NatWest's 15.94% now equates to £2,510.96. In addition, the interest paid on penalties over the three-year term totals £235.79 and the compound interest on this is a further £620.05. This produces a grand total of £3,366.80.

So, my question to you guys, do these figures seem feasible?!

As ever, thanks in anticipation
Fred_Funk

OK I checked - loan of £4053 paid by 36 installments of £140.19 = 15.9% APR

£553 at 15.9% over 10 years gives interest of £2418.57 altough the interest you actually paid is over 3 years - £860.95

Steven

Thanks for taking the trouble to have a look at this.

Just not quite sure what you're trying to say in your second line... where does the £860.95 figure for interest I actually paid over three years come from?!

Are you saying some of my calculations are incorrect?

Fred_Funk

The £860.95 is £553 at 15.9% over three years - but that's not what we want to calculate anyway because it ignores the payments you made.

It goes back to what I said a few posts ago - we need to do this with dualcalc.

I'm tied up until later tonight (possibly tomorrow) but I'll try and have a look at it and come up with the 'right' answer

Thanks Steven. I've emailed you a copy of my spreadsheet which may or may not help you shed some light on the situation. Much obliged.

Fred_Funk

As I said above, I don't think you can do it with a spreadsheet - for the reason you hint at in post #14 - the £140.19 contains £50 interest in May 2001 and £1.70 by near the end. At the beginning, virtually all the £141 would have been interest.

This is what dualcalc gives:

Paying back the total of £4053 in 36 payments of £140.19 gives an APR of 15.9%

Taking off the PPI gives a laon of £3500 also payable in 36 installments at 15.9% - dualcalc gives a monthly repayment of £121.06

So if the same loan had not had PPI that is what you would have paid per month - £121.06. the PPI therefore cost £19.13 per month.

You paid 36 installments of £19.13 for the PPI and interest thereon, which comes to £688.68 .

Steven

Thanks, again, for the explanation. Unfortunately, I think my lack of clarity in explaining the situation might account for our differing interpretations.

I understand your rationale for calculating the PPI at £19.13 per month.

However, my statements reveal that Natwest took the £553 in one lump sum at the outset and not bit by bit throughout the duration of the loan.

This is why, on Vamp's spreadsheet, I have put the mis-sold £553 PPI down as if it were a penalty charge, giving rise to a total of £2,510.96 when compounded at 15.94% from April 1998 to the present day.

Does that make sense? It seems logical to me.

I've then taken the interest element of the £140.19 I've repaid each month - fortunately, my NatWest statements reveal this figure - and multiplied it by 553/4053 to calculate the proportion of the interest attributable to the mis-sold PPI.

Then again, I've entered these figures on Vamp's compound interest spreadsheet as one would to calculate the interest paid on a current account attributable to unlawful bank charges.

The interest attributable to the mis-sold PPI is £235.79, which produces compound interest of £620.05.

Again, does this now make more sense? I hope so, lol.

Thanks in anticipation
Fred_Funk