Compound Interest calculation on savings...

Ad · 2011-02-28 09:53:16

Hi guys,

Need some help.

I have a savings account which I pay £100 into each month. Its 6% interest rate for a period on 12 months.

I'm no good at maths and am trying to set up a spreadsheet with the calculation. I know that the first £100 will earn 6% interest for the 12 months ie, £6, and the second £100 will earn 11 months at 6%, ie £5.50, (I don't know if this is right but its the answer I got when I tried to work it out), and thereafter down to 50p interest on the final £100.

I'm having problems getting it clear in my mind how the formula for compound interest would work calculating the extra 100 each month and the depreciating returns due to the decreasing months

. I would like to understand it rather than just be given a formula that works. And it needs to be explained as if to an 8 year old I'm that bad at maths, though I'm probably doing 8 year olds a disservice saying that.

Hope that makes sense and I'm not talking out my backside.

Thanks.

bobbym · 2011-02-28 12:09:46

Hi;

Have you seen these pages yet.

http://www.mathsisfun.com/money/interest.html

http://www.mathsisfun.com/money/compound-interest.html

Try to think about it like this for your algorithm whether it is for a spreadsheet or a computer program.

I will round down as my bank does. I am assuming you are saying you are getting a flat 6% per month. Which is waaaaay to high or where is your bank? I would like use them instead of mine.

start 100

month 1 = 100 * 1.06 + 100 = 206

month 2 = 206 * 1.06 + 100 = 318.36

month 3 = 318.36 * 1.06 + 100 = 437.46

This leads to the recurrence:

That says get 6 percent on what is currently in there and deposit 100.

I can solve that recurrence and get a formula for you.

Lets play with the formula:

How much do we have in the bank after 3 months? n is how many months and r is the interest rate 6% = 1.06.

That is what we have in the table.

If I have misunderstood the interest, the monthly deposits then please tell me and I will get a new formula.

byronjordan · 2011-02-28 12:54:44

bobbym,

Hmmm ..... except it sounded like the monthly interest rate was 0.5%, not 6%, while £100 was indeed being added monthly.

That is also showing compounding monthly. Compounding was not specified, but the explanation made it sound like it was not, which would be unusual, but is possible. Compounding is much more common.

The links gave a good explanation of compounding, but there is also a question as to whether the 6% is the effective annual, also just called annualized, rate, sometimes abbreviated APY (Annual Percentage Yield). If it is the APY, then the monthly rate, if it is compounded monthly, would be slightly lower than 0.5%. A monthly interest rate of 0.5% would give an apparent yield of more than 6% when compounded monthly over a year.

Hope I'm not complicating things too much. Compounding is much more usual, and using the effective annual rate isn't necesssary for understanding the basics of interest rates, but most investments and saving accounts list an effective rate, although some will list both.

bobbym · 2011-02-28 13:02:58

Hi;

That is what I think too. I am not sure whether this a real question or a contrived one. I will just adjust the recurrence down to r =1 +.06/12 if the OP does not like the way it was computed.

Hope I'm not complicating things too much.

No complications at all. Speak your mind whenever you feel like it.

Personally I am certain you are correct. It will be good if the OP can chime in with a correction, rather than just blindly copy my answers.

byronjordan · 2011-02-28 13:34:19

I stand corrected, the compound interest link gives a good explanation of annualized/effective annual/APY. I missed it when I first scanned over it. It is called APR at that link.

So many different terms for the same thing, do those behind it make it intentionally confusing?

But those links really are excellent, good job!

bobbym · 2011-02-28 13:40:33

MIF, the administrator creates those pages and has done a good job.

He also has a nice little interest calculator here:

http://www.mathsisfun.com/money/compoun … lator.html

So many different terms for the same thing, do those behind it make it intentionally confusing?

The cynic in me says yes! Let's face it some people hide knowledge. They do it by being purposely obscure. They want to make it tough on those who are trying to follow in their footsteps.

Fortunately, there are others who want to help.

Bob · 2011-02-28 20:28:33

hi Ad and others

Its 6% interest rate for a period on 12 months

So, to do monthly calculations you must use the twelth root of 1.06.

That is x 1.00487.

And check that the bank does do monthly interest because the terms of the deal may not allow this.

Ad: What spreadsheet program are you intending to use? If it's Excel I can help with the formulas.

Bob

bobbym · 2011-02-28 21:02:36

Hi Bob;

I thought you should use the 1.06 / 12 = 1.005 which gives you an effective rate of 6.17%

Bob · 2011-02-28 23:08:47

hi bobbym

My bank works out interest like this:

Take the yearly rate, convert it to a daily rate, work out the interest for each day the money is deposited and add on before calculating for the next day.

When I had a mortgage the interest was calculated similarly but by month = twelth root of the annual rate.

There was UK government legislation a while back on this because different money lenders were using different ways to declare their rates and it was difficult to compare. Also some people were getting saddled with huge debts because they hadn't realised just how much the interest was going to be. Now all rates have to be given with an APR (= annual percentage rate)

I've just had a look at Barclaycard for example.

They quote a monthly rate of 1.313% and an APR of 16.9% for purchases
and monthly 2.075% equivalent to 27.9% for cash withdrawals.

In both cases monthly multiplier raised to the power 12 = annual multiplier.

What we need is for AD to clarify. I think the 6% rate he states is an APR but I made be wrong.

Bob

Last edited by Bob (2011-02-28 23:11:11)

bobbym · 2011-02-28 23:19:22

Thanks Bob, I had not heard of that at all. I think that the OP just went to the bank collected the money and went to Bimini.

Bob · 2011-02-28 23:30:36

I think you might need to save more than £100 per month to go to Bimini.

Have you seen the exchange rates? We're all broke and destitute over here.

Bob

Ad · 2011-03-01 06:27:35

The question was honest not contrived and I was at work and unable to post. Not to mention trying to understand what you're all saying.

I have read the links before I posted and tried to use the calculator (which makes no sense to me, I'd like to say it doesn't work but I'm not confident enough to know).

It seems I won't understand this calculation, posting the formula is no use to me as I don't understand them. You have my apologies I'm dyslexic and find maths very hard to understand until its clear in my mind, sometimes needing an alternate methods of doing things (like the table way of doing long multiplication).

I'll try and explain again. I put £100 into the account a month because thats the max per month allowed for this account. Overall £1200 is the max the account can hold. Its 6% interest and I believe the interest is calculated daily but thought I could do it monthly since I pay monthly. The account is this one: http://www.halifax.co.uk/savings/accounts/childrens-savings/childrens-regular-saver/

I wanted to be able to calculate the interest in a spread sheet and have found the formula but wanted to understand the maths behind it, thats all.

I'm starting to think its too difficult.

Thanks.

Ad · 2011-03-01 06:37:04

From the website about the interest calculation if this helps:

"A worked example below on interest calculation:
The interest on the Regular Saver is calculated on the balance of the account each month. e.g. If you pay £100 in a month, the balance in the account for the first month will be £100, second month £200 etc. and therefore the amount of interest paid will increase over the year in line with the balance of the account."

Bob · 2011-03-01 07:24:10

Small text =C5*$E$3

Bob

Last edited by Bob (2011-03-01 08:45:52)

Ad · 2011-03-01 08:59:38

Thanks Bob,

Thats where my calculations were going wrong. I thought I could do this: 1.06 / 12 = 0.0883333333333333.

Some basic questions. Why is it the 12th root used instead of the above? And how do I calculate that? Sorry just trying to understand it all.

Bob · 2011-03-01 10:30:12

And how do I calculate that?

Last edited by Bob (2011-03-01 10:39:13)

Ad · 2011-03-01 11:13:31

Hi Bob,

What with getting the kids dinner ect I'm only half way through it, I thought I'd ask that quick question. I'm off to bed for the night, early shift tomorrow. I'll do the rest tomorrow evening (which I think is your morning).

Thanks.

Bob · 2011-03-01 22:11:25

Ad · 2011-03-17 03:16:21

Ok sorry for the slow reply my computer broke. No need for big text, this forum is coloured very well for me. Anyway I'm still having trouble with this one. I'm using OpenOffice Calc as my spread sheet.

C6 doesn't equate to: 201.462... it equates to: 200.4867551

C3 shows 6
D1 is =C3/100+1 and shows 1.06
E3 is =D1^(1/12) and shows 1.004867551
C5 shows 100
D5 is =C5*$E$3 and shows 100.4867551
C6 is =D5+100 and shows 200.4867551

As I understand it 0.4867551 is the monthly interest rate but if you multiply that by 12 you get: 5.84, which isn't 6% interest.

Thanks for your help.

Ad.

Ad · 2011-03-17 03:18:07

Ok sorry for the slow reply my computer broke. No need for big text, this forum is coloured very well for me. Anyway I'm still having trouble with this one. I'm using OpenOffice Calc as my spread sheet.

C6 doesn't equate to: 201.462... it equates to: 200.4867551

C3 shows 6
D1 is = C3/100+1 and shows 1.06
E3 is = D1^(1/12) and shows 1.004867551
C5 shows 100
D5 is = C5*$E$3 and shows 100.4867551
C6 is = D5+100 and shows 200.4867551

As I understand it 0.4867551 is the monthly interest rate but if you multiply that by 12 you get: 5.84, which isn't 6% interest.

Thanks for your help.

Ad.

Edited out the smilies

Ad · 2011-03-17 03:27:50

bob bundy wrote:

...

Sorry makes no sense to me, in 2 steps would you just multiply 7 by 9?

Ad · 2011-03-17 03:54:17

Ok got it done, mis read your instructions. Answer for the 12th month is $1238.65?? What a rip off! £38 interest is rubbish!

I still done understand whats going on with it. Its not clear in my mind at all. Is that figure compounded interest or simple?

Why 12th root and a 12th?

Bob · 2011-03-17 04:57:38

hi Ad,

£1238.65 is exactly what I got. It's not great but, then again, interest rates are pretty low at the moment.

It is compound interest.

Why did I work it out like that? You don't happen to play the guitar do you? It makes a lot more sense if you work along the fret board.

Middle C is 262 cycles per second. (There is some debate about this definition. I'm guided by Wiki.)

The C below it, is 131 cycles. That's exactly half the frequency and that's how octaves work for all notes.

Middle E is 330. The E below it is 165.

There are 12 notes between one C and the next; specifically they are

C, C#, D, D#, E, F, F#, G, G#, A. A#, B, and then C again.

There is an exact multiplier that takes you from one note to the next note up.

It is M = 1.0594630943593...

So Start with the lower C 131, times by M and you get 139. (I've rounded off)

Then times that by M and you get 147. Then 156, 165, 175, 185, 196, 208, 220, 233, 247, 262 and we're back to middle C again.

To make that work you need M x M x M x M x M x M x M x M x M x M x M x M = 2

So to calculate M, I did 2^(1/12).

It has to be a multiplier or the frequencies don't work out right.

Notice the gaps between the frequencies are not constant ... but the ratio between two notes is always M.

That's why the frets on a guitar aren't the same distance apart. At the low note end the gaps have to be further apart to make the right notes. But the same fret distance works for all the strings. You tune each string to its base note and then all the fretted notes come out harmoniously.

Now back to compound interest.

If you wanted a yearly interest rate of 2%, but you wanted to calculate one month at a time you'd need to multiply by M for each month.

Try this.

Enter 28 on a calculator and times by M. Then again and again. After 12 repetitions you should have 56.

Going up by one twelth each time just doesn't do it because that would give a steady rate of increase and we want a rising rate.

Bob

Ad · 2011-03-17 06:13:14

Ok I'm admitting defeat on this one, what does M mean??? My calculator doesn't have M. The only thing I've worked out is that compound interest is something to do with playing a guitar ( I was wondering why the guitar kept popping up on google searches).

I guess its a case of "its calculated by the 12th root because it is" theres no point in trying to understand it.

I don't need to understand why the TV works to watch it. I'll not bother with the spread sheet though as I can't work it out for myself, whats the point I can't repeat the process.

I don't understand why you need to add 1 to 0.06, or why it needs to be 1.06. I don't understand why you use the 12th root instead of a 12th share. And because of that none of the calculations make sense.

Basically 1.06 ^ ( 1 / 12 ) means nothing. I don't understand how 1.06^(12/12)=1.06.

Sorry for wasting your time.

Bob · 2011-03-17 06:37:29

hi Ad,

I don't feel you have wasted my time. I enjoy trying to help people with their maths.

For the record M = 1.0594630943593...

I picked the guitar story because there are 12 notes between Cs, just as there are 12 months in a year.

But I'll stop at that for the moment as it seems to be 'doing your head in' as we say here in Essex.

Don't be put off posting again when you feel the need.

Bob

Math Is Fun Forum

#1 2011-02-28 09:53:16

Compound Interest calculation on savings...

#2 2011-02-28 12:09:46

Re: Compound Interest calculation on savings...

#3 2011-02-28 12:54:44

Re: Compound Interest calculation on savings...

#4 2011-02-28 13:02:58

Re: Compound Interest calculation on savings...

#5 2011-02-28 13:34:19

Re: Compound Interest calculation on savings...

#6 2011-02-28 13:40:33

Re: Compound Interest calculation on savings...

#7 2011-02-28 20:28:33

Re: Compound Interest calculation on savings...

#8 2011-02-28 21:02:36

Re: Compound Interest calculation on savings...

#9 2011-02-28 23:08:47

Re: Compound Interest calculation on savings...

#10 2011-02-28 23:19:22

Re: Compound Interest calculation on savings...

#11 2011-02-28 23:30:36

Re: Compound Interest calculation on savings...

#12 2011-03-01 06:27:35

Re: Compound Interest calculation on savings...

#13 2011-03-01 06:37:04

Re: Compound Interest calculation on savings...

#14 2011-03-01 07:24:10

Re: Compound Interest calculation on savings...

#15 2011-03-01 08:59:38

Re: Compound Interest calculation on savings...

#16 2011-03-01 10:30:12

Re: Compound Interest calculation on savings...

#17 2011-03-01 11:13:31

Re: Compound Interest calculation on savings...

#18 2011-03-01 22:11:25

Re: Compound Interest calculation on savings...

#19 2011-03-17 03:16:21

Re: Compound Interest calculation on savings...

#20 2011-03-17 03:18:07

Re: Compound Interest calculation on savings...

#21 2011-03-17 03:27:50

Re: Compound Interest calculation on savings...

#22 2011-03-17 03:54:17

Re: Compound Interest calculation on savings...

#23 2011-03-17 04:57:38

Re: Compound Interest calculation on savings...

#24 2011-03-17 06:13:14

Re: Compound Interest calculation on savings...

#25 2011-03-17 06:37:29

Re: Compound Interest calculation on savings...

Board footer