PMT function in Excel – 5 Minute Easy Guide
SHARE
PMT function description
PMT function in excel can be easily used to get total loan payment per period assuming equal payment and constant interest rate.
PMT function parameters
= PMT(rate,nper,pv,fv,type)
rate : This is the interest rate for a period. Since here the annual interest rate has been given, we have to divide that value by number of payment periods per year which is 12.
nper : This is the total number of payment periods which is 24 here.
pv : It is the principal or present value of the loan. Usually if this is a borrowed amount, it is entered as negative value, which gives the positive payment values.
fv : This is an optional parameter which represent the future value after the last payment period which is zero for a fully amortized loan. If you omit the fv, it is assumed to be zero.
type : This is also an optional parameter that says whether the payments are due at the end of the payment period or at the beginning of the payment period. The default value is 0 (zero) which represent the end of the period and alternatively you can set to 1 (one) which represent the beginning of the payment period. By omitting this parameter, it is assumed that the payments are due at the end of the payment period.
PMT Function Example
Use of PMT function for fully amortized loan
Let us now calculate the monthly due payment amount for a $1000 loan obtain to pay in 12 equal monthly payments. Consider the term annual percentage rate (APR) is 4%.
Insert following formula within a cell;
= PMT(4%/12,12,-1000)
Above formula gives the below output value which is the equal or constant monthly installment for the loan;
$85.15
Use of PMT function for partially amortized loan
A partially amortized loan has equal monthly payments during the loan term to pay an agreed partial amount of the loan and the borrower liable to pay the remaining loan principal amount as lump sum or balloon payment at once.
Let us now calculate the monthly due payment amount for a $1000 partially amortized loan obtain to pay in 12 equal monthly payments.It has structured to have $250 lump sum at the end of the loan term. Consider the term annual percentage rate (APR) is 4%.
Here we use the fourth parameter (fv) which is the future value of the loan at the end of payment term to enter the final lump sum payment.
Insert following formula within a cell;
= PMT(4%/12,12,-1000,250)
Above formula gives the below output value which is the equal or constant monthly installment for the loan;
$64.70
Use of PMT function for loans with payments due at the beginning of the each period
Imagine that you have $1000 loan obtain to pay in 12 equal monthly payments due at the beginning of the month. Consider the term annual percentage rate (APR) is 4%.
In this case we have to use the fifth parameter of the PMT function, “type“, which says the function whether the loan payment is due at the beginning of the period or end of the period. Beginning of the period represented by value “1”.
Consider this is a fully amortized loan, therefore fourth parameter, “fv” , should be set to zero or empty.
Insert following formula within a cell;
= PMT(4%/12,12,-1000,,1)
Above formula gives the below output value which is the equal or constant monthly installment for the loan;
$84.87
Free Download function usage example file
This example file contains above usage examples and additional section for examples with cell references.
Have a look at this excel data table tutorial also.
SHARE