The PMT function is one of the MS Excel’s built in function
used to compute the periodic amount that will be used to pay off a loan either
on daily, weekly, monthly or yearly basis. The PMT function is used in Loan
Amortization or payment analysis and helps to know the total amount you have to
pay back when you borrow a loan from any financial institution. The PMT function
=PMT(Rate, NPer, PV,
FV, Type) then press the Enter Key
The PMT function takes five argument of which the last two
Rate is the
percentage interest charged on the principal or amount you borrowed,
NPer is the
number of period or duration of the loan,
PV is the present
value, that is the principal amount you planned to borrow,
FV is the future
value and it is optional likewise the Type
This is part 1 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to calculate loan payment using the PMT function in a spreadsheet software like Microsoft Excel 2016, 2013 and other lower versions.
WHAT IS LOAN
AMORTIZATION OR PAYMENT?
Loan Amortization is the act of paying a certain amount of
money regularly in order to offset a loan over a period of time (daily, weekly,
monthly or yearly basis). The PMT function is used to calculate the exact
amount of money to be paid regularly in order to amortize or pay back a loan
including the interest and other accrued charges within that period.
also adjust the PMT function to suite your payment interval which can be daily,
weekly, monthly or even yearly.
For example, to calculate the monthly payment that will
amortize a 25 years mortgage of $500000 with an interest of 21%:
LOAN PAYMENT CALCULATION STEPS USING THE PMT FUNCTION IN MS EXCEL
These are the steps you need to take n order to calculate the monthly pay, Total pay and Bank Profit on a loan uing the PMT function before you conclude borrowing money from any financial institution.
You will first determine the monthly interest by dividing
the 21% interest rate by 12 (21 / 12) which is the number of months in a year.
It will you a monthly rate of 1.75%.
Next step is to convert the number of periods into months by
multiplying the 25 years by 12. It gives you 300 months.
Finally, you input the values into the PMT function as
500000) then press the Enter key.
It returns a value of $8798.31.
This means that you will be paying $8798.31 at the end of every month for 300
months (25 years).
USING MS EXCEL'S WORKSHEET FOR THE CALCULATION:
In Microsoft Excel worksheet, you can arrange the data in a
worksheet and then further calculate the Total
pay and Bank profit. Enter the
data as seen in the excel worksheet shown below.
Enter the following formulas in the appropriate cells as
For the Monthly pay; in cell B4, type: =PMT(B2/12, B3, B1) then press the Enter key.
For the Total pay; in cell B5, type: =B4*B3 then press the Enter key.
For Bank profit; in cell B6, type: =B5-B1 then press the Enter key.
Your worksheet should now look like the one shown below.
did not provide values for the last two arguments in the PMT function formula.
The values are not necessarily needed in this example and also they are
This is the end of part 1 of chapter 6 of this online tutorial series.
Recommended MS Excel Textbook
In the next part (part 2), I will explain What If Analysis and Excel Powerful tools – Scenario Manager, Data tables and Goal Seek in MS Excel.
Was this tutorial helpful to you?
Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.
Also click Here to subscribe for free so that you will get our latest game updates in your email.
Labels: Microsoft Excel