PMT Function

In this article we will discuss about PMT function, one of the financial functions available in Microsoft Excel which calculates the payment for a loan based on constant payments and a constant interest rate. We use PMT function in Excel as formula to figure out a monthly loan payment.

Last year I have to take a loan from Bank for short period and I was trying to calculate the amount in Excel that I must pay per month for that borrowed amount.

Surprisingly, Microsoft Excel provides a very easy function, 'PMT function' which is very easy to calculate the amount payable for a loan.

Let us discuss about PMT function but before that let us first write down the information we have.

Information with us for a loan

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 5.50%)

3.   Periods, the number of months (suppose 48 months)

4.   Compounding period per year (suppose every month i.e., 12)

Note : Out of these four available data or information, the first three are easily understandable but the fourth information needs to be clarified to some persons. Honestly speaking it is little confusing to a newbie.

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the PMT Function.

Syntax

The syntax for the PMT function in Microsoft Excel is very simple,

= PMT(interest_rate, number_payments, PV, [FV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

How to use PMT Function in Excel ?

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =PMT(C5/C7,C6,-C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify PMT function.

PMT Function

I hope you have enjoyed reading this article ...

Keep reading & Sharing....
Stay blessed ...