Can i run my excel calculations by you?

moneylearner

Hi everyone.

Been lurking on this site for about a month and have learned a lot so far. I thought I’ll make my first post

I was wondering if someone can check my calculations for me. I want to make sure I have got it all correct since I hope he give this spreadsheet to other people.

For example if I have a list of deposits per month:

1000 per month

I then use a formula to work out 5% as monthly interest amount assuming interest is applied monthly:

=(1+5%)^(1/12)-1 which give me 0.407% instead of
5%/12 which is 0.417%

I multiply 1000*0.407% to give me 1004.07

Then I add £1004.07 (month 1) to next months (month 2) £1000 and then multiple it by 0.407% to give me the interest £8.16, then total up for month 2012.24. I repeat this for x years

Is this correct? I would like to use this in a classroom situation so that people can learn about their finances

Many thanks

Richard

MarkyMarkD

Yep, those figures are a great explanation of how compound interest works.

moneylearner

Thats great thank you.

Just to finish do I then take the monthly interest earned and take 20% off for tax. so on say on £4.07 interest * 20% to take £0.81p off £4.07


Regards

Richard

PS. If any of the experts can think of anything else financial that I can teach in my excel class then i would be most willing to consider it

Robert_Sterling_3

The bank/building society will give you a statement of how much interest has been earned and how much tax has been deducted at 20%.
Whislt for some purposes they may calculate the interest daily they may only pay it once per annum.
Your method of doing it monthly is a good approximation to what is going on but banks have to work to a high degree of accuracy so they probably extract the 365th root of the annual multiplier of 1.045
to get the daily multiplier then subtract one from it to get the daily percentage rate. This is all tied up with months not having the same number of days.
For most purposes what you are doing is OK.

moneylearner

so i take it as =(1+5%)^(1/365)-1 = 0.000133680617113496

cloud_dog

PS.  If any of the experts can think of anything else financial that I can teach in my excel class then i would be most willing to consider it

One of the things I found quite interesting (shocking really) when I got into the financial stuff was the effect of inflation on money over a number of years in real terms.

People think interms of todays value, i.e. someone is starting a pension and the figures come out that for a monthly premium, say £300, for 30 years they might end up with a pension of £25k pa most people would think "yeah, ok that looks good" but in real terms (in 30 years time because of inflation) it might only be equivilent to a £15k pension.

I think this concept is quite important . I know the financial products have tables that show the effect of different levels of inflation over different periods - but, don't ask me to calculate it

cloud_dog
(sorry to post and run)

mary

=(1+5%)^(1/12)-1 which give me 0.407% instead of
5%/12 which is 0.417%

For those of us who weren't paying attention in class, please can you explain the above in layman's terms?

moneylearner

Hi.

I’ll try and explain as much as I can in layman’s terms the above formula.

Well the reason the formula is this way is because of the inaccuracy of taking a yearly interest rate and dividing it by twelve to find the monthly interest rate.  For example

If egg saving are giving 5.5% interest a year it might seem reasonable to take 5.5% and divide it into 12 monthly parts to work out the monthly interest rate by:

5.5%/12 = 0.4.533333% a month interest.

Then you could take say 500 invested at the start of the month and multiply it by the monthly interest rate:

500 * 0.453333 = £2.29p for that month giving you £502.29 in the bank.

Now apparently this is not fully accurate.  At this point im not 100% confident on my explanation here but here goes.

The formula example in the above post is:

=(1+5%)^(1/12)-1

Now the gist of this formula is to eventually take into consideration compounding of interest.  The concept of interest on top of interest.  For example:

Month 1 I save £1000 and earn 1 months interest at (5% a year) 0.407% (more decimal places but I have cut it off).

=1000* 0.407% =  £4.07  so £1004.07

Next month I would have 1004.07 in my account.  Interest on that would be:

1004.07*0.407% = £1007.16.  So I have earned interest on my last month’s interest (compounded)

So how on earth does this relate to that formula =(1+5%)^(1/12)-1.  

The formula =(1+5%)^(1/12)-1 broken down takes

(1+5%) 5% and add 1 to it to make it 1.05 in decimal.  This is used to multiply your savings amount by.  For example:

1000* 1.05 = £50 per year.  Trouble is I want to work out the monthly interest.  So to do this:

(1/12) in decimal a month would be 0.083333.  Since in decimal (1-10) doesn’t quite match up to the number of months in a year (1-12) taking 1(as in 1 year) and dividing it by 12 (as in months), gives use 0.08333 as a fraction.

So far we have 1.05 to indicate the amount for interest purposes.  Why it is 1.05 is that we want the growth not just the interest.  So 1000*1.05 is £1050.  If we only took 0.05 then the answer would be £50.

We don’t want £50 (the interest) we want the interest and the amount invested (£1000) to give us £1050

Where the ^ (power) comes in is:

=(1+5%)^(1/12)-1

1.05 (interest growth rate) ^ (power of) 0.08333 (month as a decimal fraction) is 1.00407  this answer is your more accurate monthly interest multiplier as in:

1000*1.00407 = £1004.07 in bank for the month.

The last bit -1 is to make the final answer im looking for 0.00407 (otherwise it is 1.004074) which I can then format in Excel as percent giving me 0.407%  Then with this answer I can take my 1000 and multiply by 0.407% to tell me I have earned £4.07 interest.

I think that is why if you just take 5% and divide it by 12 this would evenly divide you 5% into 12 parts as in 5%/12 = 0.416 (again I’m cutting of decimal places) which is different to 0.407.

The reason for this is that I have read that you need to work out the 12th root of you yearly interest rate (which is expressed as a growth of 1.05).  This is where I struggle and would ask someone to pick up this point of why we need to use the 12th root as opposed to just simple dividing it

Please anyone correct me if im wrong on any points above and I will amend them

CurryKing_2

Does that mean if i wanted to calculate the cost of borrowing ie. £1000 at 5% APR it would be the same equation? 8)

Neil

MarkyMarkD

Does that mean if i wanted to calculate the cost of borrowing ie. £1000 at 5% APR it would be the same equation?  8)

Neil

Yes, it would exactly, if it was a loan of a fixed amount of £1,000, you never made any capital repayments, and you paid the interest on the compounded balance at the end of the year. The interest would total £50 over the year, but would accrue in an increasing way e.g. £4.07, £4.09, etc. as previously illustrated.

Robert_Sterling_3

I would agree with the above if Gadget Freak had just said the rate was 5%.  However he actually said 5% APR which is something quite different.