Results 1 to 15 of 27
Thread: Interest Payments (2K3)

20080126, 16:36 #1
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Interest Payments (2K3)
I always get a tad confused with which function to use to calculate interest on a fixed rate savings account. I am aware that banks and building society's calculate slightly differently I am looking at an assumptive amount.
For this example, I have 1000 and a fixed term interest account for 1 year and it pays monthly interest, the annual interest rate is 5.5%
In cell B2 I have 1000 and in cell C2 I have placed =(B2*0.055)/12, this shows the monthly increase for the first month. In Cell B3 I have =B2+C2
and then dragged down, for some reason it shows I would have only accrued 51!!!! I could win that on the premium bonds <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Can you advise the correct way to calculate accrued interest on an investmentJerry

20080126, 16:54 #2
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
I'm the last one that should respond to this as I am "not" the financial wizz! However, I did spot this VERY impressive formula that I hope will assist you in your calculations.
See: http://en.wikipedia.org/wiki/Fixed_rate_mortgage
If the world relied on me to go this, I will run to the Loan calculator in the Excel templates. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
PS: Another interesting site: http://mathforum.org/dr.math/faq/faq.interest.htmlRegards,
Rudi

20080126, 17:09 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
Hi Rudi
Thanks but this is not about repayments for a mortgage but compound interest of an investment, the other formula appears to be what may be what I am looking for but seem to be overly verbose for a simple calculation.Jerry

20080126, 17:54 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
How about the ACCRINT() function. It calculates accured interest? (You specifically ask for a function, not a formula)?
I think it is part of the analysis toolpak...
=ACCRINT(DATE(2008,1,26),DATE(2008,2,26),DATE(2009 ,1,26),0.055,1000,1)
PS: It works out to 55.00, not much of an improvement. Maybe a loaf of bread! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>Regards,
Rudi

20080126, 19:02 #5
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
Thanks
Bizarre, that is the equivalent of just multiplying the original amount by 0.055
=(B2*0.055)+B2 , where B2 is the original amount.
So I am right with the accrual as I calculate it as 56.40Jerry

20080126, 20:51 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Interest Payments (2K3)
I calculate 56.40 as well:
=B2*(1+5.5%/12)^12B2
Steve
PS if obtained yearly instead of monthly it would be:
=B2*(5.5%)
or only 55...

20080126, 21:09 #7
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
Perfect, I even understand the reason <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Jerry

20080126, 22:03 #8
 Join Date
 Oct 2002
 Posts
 1,993
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Interest Payments (2K3)
Hi Jerry,
First I don't see anything wrong here; 5.5 %/year on 1000 will give 55 after a year, nothing more, nothing less (no miracle). <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
Then, on the other hand, when calculating interest for shorter terms, let's say a month, the correct way isn't to divide by 12. As an approximation OK, but it should give the same result as if using interest per year, when adding months.
It's called different things around the world, amount of one, compound interest; FV=PV*(1+i)^n, as I think you know. FV and PV is not interesting here, but the factor (1+i)^n is. i=interest rate, n= amount of terms, and they must match.
i= n:th root of (FV/PV)1
The approximation many people use, divide with the amount of terms: 0.055/12=0.004583 per month.
But: 1.055=(1+i)^12 ===> i=0.004472 per month (rounded).
That is, the twelfth root of 1.055 is 1.004472. So, to get the same result as with interest per year 1000*(1+0.055)^1=1055
you will have to use the result above (per month). Remember, at the end of the year the amounts should be equal. 1000*(1+0.004472)^12=1055.004 (depending decimals used)
And of course if one want to calculate per 3 months it's the fourth root of the factor (1+i). The other thing to remember (besides to match interest with length of term) is to calculate correct, if at beginning or end of term, most used is end of term. Then of course no one knows how the banks are doing, continuous compounding or whatever.
P.S.
Did I see 051000 first? Now it shows as ?0. (Also I could have used some math fonts, or pictures, or something, but I think you can follow.)

20080126, 22:24 #9
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Interest Payments (2K3)
Thank you Argus, that is really useful. I invariably use 0.055/12 to work out the monthly equivalent of 0.004583 but the explanation you have provided me is really good, especially about the root thingy <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
Jerry

20080126, 23:28 #10
 Join Date
 Oct 2002
 Posts
 1,993
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Interest Payments (2K3)
The root thingy, yes. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> If one think about it, it must add up.
But I understand that the use and calculation of periodic interest rate can vary between countries and different branches of the economy (or degree of approximation), maybe I shouldn't have said it wasn't correct. So some may very well use r=i/n. Don't ask me how or why since they then can't use FVPV*(1+i)^n. But you have this whole thing of nominal annual rate, effective annual rate etc.

20080127, 00:27 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Interest Payments (2K3)
<hr>First I don't see anything wrong here; 5.5 %/year on 1000 will give 55 after a year, nothing more, nothing less (no miracle). <hr>
This is true if you are only compounding annually.
But if you compound monthly (as the question was asked) then the value is 56.40 since every month you earn money and then the next month you earn interest on the money you earned the month before. That is why the formula is:
=B2*(1+5.5%/12)^12B2
For B2 = 1000 that is 56.4
5.5%/12 is the interest earned each month. If you compound twice a year:
=B2*(1+5.5%/2)^2B2
=55.76
If you compound daily:
=B2*(1+5.5%/365)^365B2
=56.54
Steve

20080127, 21:46 #12
 Join Date
 Oct 2002
 Posts
 1,993
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Interest Payments (2K3)
Ah, the old "answered a question never asked". Of course the interest will be added. Probably have seen the question {I answered] too many times, that I didn't read what he asked. Now Jerry got two questions answered by asking one. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Thanks Steve.

20080127, 22:09 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Interest Payments (2K3)
Yes. Yours was the "simple interest" (no compounding) answer. The question asked was about "compound interest"
Steve

20090327, 19:26 #14
 Join Date
 Apr 2001
 Location
 Guatemala City
 Posts
 515
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='Rudi' post='690960' date='26Jan2008 16:54']How about the ACCRINT() function. It calculates accured interest? (You specifically ask for a function, not a formula)?
I think it is part of the analysis toolpak...
=ACCRINT(DATE(2008,1,26),DATE(2008,2,26),DATE(2009 ,1,26),0.055,1000,1)
PS: It works out to 55.00, not much of an improvement. Maybe a loaf of bread! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>[/quote]

20090327, 19:34 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Yes?
Did you want to ask a question?