How To Calculate Loan Payment Using The Pmt Function In Ms Excel

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:

=PMT(Rate, NPer, PV, FV, Type) then press the Enter Key

learn how to calculate loan amortization using the MS Excel's PMT function

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

Where:

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

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.

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.

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

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.

Step 1:

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

Step 2:

Next step is to convert the number of periods into months by multiplying the 25 years by 12. It gives you 300 months.

Step 3:

Finally, you input the values into the PMT function as follows:

PMT(1.75%, 300, 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 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.

Final loan amortization worksheet in excel

NOTE:

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.