Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: PMT formula

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone know the mathematical formula behind the PMT formula? I want to earn 20% and I want to know what interest rate to charge. ie. .2=PMT(1000,36,x/12) -> Solve for x.

    Thanks for any help.

  2. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-07-23 08:09, mscheske wrote:
    Does anyone know the mathematical formula behind the PMT formula? I want to earn 20% and I want to know what interest rate to charge. ie. .2=PMT(1000,36,x/12) -> Solve for x.

    Thanks for any help.
    I don't understand your question. If you want to earn 20%, charge 20% interest. If you solve for x in the equation you showed, you will get a monthly rate that gives you a monthly payment of 0.2 (in the currency of interest).

    If you want an introduction to a generalized solution for problems where the payment amount is invariant, check http://www.aemsinc.com/fincalc/index.html. Scroll down and click the button 'How we calculate the numbers.'

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sorry for not clarifying. I work at a bank and as people pay monthly loan payments, they don't pay as much interest. So if I charge 20% annually, then I will actually receive less than 20% profit. ie. for the first monthly payment, they pay 20/12 % on the entire balance, then next month pay 20/12 % on the remaining balance and so on.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could use the RATE formula to get a specific Interest value, such as this

    =RATE(36,37.16,-1000)*12

    which would get you the 20% in your example.

    Also, you could use PMT using Goal Seek to find out what you're trying to do.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Jun 2002
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just an FYI to your original question the math is

    payment per period = P* i*(1+i)^n/((1+i)^n-1)

    where i is the interest per period (20%/12)
    n is the number of periods
    P is the original principal

    Using Juans method would be easiest.

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bingo!
    Thanks for the help guys!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com