# PMT calculation

#### gidsy

##### Board Regular
Hi,

I am trying to find out the formula behind this function. I need to replicate it in a document and can't just write PMT(rate,nper,pv,fv,type) and explain what each item is...I need to explain how this function works..

Is there anything in Excel help or other sources that will give me this?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### RalphA

##### Well-known Member
Edited:
See my post below

#### RalphA

##### Well-known Member

Note that he rate (R) is expressed in % per period, as in 5%/12 for monthly payments, or in per unit, as in 5/1200, and the number of periods is expressed in months, as in 360 for 30 years.

As an example to check yourself with:
PV = 100000
R = 5%
n = 360

Payment = (100000*5%/12)/(1-(1+5%/12)^(-360) = 536.82, or
Payment = (100000*5/1200)/(1-(1+5/1200)^(-360) = 536.82

Date: 03/23/2005 at 23:18:24
From: Doctor Wilko
Subject: Re: Amortization

Hi Gameel,

Thanks for writing to Dr. Math!

The monthly payment can be found by using the following formula:

P = (Pv*R) / [1 - (1 + R)^(-n)]

where

Pv = Present Value (beginning value or amount of loan)
APR = Annual Percentage Rate (one year time period)
R = Periodic Interest Rate = APR/ # of interest periods per year
P = Monthly Payment
n = # of interest periods for overall time period (i.e., interest
periods per year * number of years)

If you're familiar with Excel, you can also use the PMT function in to
get the monthly payment.

Does this help? Please write back if you have further questions.

- Doctor Wilko, The Math Forum
http://mathforum.org/dr.math/

Replies
1
Views
203
Replies
2
Views
192
Replies
0
Views
299
Replies
0
Views
572
Replies
1
Views
267

1,195,940
Messages
6,012,426
Members
441,699
Latest member

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

### Which adblocker are you using?

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

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