Excel Contractual Interest Spreadsheet

[overflow]

Hercules

have you tried Mindsia's sheet ? Its really good and will work that out for you. This is the one ive used and believe me if i can do it anyone can . lol

[Hercules]

I had a brief look at it I will check it out again but slightly worrying if people have been using this spreadsheet for contractual interest and its wrong......

[lucid]

This has probably already been covered but difficult to search for the equation to work out monthly interest in your spreadsheet is:

=((1+F16)^(1/12))-1

F16 being the contractual interest rate in my case 18.3 now me being naive thought to work it out monthly it would be 18.3/12 which is 1.525 but your spreadsheet it is 1.41........can anyone shed any light on this before I send off my prelim......

 

Hi Hercules,

 

The monthly rate isn't found by simply dividing the annual rate by 12. I used to know a good way of explaining this but I've completely forgotten now. The spreadsheet is right though so don't worry. The reason you can't divide the annual rate by 12 is to do with compound calculations. Sorry I can't give a solid explanation - but you will probably be able to find your bank's annual and monthly rate on their website where you can check.

 

Lucid

[KiteSurfer]

try this...

 

If the annual rate was 10% and you had £100 in the bank for a year, at the end of the year you would have £110 (£100 + £10)

 

To keep it simple - if you wanted to pay interest every 6 months but keep the same annual rate you could not just divide the rate by 2 and charge 5% every 6 months.

 

If you did at the end of the year you would have £110.25 ...

 

because..

 

At 6 months you would have £105 (£100 + £5)

At 12 months you would have £110.25 (£105 + £5.25)

meaning the annual rate was actually 10.25% not 10%

 

 

This explains why the monthly interest rate would need to be slightly lower than the annual one divided by 12 (as you found) - hence the complex calculation.

 

Hope this helps...

[Hercules]

I thought about the above solution which is in essence explaining compound interest. I suppose it all depends on how the banks work out our interest because getting compound on a daily rate is going to be different to compound on a monthly rate and annually?

[chezt]

. I suppose it all depends on how the banks work out our interest

 

Which is something as a lay person you are not going to know - Basically as long as you can show the courts you have made a genuine attempt to calculate your claim with the resources you have available then that is acceptable - it is up to the banks to give a more accurate figure if they so wish

[Hercules]

Thanks for the help guys.

[chezt]

Thanks for the help guys.

good luck with your claim - I'm sure Mindzais spready will do the job for you just fine!

[Volkswagen]

Mindzai your spreadsheet is the best on the internet as far as I am concerned.

 

Please take time everyone to understand and use it correctly and it will do everything for you.

 

Also check the figures on each page as they should all correspond before the simple or compound interest is added. This is a way of making sure that all the pages are totalling their columns correctly (particularly important if you have added or removed lines on the spreadsheets).

 

Download v1.9

 

My claim against the Ulster Bank is totalling over £12,000 when compound interest is added. I am about to send my first letter and will let everyone know how I get on.

 

Volkswagen.

[gmex2005]

Hi there,

 

I'm trying to use Mindzai's spreadsheet for credit cards to correctly work out the interest paid on charges. I have a quick question which I couldn't see answered elsewhere.

 

There is no column for including the date at which interest was charged on the credit card sheet. The reason for this was explained earlier in this thread as being due to the fact that the interest was always charged at the same time as the charges themselves - which makes sense.

 

However, say for example I get charged a £20 charge in January and then another £20 charge in March, but no charges in February. During February I get charged interest, some of which is charged due to the previous months charges but I can't see how the spreadsheet takes this in to account. Can someone tell me how the spreadsheet accomodates this situation? Additionally, if I have more than one charge in any statement month, should I total them up and have only one entry for the total charges for that month on the "Charges and Interest" sheet? If I have them on separate rows and I complete the interest section for each, surely I'm counting interest twice for those charges?

 

Many thanks.

[Capitulator]

Gmex, I'm in a similar position to you and can't find a spreadsheet specific to credit cards ! I tried to do my own but it didn't seem to give the correct answers (well, it wouldn't really would it !!)

