A New Way of Looking at Interest- 1st successful Claim - N'wide

[Glenn UK]

Tadasin

 

you ase confusing yourself, the rate has nothing to do with whether the interest is compounding or not, niether does the period of the interest.

 

Its how its applied to the original stake and then subsequently re aplied repeatedly that makes it compound interest.

 

HTH

 

glenn

[Glenn UK]

It doesn't compount the 8% (or whatever) interest. I know this because I made a spreadsheet that does compund the interest just last night, and he results are vastly increased with my sheet.

 

btw if a spreadsheet for calculating compound interest is of any use to people I can upload it later tonight.

 

Thats interesting since the values i arrive at for interest are quite large already and i am sure that i saw a post by vamppire suggesting to change from 8 to the contractual rate for compounding the interest.

 

I think theres a function in excel specifically for compounding interest but i never got round to working it out.

 

 

Glenn

[tasadin]

So for my CC, I work out the charges on the spreadsheet and apply 16% interest not the 8% currently in the spreadsheet. Then do I compound that??? or am I really confusing myself now??

[Glenn UK]

Tasadin

 

you take the charge, if you can calculate what interest you have padi add that, then add interest at the rate you want and compund it for the period in question.

 

Apparently i was mistaken the standard spreadsheet doesnt compound the interest.

 

Someone has offered to upload a sheet that does compound the interest for you.

 

Unless your in a rush id wait for the new sheet and use that.

 

HTH

 

glenn

[tasadin]

Tasadin

 

you take the charge, if you can calculate what interest you have padi add that, then add interest at the rate you want and compund it for the period in question.

 

Apparently i was mistaken the standard spreadsheet doesnt compound the interest.

 

Someone has offered to upload a sheet that does compound the interest for you.

 

Unless your in a rush id wait for the new sheet and use that.

 

HTH

 

glenn

 

Thanks Glenn most helpful!! I have done VAMPIRESS spread sheet at 16% (contractual) so I am happy with that, just confused myself about where the compound came in, ty tho u have been most helpful.

[Mindzai]

I think theres a function in excel specifically for compounding interest but i never got round to working it out.

 

There are various interest related functions provided by Microsoft's analysis toolpack, but as very few people will have this installed it's best to use a custom formula.

[Glenn UK]

There are various interest related functions provided by Microsoft's analysis toolpack, but as very few people will have this installed it's best to use a custom formula.

 

Fair point

 

I did look at it myself but dont have the time to work out how to use that specific function.

 

Im an engineer and always found accountancy a weird subject, its not proper maths at all!!!

 

Glenn

[Mindzai]

Yep you're right there, I use spreadsheets all day at work for data analysis but I'm rubbish at maths.

 

Edit: I've had a look at vampiress' spreadsheet now, and it seems it does compound interest on charges but using a percentage rather than a root so gives a lower total than the one I made. The question is which one is correct?

 

Vampiress method: charge_amount*apr*(todays_date - date_of_charge)/365

 

My method: (charge_amount*(1+(apr/365))^days_since_charge)-charge_amount

 

As i said im useless at maths so can someone who is good with numbers confirm the best way?

 

There's a few possible consequesnces to the outcome of these last few posts as far as I can tell. Firstly, if my method is correct (though it's probably not!), Vampiresses spreadsheet is underestimating the figure we should be claiming. Secondly, it seems that as Vampiress' spreadsheet is calculating compund interest, many people are unaware of this and are using these figures as a basis for them manually calculating compound interest - on sums that have already had compount interest applied. The first issue may not be an issue if my method is incorrect, but the second issue is one that should be made clearer i think, maybe by stating implicitly on the spreadsheet that it does compound interest already.

[caro]

Although I work in accounts, I am nowhere near as good on this as Vampiress, who is an accountant. I am not sure which specific spreadsheet you got this formula from, but I know that the spreadsheets on this site have been thoroughly checked before being made available so am confident to use them myself. I hope that helps.

[Glenn UK]

All i can say is that I have used the vampiress templates and believed they compunded the interest at 8%.

 

Replacing the 8% with your chosen contractual rate would therefore provide a compunded rate over the required period.

 

Its only today that in my mind this has been quesiotned. I am not sure where the issue of a 'root' comes in.

 

Take a value x, then apply an interest rate to it 10/100 for example.

 

this gives x+10%, the following interest period redo the calc gives (X+10%)+((x+10)*10%)

 

its getting late, that doesnt look at all elegant and i cant work out why.

 

But i dont see where roots come, in it may be becuase i dont know much about accounting and have missed something fundemental.

 

Anyway Im glad that Vampiress spreadhseets give a compunded value since my lba to abbey has just been submitted and it gives me a warm glow to know its not that far out.

 

Be interested to understand this issue baout roots. i could go get my acvcounitng book out, but i never read it and it was a module on my degree.

 

I did graduate by the way, lol

 

Glenn

[caro]

Haven't read the bit about roots and cannot think for the life of me what they would have to do with anything. The thing is if anyone wants to argue the accuracy of it in court you simply point out that you are not an expert but have calculated it to the best of your ability. Of course you don't want to miss out on anything by underestimating, but if you overestimate this could be misinterpreted in court so better to err on the side of caution IMO.

[Mindzai]

Yes very true points.

 

The 'proper' formula for working our compound interest seems to use the root method as explained here (better than I can!):

 

