Excel Contractual Interest Spreadsheet

[progenic7]

Doo im using it on a live claim RBS credit card

 

Mindzai if you are around could you explain the box that says "balance paid over next month" im not sure what you mean by that ?

[isidore]

Mindzai. Thanks for the release. Appreciate it given your other priorities.

[callingcard]

I was hoping the spreadsheet for calculating concontractal interest for credit cards would be available by now. I will have a go at calculating on the spreadsheet when I have listed the panalty charges and dates they were incurred for my Capital One CC cards. The interest is compounded each month. If Mindzai uploads his spreadsheet before I have finished mine I will have a look at it. In thinking about how I am going to do it, it isn't easy, if I come up with something that I think will be useful to others I'll offer it to anyone who wants it.

 

Regards G.

[isidore]

I was hoping the spreadsheet for calculating concontractal interest for credit cards would be available by now.

 

Ummm.....it is??

 

Right, the sheet is below:

 

Download v1.0

 

BE WARNED! I have not tested this as much as I would like. The interest paid on interest section is not as accurate as I would like due to the varying ways credit cards work. I will be working on this, but for now its not 100%. It should be as accurate as the existing sheets, but I dont claim it's as accurate as it could or should be. If you are not claiming interest owed on interest, then there should be no problem. The contractual interest on the charges themselves should be as accurate as the bank charges sheet.

 

Feel free to use this sheet, but do so at your own risk! If you do find any problems with it please let me know.

[callingcard]

I'm not sure, I've downloaded the spreadsheet and it looks like it is for bank account interest or have I got it all wrong?

[Seahorse]

Which one did you download? v1.9 is the bank one, v1.0 is for CC's

[maangov]

Hi Mindzai,

 

Thanks for releasing the CC spreadsheet. I loved the Bank account one, and this looks just as good.

 

I have noticed a small bug i think. Should the formula in cell O19 on sheet Charges & Interest be extended right down the column? It seems to stop the "Interest Paid on Penalties" from working?

 

Apologies if i'm wrong!

 

Regards

 

MikeA

[progenic7]

Hi all

 

is anybody having any problems with the interest paid on penalities bit ?

 

im especially struggling with the balance paid over from last month bit, but i dont get any actual totals in the end box ie the interest paid box, it just remains blank !

 

any ideas anyone ?

[maangov]

Hi all

 

is anybody having any problems with the interest paid on penalities bit ?

 

im especially struggling with the balance paid over from last month bit, but i dont get any actual totals in the end box ie the interest paid box, it just remains blank !

 

any ideas anyone ?

 

Hi progenic7,

 

See my post above. I believe the formula thats locked and hidden in the O column needs extending down the page. It's there in cell o19 but not o20 downwards.

 

Mike

[Mindzai]

Hi Mindzai,

 

Thanks for releasing the CC spreadsheet. I loved the Bank account one, and this looks just as good.

 

I have noticed a small bug i think. Should the formula in cell O19 on sheet Charges & Interest be extended right down the column? It seems to stop the "Interest Paid on Penalties" from working?

 

Apologies if i'm wrong!

 

Regards

 

MikeA

 

No you are quite right thanks, somehow I managed to undo copying that formula down before I saved

 

New version is below:

 

Download CC v1.1

[progenic7]

Mindzai,

 

Sorry to keep bugging you, but can you clarify this for me mate, on my statement RBS CC i have the following ok.

 

Balance from previous statement

Payments received

Interest summary (cash)

Interest summary (purchases)

New Balance

Spending on account plus adjustments

Minimum payment.

 

 

Im assuming i put the New balance figure and the Balance from previous statement figure in the interest boxes, its just when i do this i get a figure higher than the total interest paid altogether !

 

any thoughts mate ?

[InKogneeToh]

Hi

 

Mindzai

 

I have filled in my bank charges on your very easy spreadsheet and saved it but when I try to print the compound interest tab (only 22 cells filled in) my printer just keeps trying to print 51 pages! I have tried this from the printer icon, the file/print option and from 'preview' page but still the same. Am I being extra think and missing some simple step? Yours or anyone's help would be much appreciated.

 

Regards, Pam

[progenic7]

Inkogneeto

 

Lol

 

when you click on print instead of asking the printer to print all pages, just change it to print page 1, or pages 1-2 ect.

Otherwise it will print loads of them out

 

Johnny

[InKogneeToh]

Inkogneeto

 

Lol

 

when you click on print instead of asking the printer to print all pages, just change it to print page 1, or pages 1-2 ect.

Otherwise it will print loads of them out

 

Johnny

 

Hi

 

Yes, I did that, but it just prints the 'notes' tab page even though I have the contractual interest tab open and not the 'notes' tab.

 

Regards, Pam

[progenic7]

yeah you need to click print "current or open page" then tell printer to only print page 1 or 1-2 otherwise it will try and print the entire book of pages

 

Johnny

[InKogneeToh]

Hi

 

Thanks, I will try that tomorrow. Off to beddy byes now.

 

Regards, Pam

[JustJon]

Hi Mindzai and you other knowledgeable people you.

 

