Excel Contractual Interest Spreadsheet

[sjcwhuk]

I have read it once, twice, three times...

 

Still got to get my head round it. But rest assured come judgement day we will see who blinks first claimant or the lowly defence lawyer!!

[bill-k]

...we will see who blinks first claimant or the lowly defence lawyer!!

QED !!

[kologna]

Awesome spreadsheet - does what it says on the can.

 

How do you calculate the APR....i have a CLASSIC account with an AER of 4.25%.

 

I thought the APR only applied to loans and credit cards

 

[bill-k]

How do you calculate the APR....i have a CLASSIC account with an AER of 4.25%.

 

I thought the APR only applied to loans and credit cards

 

That's the usual idea, but if your card doesn't have any one-off charges made for certain transactions (eg cash advances, etc.) perhaps they may quote the AER instead.

 

However, if your CLASSIC account is a current account, then the AER or EAR will be quoted instead.

[Mad Nick]

From post 148,

You then raise the figure you obtained from P*(1+r) to the power of n

No, it's (1+r)^n then multiply by P, otherwise you'd be doing P^n x (1+r)^n. Regards, Mad Nick

[kologna]

That's the usual idea, but if your card doesn't have any one-off charges made for certain transactions (eg cash advances, etc.) perhaps they may quote the AER instead.

 

However, if your CLASSIC account is a current account, then the AER or EAR will be quoted instead.

 

Thx Bill

 

It is a classic current account, and i am still confused as to how to work it the daily & monthly interest rates as the spreadsheet requires the APR figure ----and i have an AER of 4.25%:twisted: :???:

[bill-k]

From post 148, No, it's (1+r)^n then multiply by P, otherwise you'd be doing P^n x (1+r)^n. Regards, Mad Nick

I find P*(1+r)^n works for me.

I put the formula on row 1 of my Excel spready exactly as "=B1*(1+(E1/365))^D1"

Where B1 is £100.00, E1 is 20%, and D1 is 365 days, this gives me £122.13

I believe the mathematical hierarchy used forces it to use this set of priorities:-

The spready works out (E1/365) first, to give 0.000548,

then adds 1 to give 1.000548,

then raises this to the power of 365 to give 1.221336,

then multiplies this by 100 to give 122.1336

 

If I try punching the formula in in the order "P^n*(1+r)^n" it just tells me to #### off !!!

[bill-k]

Thx Bill

 

It is a classic current account, and i am still confused as to how to work it the daily & monthly interest rates as the spreadsheet requires the APR figure ----and i have an AER of 4.25%:twisted: :???:

 

I think you will be correct in entering the APR on the spready as 4.25%.

[Mad Nick]

Bill,

I find P*(1+r)^n works for me.

That formula is spot on and the way Excel works, it will do the (1+r)^n before multiplying by P. It was just that you described it wrongly as "raise the figure you obtained from P*(1+r) to the power of n." That would be [P*(1+r)] all to the power n which, as you computer says, is ****** Regards, Mad Nick

[kologna]

Unfortunately Bill - The contractual interest TOTAL is less than the statutory 8% TOTAL when using the 4.25%....so i don't think that this is right.

 

Could i pm somebody and maybe someone can look at my spready...working with a figure of 29% seems more reasonable...but that is just a pie in hte sky figure that i remember somebody referring to on another thread.

 

Either a dumb moment or too many red wines for mother's day has got me confused on this spready...help please.

 

Kolo

[bill-k]

Bill, That formula is spot on and the way Excel works, it will do the (1+r)^n before multiplying by P. It was just that you described it wrongly as "raise the figure you obtained from P*(1+r) to the power of n." That would be [P*(1+r)] all to the power n which, as you computer says, is ****** Regards, Mad Nick

 

Nick - my apologies for misunderstanding you !!

 

You are indeed correct, and the way I worded the analysis in my post was indeed incorrect.

 

My thanks for pointing it out as quickly as you have done, and I have now corrected the post to show the method, as you have kindly now clarified.

 

Cheers mate.

[bill-k]

Unfortunately Bill - The contractual interest TOTAL is less than the statutory 8% TOTAL when using the 4.25%....so i don't think that this is right.

 

Could i pm somebody and maybe someone can look at my spready...working with a figure of 29% seems more reasonable...but that is just a pie in hte sky figure that i remember somebody referring to on another thread.

 

Either a dumb moment or too many red wines for mother's day has got me confused on this spready...help please.

 

Kolo

 

Kolo - the dumb moment is mine !! Yes, of course, if you claim 4.25% compound interest, then it will amount to less than 8% simple interest would !!

 

You need to check what the bank's current annual interest rates are for authorised and unauthorised overdrawing. You should be able to do this by checking online. I think you may then have a higher rate you can use in your spreadsheet.

[LEECHTHEBREACH]

Kolog, no way is your aer 4.25%, otherwise you would be lending the bank money(!), that is less than the base rate. If you tell me exactly what account you have and who with then i can tell you your APR

Leech

[sortingitout]

Is there a spreadsheet like this for credit cards?

[Seahorse]

Is there a spreadsheet like this for credit cards?

http://www.zen122856.zen.co.uk/CompoundSheet_CC_v1.1.xls

[sortingitout]

Thankyou

[tifo]

i've been looking for the right spreadsheet to use for a CC claim, but this one also only calculates the 8% stat and not the same compounded rate (maybe around 16%) the bank charged me, which is what i thought most people claim?

[LEECHTHEBREACH]

This the right one Tifo, the compounded part is on the green daily tab!!!!

Once you have filled in the charges click on it and you will see.

Leech

[sn00psmum]

Please can anyone advise? I'm working on my spreadsheet (Mindzai's v1.9) i'm almost finished putting my charges in, about 20-30 lines to go. I'm on line number 194 and every time I try to insert the charge I get a pop up message saying 'protected cells cannot be modified'. What should I do?

Sorted it now, duh! Can't seem to delete this message though!

SM

[isidore]

Mindzai has protected some cells to ensure precious fields aren't overwritten. I'm sure this is probably something that has been overlooked and will be amended and a new release will come out but in the meantime, if you go to TOOLS then it will say something along the lines of UNPROTECT SHEET (no precise as I don't have excel to hand right now). Hopefully there are no passwords on it, this will then allow you to carry on. If you have any problems, there may be advice further up in this thread with regards to unprotecting the sheet (and it might be more correct lol)!!

[sn00psmum]

T4FF you're a sweetie and always reply really quickly- do you live here?LOL

 

You were right of course, simple case of unprotecting sheet, i've done it now and spreadsheet is done, yippee!

 

ps. thanks for kick up the bum earlier!

SM:p

[isidore]

do you live here?LOL

[fatboy88]

hi, so if you claim compound you can claim contractual interest as well. I cant seem to do mine right. Can i PM someone with my sheet so they can see where i've gone wrong

 

many thanks

[pc23]

I'm about to send my LBA to Cap One - but I have some serious doubts about the spreadsheet I have used (taken from here... 6. Interest calculation spreadsheets I have used England - Advanced - Excel). Or put more accurately how I've used them. In a nutshell... the sheet does the following to calculate simple contractual int.

 

(Total amount charged in fees over the period of time)

 

Divided by

 

(The statement balance Month 1)-(the payment made in respect of that eg. the one which appears on Month 2 statement)

 

Multiplied by

 

Total interest charged as per Month 1 statement

 

= interest to reclaim for Month 1

 

I might be being thick, but I don't understand why the following month's payment needs to be deducted? Can anyone explain to set my mind at rest?

[pc23]

fATBOY 88 send it over and I'll have a look if you like.