What I mean is, doing it the simple way of compounding the interest on each penalty to date and totalling them all gave a higher figure than doing a running total plus interest for all charges on a monthly basis.

 

I have a feeling this is why card companies settle rather than going to court as they know full well we have probably calculated very conservatively !

 

Tim H-S aka. Capitulator

[MgintyMIA]

Morning,

I was hoping someone could give me some advice on the following spreadsheet related question. I have 2 claims against my bank (First Direct) concerning 2 accounts. I received 2 letters from Dg concerning each claim. The first claim is the lower amount (under £1000) while the second claim is higher (over £5000). In both cases I have used Mindzai & Lucid’s spreadsheet and am claiming contractual interest. Both cases are with the court and I submitted my AQ on 1st May. I have done pretty extensive reading of important threads and tried searching forums and threads but have not yet found my answer.

Letter re claim 1:

'Upon cross-referencing your statements with your breakdown, it is clear that the overdraft you seek to claim relates to the entire overdrawn balance not purely the charges applied to your account (for example 23/12/02 interest applied £0.37, interest claimed £0.37).'

Well actually my spreadsheet states this so I’m not entirely sure what they are referring to:

23/12/2002

£0.37

-£167.70

£0.13

 

Letter re claim 2:

'We note your comments regarding your meticulous calculations but you have not provided us with the basis of your calculations. Upon cross-referencing your statements with your breakdown, it is not clear how you arrived at your figures.

For example 23/11/2001 interest charged £21.26 and you state your account was in credit at that time so interest is all attributable to penalties.

However, as that interest was actually for the period 01/10/2001 to 01/11/2001 and as such the balance outstanding on 23/11/01 is immaterial, during the period the account was overdrawn considerably more than the cumulative penalties to that date and as such your calculations would appear to be incorrect.

As a further example your earliest claim of 23/01/01 advises that £0.07 of the total interest charged of £1.47 relates to the charge of £37.50. However as in the previous example the interest actually relates to a period before the charge was applied to the account and as such the interest was charged on a previous overdrawn balance which does not include the charge.’

My spreadsheet sates this for the first example:

23/11/2001

£21.26

-£0.01

£21.26

 

And for the second:

23/01/2001

£1.47

-£1,231.97

£0.07

 

So I understand their first query here but the second is simply incorrect (as in letter 1). It would appear that they have misunderstood the spreadsheet (perhaps on purpose?).

This leads me to my questions as I would like to make sure I have this correct before replying (if indeed I do reply - see below).

In my first letter (26/01/2007), I stated:

‘If you say that they are not, then will you please demonstrate this by letting me have a full breakdown of the costs to which you have been put by as a result of my breaches, in order to reassure me that your penalties really do reflect your costs.’

So I have already asked that they explain their calculations – obviously they did not respond. My understanding is that Mindzai based his calculations as best he could without knowing how they work out interest and given that they have not explained their charges. However, for my own knowledge I would like to know how this was calculated, in particular the formula:

