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
syntax is:

The PMT function takes five argument of which the last two
are optional.

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.

In the **previous chapter**, I explained How To Calculate Depreciation Charges Using The 3 Most Common Methods in MS Excel. Also see the **full tutorial course content of this Microsoft Excel Tutorial Series**.

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.

You can
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%:

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
follows:

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

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
specified below:

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.

I
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
optional arguments.

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.

Get this book (Kindle format): **Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.

Click **Here** to know more about the book.

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