Results 1 to 6 of 6

PMT formula

This is a discussion on PMT formula within the Excel Questions forums, part of the Question Forums category; Does anyone know the mathematical formula behind the PMT formula? I want to earn 20% and I want to know ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    26

    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
    10,924

    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

    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,950

    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

    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

    Default

    Bingo!
    Thanks for the help guys!

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