Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Platinum Lounger
    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 investment
    Jerry

  2. #2
    Gold Lounger
    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 interest-ing site: http://mathforum.org/dr.math/faq/faq.interest.html
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    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

  4. #4
    Gold Lounger
    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

  5. #5
    Platinum Lounger
    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.40
    Jerry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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)^12-B2

    Steve

    PS if obtained yearly instead of monthly it would be:
    =B2*(5.5%)
    or only 55...

  7. #7
    Platinum Lounger
    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

  8. #8
    Silver Lounger
    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.)

  9. #9
    Platinum Lounger
    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

  10. #10
    Silver Lounger
    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 FV-PV*(1+i)^n. But you have this whole thing of nominal annual rate, effective annual rate etc.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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)^12-B2
    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)^2-B2
    =55.76

    If you compound daily:
    =B2*(1+5.5%/365)^365-B2
    =56.54

    Steve

  12. #12
    Silver Lounger
    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.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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

  14. #14
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Rudi' post='690960' date='26-Jan-2008 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]

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes?

    Did you want to ask a question?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •