PMT calculation

gidsy

Board Regular
Joined
Jan 13, 2005
Messages
142
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?

Thanx in advance.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Here is your answer.

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/
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,073
Members
452,444
Latest member
ShaImran193

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