Results 1 to 8 of 8
  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

    Monthly repayments (2K3)

    Right from the start of this question I have a complete dyslexia when it comes to accounting, so be gentle. I'll use fictitious figures for this example

    I wish to get a loan out and want to make repayments over a 3 year period, loan
    Jerry

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

    Re: Monthly repayments (2K3)

    The financial functions in Excel are correct, as far as I know, but a) there is some ambiguity as to how interest is calculated, and [img]/forums/images/smilies/cool.gif[/img] banks do not always keep strictly to the mathematical rules.

    Keep in mind that if you pay monthly, the number of periods has to be in months and the interest rate too.

    If you repay monthly at a yearly interest rate of 8%, one way of calculating the monthly interest rate is simply 8% / 12 = 0.666...%. But since interest is cumulative, that results in an effective interest of more than 8%. The effective interest can be calculated as follows: the monthly multiplication factor is 100%+8%/12 = 1.0066.... This leads to a yearly multiplication factor of 1.00666...^12 = 1.083. The increase is 0.083 or 8.3%.
    You can use the same method to calculate the monthly interest that results in an effective interest of 8% yearly: the yearly factor is 1.08, thus the monthly one is 1.08^(1/12) = 1.006434. The increase is 0.006434 or 0.6434%.
    Now it depends on which method the bank uses to determine the monthly interest rate...

    You can also use RATE Function with the figures provided by the bank to calculate their "real" interest rate.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly repayments (2K3)

    Hi Jerry. In addition to Hans' answer, does this help. Scroll down the page to the Excel bit.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    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: Monthly repayments (2K3)

    OK, take a deep breath <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I can work out the monthly increase as (8%/12)=0.666 and the increment is *1.666.

    Copying this down I can say that without repayment the amount increases to
    Jerry

  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: Monthly repayments (2K3)

    Thanks Rob

    I decided to grit my teeth and go through the PMT function ( I have a habit of over analysising like I do in Puzzles <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).

    I get irritated that I can make a calculation and it differs from the banks quote by about 50 pence per month in the banks favour, this is why I have difficulty in beliving this function is correct or if, as Hans says, they calculate it differently.
    Jerry

  6. #6
    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: Monthly repayments (2K3)

    OK , I told you I never understand accountancy, a mental block

    I calculated the monthly installments using PMT and then tried to see it as a reduction over 36 months. I want to do this so that I can put lump sums to pay i? off quicker. Why am I getting negative amounts towards the end/
    Jerry

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Monthly repayments (2K3)

    You weren't including the interest that the bank would have been charging

    See the attached....
    Attached Files Attached Files

  8. #8
    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: Monthly repayments (2K3)

    Thanks Dean

    That makes sense, I tried something similar but did not split it into a different column and hence got mucked up <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    This is great
    Jerry

Posting Permissions

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