How do I calculate interest?

jimjam01

I would like to create a basic spreadsheet to calculate my interest but i'm not very good with excel, can anyone here please help me by providing the necessary fomulas that I would need to input to create the chart or provide a link to a suitable template?


Any help would be greatly appreciated.

Rustvaar

=SUM(X*Y/100)

Where X is a cell containing a figure, A2, D5, etc. you're looking to calculate interest on.

Where Y is your interest rate, mine is 0.08% on a current account just now so ...

=SUM(100.00*0.08/100) Would yeild £0.08, unsurpisingly.

Hope that's at least a slight helpful start to show you you might begin. - I think dividing the figure by 12 would then yeild a monthly results, although different banks I am sure will have different ways of calculating it to benefit their own end.

Someone will definitely come along in a short while and slam my idea into the ground - Haha - I keep spreadsheets of some things but my work with interest rates are not exactly expansive. I think my method might be classed as a basic APR?

[Deleted User]

What sort of account is it for, a savings account or a current account?

jimjam01

Rustvaar wrote: »

=SUM(X*Y/100)

Where X is a cell containing a figure, A2, D5, etc. you're looking to calculate interest on.

Where Y is your interest rate, mine is 0.08% on a current account just now so ...

=SUM(100.00*0.08/100) Would yeild £0.08, unsurpisingly.

Hope that's at least a slight helpful start to show you you might begin. - I think dividing the figure by 12 would then yeild a monthly results, although different banks I am sure will have different ways of calculating it to benefit their own end.

Someone will definitely come along in a short while and slam my idea into the ground - Haha - I keep spreadsheets of some things but my work with interest rates are not exactly expansive. I think my method might be classed as a basic APR?

thank you.

anewhope wrote: »

What sort of account is it for, a savings account or a current account?

Its actually an Abbey current account. will the above formula work with this type of account?

I would also like to use it to calculate my credit cards & other accounts too if possible.

Rustvaar

The formula should work jimjam01, so long as you know your interest rate.
The only downside (which makes me head throb, unfortunately) is when you try to take into account how much money you have in the account.

If on Day One you have £100 and calculate the interest at the end of the night (as I'm sure most banks do) you'll have an obscurely small number given that it's one day's worth of interest. (2.191780822 x 10 -04).

If on Day Four you've not paid anything in yet make a deposit of £1,000 you then have to calculate that three days interest have added up on the £100 and at the end of the day you'll have £1,100 interest to calculate (2.410958904 x 10 -03).

As you can imagine, calculating interest day by day is going to be, for you (and all of us) as a human, very, very difficult but it is the only way you're going to get an accurate figure unless you don't touch your account.

£100 in a month will earn you another long 'to the power of' number, £100 in a year, will earn you £0.08 - Some cases you'll find calculating interest to be utterly futile especially in low interest accounts with low sums of cash.

Calculating credit-card debts is probably an easier scenario but there could be compound interest involved (Anyone confirm this?) unfortunately I don't know enough about how credit-cards work so I can provide much help, hopefully I'm laying the groundwork for someone truly helpful though, eh?

Best of luck with it. I've a smidge of experience with Access and I'm sure OpenOffice has a similar program, I'm sure Spreadsheets in tandem with a Database setup would make this work far easier so I'll certainly see if I can come up with something that's of use

[Deleted User]

Rustvaar wrote: »

The formula should work jimjam01, so long as you know your interest rate.
The only downside (which makes me head throb, unfortunately) is when you try to take into account how much money you have in the account.

If on Day One you have £100 and calculate the interest at the end of the night (as I'm sure most banks do) you'll have an obscurely small number given that it's one day's worth of interest. (2.191780822 x 10 -04).

If on Day Four you've not paid anything in yet make a deposit of £1,000 you then have to calculate that three days interest have added up on the £100 and at the end of the day you'll have £1,100 interest to calculate (2.410958904 x 10 -03).

As you can imagine, calculating interest day by day is going to be, for you (and all of us) as a human, very, very difficult but it is the only way you're going to get an accurate figure unless you don't touch your account.

£100 in a month will earn you another long 'to the power of' number, £100 in a year, will earn you £0.08 - Some cases you'll find calculating interest to be utterly futile especially in low interest accounts with low sums of cash.

Calculating credit-card debts is probably an easier scenario but there could be compound interest involved (Anyone confirm this?) unfortunately I don't know enough about how credit-cards work so I can provide much help, hopefully I'm laying the groundwork for someone truly helpful though, eh?

Best of luck with it. I've a smidge of experience with Access and I'm sure OpenOffice has a similar program, I'm sure Spreadsheets in tandem with a Database setup would make this work far easier so I'll certainly see if I can come up with something that's of use

With the bank account you wouldd have to make a decision whether a card purchase stops acruing interest at POS when the balance is earmarked, or when the funds are actually claimed a few days later. I'm not sure how debits being removed before credits would affect the figures either, along with when direct debits are taken on a Friday night/Saturday/Sunday to be debited on the following Monday.

Credit cards would have to have to take different interest rates for cash, balance, promo etc and for how much, whether interest is charged on interest (I assume so), once again whether interest is acrued from POS and then perhaps the most confusing of all, the order in which your payments are applied.

Overall, it is going to be something that is very complicated to design and the accuracy is going to be very questionable.

Rustvaar

I suppose a weekly snapshot of an account would be a far easier method.

Say one Sunday (or any low activity day for the account) each week you check off the balance and run the calculations over that. Not perfect but it's never going to be unless you actually obtain it direct from the bank's system.

One method would be to call up the bank, if they do a 24/7 call-centre as some now do, and ask them what the Net Credit Interest for the account is. It's right there on the screen, it certainly was whenever I worked there however the staff will no doubt state the same thing - they can tell you but the figure they give you could be incorrect.

Obviously I don't know enough about credit-cards, as you've pointed out I didn't even know there were different interest rates on one card for different things. I think the best that can be done is a very basic "I have 'X', 'X' earns interest at a rate of 'Y', my overall interest for WEEK1 is 'Z'."

I think opting for simplicity over accuracy is a clear winner. There's no way we're going to hit perfect figures in the calculation of interest rates without a lot of time and effort poured into it. By taking 'snapshots' it seems a far simpler process. Not to say that it could not or cannot be done.

ray123

For the interest rate compounded: S=X x (1+r)n
S = Final sum
X = initial investment
r = the interest rate (i.e 6% is 0.06)
n = the term (1 year is 1 , 2 years is 2, etc)

i.e S= 1000 x (1+0.1)3
S= 1331

In order to calcuate it daily, you need to know the AER and then calculate the daily rate accordingly. I do not have a calculator with me!
Eg. If the AER is 5%, the calculation should be:
1.05 to the power of 1 r (r is a symbol on the calculator) 365 (this is a fraction 1/365).

willo65

I would use rustvaars Idea but put edit the formula to divide by 365 giving you a daily interest and copy the sum into 31 different cells and key the balance every day in the corresponding cells and then have a total at the bottom for the month.

jimjam01

Thanks for your help so far guys but i still need help if its not too much to ask? i'm trying to work out what my monthly interest is but i dont know where to start as i'm not the greatest at math.

I got paid £2.00 interest on a £2119.29 balance after 0.5% tax deduction. can anyone work out how much interest this is & provide me with the correct working formula for my predictive chart please?


Thank you in advance

Lansdowne

It's cost you more than £2.12 in stress, electricity to work your PC to post this thread, etc.

A current account isn't there to provide you an income, so the interest is always small and incidental. Your mention of "£2.00 interest on a £2119.29 balance" is only helpful if the balance was unchanged for the whole month - and for a current account with normal use that won't be so.

I presume you mean £0.50 tax not 0.5% tax. That would be the usual 20% of £2.50.
Assuming the £2119 was there untouched for an exact month, then £2.50 in a month would be £30 per year which is about 1½%. (note that 1% would be £21.19). But in practice as others have said, your balance may be going up and down so the interest will vary - it's not predictable really and you'll end up spending the £2.12 on an extra beer to sort you out after the meaningless maths stress.