I'm using the bank spreadsheet (v1.9) but have some questions about the debit interest portion and specifically the "Account balance at interest date" column. I've posted on the other spreadsheet thread - They were very helpful, but pointed me here if I needed to know more.

 

I've noticed that any positive balance in the "Account balance at interest date" column results in no debit interest being "claimable" for that month - Even though it may of only been a positive balance for that particular day and negative for most of the month. Obviously this is not correct because even if I had only one charge, a portion of the interest in every month after would be claimable regardless of the account balance.

 

I realise that it can't ever be 100% accurate but some months I'm paying £15 in interest, having previously paid £1000 in charges, but because the balance on the day used for the calculation is positive, the sheet calculates that none of this interest is claimable. This is obviously not right and adds up to quite a lot by the end of the 6 years.

 

Now I could just pick a random balance from some point in the month (If I'm correct, -1p would give the highest possible claim amount, so it would pay to get as close to this as possible), but it just doesn't seem very accurate.

 

As I see it, there are a number of ways I could go:

 

1) Average balance in charging period (Is this even possible to work out? If so how?)

 

2) Lowest balance in charging period

 

3) Highest negative balance in charging period (Highest yield - but sort of defeats the point)

 

4) Balance on last day of charging period

 

5) Balance on day interest was taken (Opening balance)

 

6) Balance on day interest was taken (Closing balance)

 

Any advice on which would produce the most accuracy / highest yield would be appreciated. I know the instructions say to use the balance on the day the interest was taken, but this just doesn't seem right. Considering the interest being debited actually applies to the charging period 1 month previous, doesn't this mean its always one month out? Or am I missing something. Anyway, help/advice needed please.

[bobandsuzzi]

hi mindzai

i have tried to update my spresdsheet from 1.7 to 1.9, i have copied and pasted all the info into the 1.9 sheet, but it wont calculate the cumulative penalties or the interest paid on penalties

will i have to start over and re enter everything, or is there a trick to geting these two columns to activate? :confused:

 

thanks for any help

by the way, your inbox is full

 

bobandsuzzi

[progenic7]

Has anybody tried the new CC spreadsheet and are you struggling with the interest bit, especially the balance carried from next month bit ? whats that mean ?

 

any help im struggling with it

[tifo]

i've asked what spreadsheet to use for Credit Card claims, as i've used Vamps' but cannot make head or tail of the figures it outputs.

 

i need to claim :

 

charges

 

extra interest due to charges

 

ppi

 

extra interest due to ppi

 

anyone help please as this is holding my CC claims for many weeks now.

[LEECHTHEBREACH]

Tifo here it is, its easy and works !!!http://www.zen122856.zen.co.uk/CompoundSheet_CC_v1.1.xls

[sjcwhuk]

Fantastic spreadsheets! Many thanks.

 

Couple of questions...

 

You quote the following formula "P(1 + r)^n", can you translate the full formula in ye olde English i.e. P multiplied by 1 plus r then x n.

 

It's so that I can get my head round it in case the DJ asks questions as to exactly what it all means before deciding whether to award it.

 

Being an EX credit controller I used to let the accounts program zap everything rather than have to work things out myself. Sadly I'm a product of the electronic calculator world

 

Steve

[bill-k]

You quote the following formula "P(1 + r)^n", can you translate the full formula in ye olde English i.e. P multiplied by 1 plus r then x n.

 

Steve

Steve, first, for CI compounded daily, you have to calculate the daily rate r, and this is found by dividing the annual interest rate by 365.

You then add 1 to this figure to arrive at (1+r)

You then calculate the number of days elapsed since the charge was made to the date of calculation (eg today), which gives you n

You then raise the figure you obtained from (1+r) to the power of n, that is: (1+r)^n

You then multiply the Principal P by this figure to give P*(1+r)^n

 

So you do not just multiply (1+r) by n, you actually multiply it by itself n number of times !!

This is what makes compound interest so "interesting" !!

 

The result of this calculation will give you the total amount owed (including the Principal P), so to work out just the interest charged, subtract P from this final result.

 

HTH

 

PS - Corrected with thanks to Mad Nick.

[sjcwhuk]

Many thanks Bill

 

Didn't want to go"errrrrrrrrrrm" to the judge

[bill-k]

Further to my post above about compound interest calculation, I forgot whose thread I'm on. Apologies to Mindzai, but I should point out that Mindzai's spready uses what I think is termed the "true" annual interest rate as the basis for the above calculation. This is actually a lower rate than the EAR or APR, and I believe more accurately represents the banks' calculations.

 

This is the formula I use for converting the EAR/APR rate to the "true" annual rate (where EAR is the EAR rate expressed as XX.XX%):

 

365*(((1+EAR)^(1/365))-1)

 

I doubt if the Judge will have bothered to let you get to this stage of your explanation in court, and will have probably have realised that you know more about it than the defence does !! Nevertheless, for completeness...:o

 

The two formulae can be combined, to give a simpler single formula, which calculates the total payable (Principal + Interest) directly from the AER:

 

P*((1+AER)^(1/365)^n)

 

Again, subtracting P from this will give the actual compound interest charged.

 

This is Mindzai's thread, and I humbly submit this info, subject to Mindzai's (or Tim's) critique !!

 

Meanwhile, HTH,

 

Bill.