Compound Interest

 

Everywhere I look this is the given formula. When I run my charges through this method I get an increase of about £70, but most of my charges are within the last couple of years, so anyone with large charges going back 6 years could be underestimating the interest they could be charging by a reasonable amount. I'm not trying to upset things by saying Vampiress' spreadsheet is wrong (it's what i've used for my claims and i'm happy with its results), but I wouldn't like to see the banks keep hold of some cash that is rightfully ours and could be claimed.

 

Regardless of this though I think it should be made clear that the spreadsheets on the site already compound interest. Many people don't know the workings of spreadsheets well enough to check, and as you said caro, "if you overestimate this could be misinterpreted in court". I'm sure some people are calculating compound interest based on the totals given by Vampiress' sheet and if this is the case they could be overestimating their claim by a significant amount.

[caro]

I had a quick look but it is too late at night and it's been a long day at work on the spreadsheets there, so I will bow out of this discussion and let you all debate the point. The spreadsheets are there if anyone wants to use them, but there is obviously no reason that you cannot calculate the interest as you see fit. As long as you believe in its accuracy and are happy to explain in court how you reached your figures then that is fine.

 

Hope no-one takes offence at this as none is intended, but I am tired and my brain hurts.

[tasadin]

Noticed late last night that if you use the spreadhseet on here and the compound interest calculator you linked to that the figure supplied on the spreadsheet is lower than the compound calculator.

[lucid]

Yes that's because all the compound interest calculators i've seen use the root method.

 

Mindzai

[meagain]

To clarify, the reason you take a root for compound interest is because you are repeatedly multiplying, as opposed to adding.

 

For simple interest, you add a specific amount to the interest each day, namely 1/365th of the annual rate.

 

For compound interest, you include the interest each day in the calculation, so instead of adding a fixed amount, you are adding an amount which increases with each day, as it's on the interest as well.

 

charge + (charge*daily rate) = charge*(1+daily rate)

 

The next day is (charge + (charge*daily rate)) + ((charge + (charge*daily rate))*daily rate) = (charge*(1*daily rate))*(1*daily rate) = charge * (1*daily rate)^2. The full year is charge*(1*daily rate)^365, hence

 

(1+daily rate)^365 = 1+annual rate => 1+daily rate = (1+annual rate)^1/365; that is to say the 365th root.

 

I've modified the version of Vamp's templates that I've been using to reflect compound interest correctly, complete with a summary sheet showing the outstanding balances, and correctly allocating any repayments to interest before the penalties themselves ( )

[Mindzai]

If anyone in interested in a spreadsheet that uses the root method for calculating compound interest you can download it from the link in my sig at the bottom of this post.

 

I haven't tested it on anything other that Excel v2002 & 2003, but I will do if it proves useful. It's essentially used in the same way as vampiress' one.

[jellybabe]

hi,tried to download your spreadsheet, but it doesn't seem to work?

[Mindzai]

what doesn't work? the link or the spreadsheet? Can you be a bit more specific please? Don't forget it will probably only work with recent versions of Excel. Ive just tried downloading and open it onto another computer with a different version of Excel (2002) and it's working fine.

[jellybabe]

sorry, i meant the link don't seem to work for me.Just tried it again.Shows "Internet Explorer cannot display the webpage"

[Mindzai]

hmm ok youre the second person to say that so Ill upload it somewhere else now.

[Mindzai]

You can download it now from the link in my signature below, seems to be working

[golfscape]

Mindzai

 

Downloaded your spread sheet no problems, using XP pro and excel 2003.

 

Great peice of work but maybe a bit late in my case as ive just manually worked out all my compound calcs for 6 claims! took many hours! Creating an excel spread sheet for this was way over my head.

 

But it seems you and i arrive at different results so I would be interested in establishing which of the following examples is correct. I checked a number of my/your calcs and they were all different, I give one example below.

 

Using an online compound calculator (i checked several and they gave the same result) it produced total of £44.69 for combined penalty and interest on original penalty of £15.00 dated 1.9.2000 at 18.2%

 

Using your spread sheet it produces a combined value of £40.94 for same penalty, period and rate.

 

Quite prepared to accept that the calculator i used is in error, just need to identify which is correct. I understand the root concept and confirm this was included in the online calculators.

 

Cheers

[Mindzai]

Hi golfscape, thanks for checking it out for me.

 

I assume that our differeing results are due to the rounding methods used in the online calcs. The spreadsheet is very precise in that it will work to many decimal places (although that behaviour can be overridden if necessary which I think I'll do). The sum as calculated by excel actually looks like this:

 

15*(1+0.00045820883324)^2192 = 40.94

 

If I round off the daily rate to 4 decomal places, I get a close figure:

 

15*(1+0.0005)^2192 = 44.87

 

Ive also just tested it on an online calc and got something in between, 44.74

 

So I assume that's the issue. I'll update the sheet to round up to 4 decimal places. If anyone knows what sort of accuracy the online calcs (or more importantly the banks) work to please let me know.

 

Could you tell me what sort of accuaracy you were working at when you did this manually and give me an example of one of your manual calculations so I can check it against the spreadsheet please?

 

Thanks

 

EDIT: I've updated the sheet to limit the number of decimal places used in the interest conversions.

[jellybabe]

Thanks, just downloaded the spreadsheet ok.