=IF(ISERROR(INTONPEN),0,IF(INTONPEN

Could someone explain that for me?

Also, I received (on 24/05/07) a General Form of Order of Judgement or Order dated 22/05/07 re claim 2 where it is ordered that:

‘Unless the defendant files an AQ within 3 days of service of this order, the claimant shall have permission to enter judgement, the defendant and any counter claim shall be struck out, and the defendant debarred from giving evidence about any claim for damages’

I understand this to mean that I should win if they do not file an AQ today (I know that late filing of an AQ is a tactic of theirs). Should I bother replying to either letters?

I have an idea of how to respond, mentioning the fact that as the bank would not provide me with how their charges are calculated, I still meticulously calculated what I believed was owed based on …..

Many thanks wit your continued help.

[MgintyMIA]

hmm sorry formatting didn't quite come out right.

Should be more like this:

Morning,

I was hoping someone could give me some advice on the following spreadsheet related question. I have 2 claims against my bank (First Direct) concerning 2 accounts. I received 2 letters from Dg concerning each claim. The first claim is the lower amount (under £1000) while the second claim is higher (over £5000). In both cases I have used Mindzai & Lucid’s spreadsheet and am claiming contractual interest. Both cases are with the court and I submitted my AQ on 1st May. I have done pretty extensive reading of important threads and tried searching forums and threads but have not yet found my answer.

Letter re claim 1:

'Upon cross-referencing your statements with your breakdown, it is clear that the overdraft you seek to claim relates to the entire overdrawn balance not purely the charges applied to your account (for example 23/12/02 interest applied £0.37, interest claimed £0.37).'

Well actually my spreadsheet states this so I’m not entirely sure what they are referring to:

23/12/2002 £0.37 -£167.70 £0.13

Letter re claim 2:

'We note your comments regarding your meticulous calculations but you have not provided us with the basis of your calculations. Upon cross-referencing your statements with your breakdown, it is not clear how you arrived at your figures.

For example 23/11/2001 interest charged £21.26 and you state your account was in credit at that time so interest is all attributable to penalties.

However, as that interest was actually for the period 01/10/2001 to 01/11/2001 and as such the balance outstanding on 23/11/01 is immaterial, during the period the account was overdrawn considerably more than the cumulative penalties to that date and as such your calculations would appear to be incorrect.

As a further example your earliest claim of 23/01/01 advises that £0.07 of the total interest charged of £1.47 relates to the charge of £37.50. However as in the previous example the interest actually relates to a period before the charge was applied to the account and as such the interest was charged on a previous overdrawn balance which does not include the charge.’

My spreadsheet sates this for the first example:

23/11/2001 £21.26 -£0.01 £21.26

And for the second:

23/01/2001 £1.47 -£1,231.97 £0.07

So I understand their first query here but the second is simply incorrect (as in letter 1). It would appear that they have misunderstood the spreadsheet (perhaps on purpose?).

This leads me to my questions as I would like to make sure I have this correct before replying (if indeed I do reply - see below).

In my first letter (26/01/2007), I stated:

‘If you say that they are not, then will you please demonstrate this by letting me have a full breakdown of the costs to which you have been put by as a result of my breaches, in order to reassure me that your penalties really do reflect your costs.’

So I have already asked that they explain their calculations – obviously they did not respond. My understanding is that Mindzai based his calculations as best he could without knowing how they work out interest and given that they have not explained their charges. However, for my own knowledge I would like to know how this was calculated, in particular the formula:

=IF(ISERROR(INTONPEN),0,IF(INTONPEN

Could someone explain that for me?

Also, I received (on 24/05/07) a General Form of Order of Judgement or Order dated 22/05/07 re claim 2 where it is ordered that:

‘Unless the defendant files an AQ within 3 days of service of this order, the claimant shall have permission to enter judgement, the defendant and any counter claim shall be struck out, and the defendant debarred from giving evidence about any claim for damages’

I understand this to mean that I should win if they do not file an AQ today (I know that late filing of an AQ is a tactic of theirs). Should I bother replying to either letters?

I have an idea of how to respond, mentioning the fact that as the bank would not provide me with how their charges are calculated, I still meticulously calculated what I believed was owed based on …..

Many thanks wit your continued help.

[southyorksman]

I created my own spreadsheets to calculate the interest relating to charges. It works on the simple premise that interest is charged on the running total of charges plus interest at the end of the previous month (using the credit card rate in operation on that date) then the charges made in the current month are added. If that total exceeds the actual interest charged in that month I argue that the balance represents losses I have made by being unable to use that surplus to pay of balances on other cards. I can provide anyone with a copy if they want it.

 

Has anyone considered instead of messing about arguing contractual interest we charge the banks it's annual reported pre-tax percentage profit on the balance of each years total of charges plus interest. I've calculated these figures for Lloyds TSB from 2001 and the results are huge. Simply argue "disgorgement", that the bank should have to disgorge all the profit made from its misappropriation of funds from customers. The fairest way is to apply overall profits proportionally to each years running total. This is the profit they made from my loss.

 

IAN

 

Gmex, I'm in a similar position to you and can't find a spreadsheet specific to credit cards ! I tried to do my own but it didn't seem to give the correct answers (well, it wouldn't really would it !!)

What I mean is, doing it the simple way of compounding the interest on each penalty to date and totalling them all gave a higher figure than doing a running total plus interest for all charges on a monthly basis.

 

I have a feeling this is why card companies settle rather than going to court as they know full well we have probably calculated very conservatively !

 

Tim H-S aka. Capitulator

[Mindzai]

 

Letter re claim 1:

 

'Upon cross-referencing your statements with your breakdown, it is clear that the overdraft you seek to claim relates to the entire overdrawn balance not purely the charges applied to your account (for example 23/12/02 interest applied £0.37, interest claimed £0.37).'

 

Well actually my spreadsheet states this so I’m not entirely sure what they are referring to:

23/12/2002 £0.37 -£167.70 £0.13

 

As you say that is just plain incorrect - however should the sum of your charges exceed the negative balance of your account, then the spreadsheet will (correctly) apportion 100% of the interest paid as due to charges.

 

Letter re claim 2:

 

'We note your comments regarding your meticulous calculations but you have not provided us with the basis of your calculations. Upon cross-referencing your statements with your breakdown, it is not clear how you arrived at your figures.

 

For example 23/11/2001 interest charged £21.26 and you state your account was in credit at that time so interest is all attributable to penalties.

 

However, as that interest was actually for the period 01/10/2001 to 01/11/2001 and as such the balance outstanding on 23/11/01 is immaterial, during the period the account was overdrawn considerably more than the cumulative penalties to that date and as such your calculations would appear to be incorrect.

 

As a further example your earliest claim of 23/01/01 advises that £0.07 of the total interest charged of £1.47 relates to the charge of £37.50. However as in the previous example the interest actually relates to a period before the charge was applied to the account and as such the interest was charged on a previous overdrawn balance which does not include the charge.’

 

My spreadsheet sates this for the first example:

23/11/2001 £21.26 -£0.01 £21.26

And for the second:

23/01/2001 £1.47 -£1,231.97 £0.07

 

So I understand their first query here but the second is simply incorrect (as in letter 1). It would appear that they have misunderstood the spreadsheet (perhaps on purpose?).

 

This leads me to my questions as I would like to make sure I have this correct before replying (if indeed I do reply - see below).

 

In my first letter (26/01/2007), I stated:

‘If you say that they are not, then will you please demonstrate this by letting me have a full breakdown of the costs to which you have been put by as a result of my breaches, in order to reassure me that your penalties really do reflect your costs.’

 

So I have already asked that they explain their calculations – obviously they did not respond. My understanding is that Mindzai based his calculations as best he could without knowing how they work out interest and given that they have not explained their charges.

 

 

That is correct. the way interest is calculated is different for each bank, so the interest incurred due to charges figure is an informed estimate. It should be pretty close bu its just not possible to get that figure 100% correct on a generic sheet.

 

However, for my own knowledge I would like to know how this was calculated, in particular the formula:

 

=IF(ISERROR(INTONPEN),0,IF(INTONPEN

 

Could someone explain that for me?

 

That formula uses some custom functions i wrote for the sheet. The later version of the sheet doesnt have this as I removed for Open Office compatibility. The actual formula is pretty complex. I will break it all down for you over the weekend but as I only have 20 mins till I finish work for the week I dont have time to do it now. If you want to have a look in the mean time, you will need to refer to the hidden column N on the Charges & Interest tab which is where the meat lies:

 

=IF(-'Charges & Interest'!H20*('Charges & Interest'!I20+VLOOKUP('Charges & Interest'!G20,'Charges & Interest'!$B$20:$E$771,4,TRUE)-'Charges & Interest'!I20+'Charges & Interest'!J20)/('Charges & Interest'!I20+'Charges & Interest'!J20)>'Charges & Interest'!H20,'Charges & Interest'!H20,-'Charges & Interest'!H20*('Charges & Interest'!I20+VLOOKUP('Charges & Interest'!G20,'Charges & Interest'!$B$20:$E$771,4,TRUE)-'Charges & Interest'!I20+'Charges & Interest'!J20)/('Charges & Interest'!I20+'Charges & Interest'!J20))

 

This is the part that actually estimates how much interest was paid due to charges.

 

Also, I received (on 24/05/07) a General Form of Order of Judgement or Order dated 22/05/07 re claim 2 where it is ordered that:

 

‘Unless the defendant files an AQ within 3 days of service of this order, the claimant shall have permission to enter judgement, the defendant and any counter claim shall be struck out, and the defendant debarred from giving evidence about any claim for damages’

 

 

I understand this to mean that I should win if they do not file an AQ today (I know that late filing of an AQ is a tactic of theirs). Should I bother replying to either letters?

 

I have an idea of how to respond, mentioning the fact that as the bank would not provide me with how their charges are calculated, I still meticulously calculated what I believed was owed based on …..

 

Many thanks wit your continued help.

 

To be perfectly honest I would not bother replying just yet. Get your head round how it works first (i will try to explain it well). Even then in your reply I would reiterate that your calculations, although meticulous, are those of a layperson, and should they wish to provide more accurate figures you will be happy to review them.

 

I'll update over the weekend with more info

[beetlejuice]

Mindzai,

 

Sorry to butt in on this topic but could you have a look at the CC card v1.1 Spreadie, The notes from the bank spreadie have been transposed. See Lucid post on this thread. I'm not sure which balances to put in which column. Would it not be easier to charge the interest on charges as the current interest rate applied 40/50 days(grace) after charge then compound it.

 

I have three CC claims i'm waiting to complete, but I'm stuck. I do appreciate like us all you have other ongoing commitments, an comments would be helpful, even if it's go away and wait till I've more time.....

 

Thanks

 

BJ

 

[Mindzai]

Hi beetlejuice. Over the weekend I will try to catch up and answer any questions that have gone unanswered so far.

[MgintyMIA]

Mindzai, as usual you are a gent. Thanks for posting. I was wondering where the rest of the calculcations were hidden! I'll try and work it out this weekend also (in between laying flooring - *sigh* bank holidays...)

[Magnum]

Hi All Desperate plea

 

Was planning on handing in Abbey claim to courts tomorrow major doubts now. Have used Mindzai SS but now re-reading thread noticed interest charged for every month should be included whether charged or not. Is this correct? as I have only included interest charged in following month from charge date.

 

Also SS daily rate on account with overdraft states £0.85, and account without o/d states daily rate £0.32 using 28.7% rate so I must be doing something wrong.:-?

[Fred_Funk]

So it's pretty easy to work out yourself but the spreadsheet does it for you. But I think I've spotted a problem with the sheet. How many rows do your charges take up? I've just looked at the formula for the daily rate and I think it is only taking into account a certain number of rows. If this is the case it must be where Mindzai extended the amount of rows available but forgot to amend the daily rate formula. If this is the case I'll ask him to amend it and upload a new one asap.

 

Fred I did a daily rate calcualtion on a total of £11,000 and I think the daily rate should be £8.89 - does this sound more like it?

 

Lucid

 

Mindzai, sorry to be a pain but I was concerned that using your excellent spreadsheet the amount calculated by the 'DAILY RATE (FOR CLAIM FORM)' did not coincide with the amount by which my claim was increasing every day (see post No.268 on this thread).

 

Lucid suggested that this may be because when you extended the amount of rows available you forgot to amend the daily rate formula (see above).

 

Can you, please, confirm whether, as seems entirely conceivable, this is the case? And, if it is, is there now an amended spreadsheet available which remedies this oversight?

 

Thanking you in anticipation

 

Fred_Funk

[beetlejuice]

Mindzai,

 

Your a star, thank you look forward with interest (sic) to your

next post/spreadie.

 

Hope you did n'y spend too much of your weekend on this.

 

Appreciation as ever,

 

BJ

 

[lou5357]

Hi all - sorry to butt in on this thread but it seemed most appropriate to place my question here because it's about this spreadsheet. First off, it's really easy to use and many thanks to Mindzai.

There is a section in it where you pop in the interest date/interest charged sections. I have an overdraft of 500.00, going back 6 years. (Oddly, it's on a cardcash account.) This has made it difficult to assertain what rate of interest has been applied to my account. (Checked on the bank's website but it can't tell me either.)

Secondly, all of the interest charges on my account (and they vary from £4.00 - £7.02 each month) are applied at the end of each month only. Therefore, nothing on my statements tell me exactly what type of inerest charge has been applied. On 14 occasions in 6 years I've gone outside my authorised o/d limit. I've applied contractual rates to the relevant figures on the spreadsheet but can't complete the boxes I've highlighted above because I have no idea what % of each monthly charge relates to going overdrawn, having a bounced cheque, etc.

Can anyone advise me as to what I should do please?

[Fred_Funk]

You don't need to know what percentage of each monthly charge relates to what. Just fill in the interest date and the interest charged and let the spreadsheet do the work for you.

 

As regards the specific rate of contractual interest you should be charging the bank, it's my understanding that there's a strong argument for simply using whatever your bank's advertised rate is as of today. That should, I guess, be fairly simple to ascertain.

[lou5357]

Many thanks for replying - I've been too vague here (trying to get the figures straight as I've already submitted my claim via MCOL and realised that it's wrong).

Okay; 14 charges I've done at 28.8% (unauthorised borrowing rate)

and 9 charges were within my overdraft limit, so I applied 15.9%, hence I haven't been able to apply the higher rate to all charges. (Was this wrong?)

Also, I've tried entering the other info' in respect of monthly interest charges applied to my account but because my o/d is ongoing, it just says £0.00. (I can only assume it's because the date of the interest is always at the end of the month, just after I've been paid from my salaries, etc.)

Should I be applying contractual rate of 28.8 on all my charges? (I guess it does beg the question, why have I been charged 30.00 if I'm within my o/d limit?!) ARGGGHHHH!!! I hate maths!

[Fred_Funk]

Lou, I'm reluctant to respond not because I don't want to help you - I do - but because there are plenty of people with a much better understanding of this than me and the last thing I want to do is confuse you any more.

 

Be that as it may, given no-one esle has yet replied and on the understanding I'm no expert but am responding in good faith, I will endeavour to answer your questions as best I can.

 

As it happens, I actually find it quite helpful to do so as it helps me to get the whole process entirely clear in my own mind. ;-)

 

Firstly, I don't quite understand how you've managed to manipulate the spreadsheet to include 14 charges at 28.8% and a futher nine at 15.9%. I assume you're using Mindzai's excellent spreadsheet in which case I thought your only option was to enter one interest rate - that's to say the unauthorsied borrowing rate - on the first page.

 

In any case, whether or not you were within your overdraft limit when any charges were applied is, as I understand it, irrelevant. The argument for asking for compounded contractual interest relies on the legal principles of mutuality and reciprocity.

 

That's to say, when the bank unlawfully levied charges on your account should they have caused you to exceed your OD limit they would have been more than happy to charge you interest at a rate of 28.8% - whether or not this actually occurred isn't the issue here.

 

Your argument is that since the bank was happy to charge you interest at this rate on money it, in effect, unlawfully deprived you of, you are now proposing that the bank sould pay interest at the same rate to you when it repays your money.

 

As regards your final question, I can't access the spreadsheet from this PC but, if my memory serves me correctly, I think Mindzai and othes have suggested this problem is best overcome simply by picking an alternative date - which is more representative of the month as a whole - for entering the balance of your account. If you're paid at the end of the month then I'd suggest you go for the midpoint, ie the 14th of each month.

 

Hope this is of some help to you.

 

Fred_Funk