PMT formula

mscheske

New Member
Joined
May 28, 2002
Messages
26
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.'
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top