Using "PMT" Function to Calculate for Loan Payments in Excel

The Excel PMT function calculates the repayment amount for a loan or the constant periodic payment required to pay off a loan, with a constant interest rate, over a specific year or period

The syntax PMT is: =PMT(rate,nper,pv)

Where the arguments are as follows:

• rate:     The interest rate, per period
• nper:    The number of years or periods over which the loan is to be paid
• pv:        The present value of the loan or investment

Cash Flow Convention:

Note: In line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers

In this exercise, we will practice by learning how to calculate the monthly payments on loans by some individuals which are to be paid off in full after some period of years.  Interest is charged at a rate of 5% and the payment to the loans are to be made at the end of every month.

• Open to MS Excel and enter the following fictitious data into the spreadsheet:

• Save the workbook as Loan Payment in a private folder or on a Flash drive

    Optional: Download our Practice Workbook

Step 1:           Converting the Loan Amount into an Accounting format

We will now convert the “Loan Amount” values into a currency format of your preference.  In our example, we will use GHC currency format.  To do this, execute the following steps:

• Highlight the cell range B4:B13
• On the Home tab, click on Number Format dialog box launcher

The Format Cells dialog box appears

• In the Category area, click on Currency or Accounting
• In the Sample area, click on Symbol drop down list, scroll down and select GHC

Note:  You can press GH on the keyboard to quickly select GHC currency from the list

• When done, click on [OK] button

Result:

Step 2:           Calculating for the RATE

This is done by dividing the Interest by a year (i.e. 12 per month)

Note:  Cell C4 will be treated as absolute reference (i.e. $C4$4)

To do this, execute the following steps:

• In cell E4, type:       =$C$4/12
• Press the [Enter] key to complete the formula

The formula result is:          0.004167

• AutoFill cell E4 through cell E13

This means each client will be charged 0.004167 as the Rate

Step 3:          Calculating for the NO. OF PAYMENTS (Nper)

This is done by multiplying the No. of Years by 12 months.  To do this, execute the following steps:

• In cell F4, type:        =D4*12
• Press the [Enter] key to complete the formula

The formula result is:          36

AutoFill cell F4 through cell F13

Result:

Step 4:      Calculating for the MONTHLY PAYMENT (Pmt)

This is done by dividing the “Rate” by 12 month and then multiply the “No. of Years” by 12 month per the “Loan Amount (Pv)”

In this case, we will use the PMT function to calculate for the Monthly Payment.  To do this, execute the following steps:

• Click in cell G4
• On the Formulas tab, click on Financial and select PMT function

The PMT function dialog box will pop up

• Enter the following data into their respective text boxes:
• In ‘Rate’ text box, type:      $C$4/12
• In ‘Nper’ text box, type:      D4*12
• In ‘Pv’ text box, type:         B4

The PMT settings should be configured as the one shown below:

• Click on [OK] button to complete the formula

The syntax of the PMT function reads:      =PMT($C$4/12,D4*12,B4)

The formula result is:          -£224.78

Formula Explanations:

Excel calculates the interest rate by dividing it by 12 months and then multiples the number of years over which the loan is to be paid by 12 months per the loan amount

As explained earlier, the formula for the PMT function is: =PMT(rate,nper,pv)

ü  rate   =       5%/year which equals 5/12 per month

ü  nper   =       36 years × 12 months

ü  pv      =       7,500.00 (This is what we barrowed from the bank)

Note: 

Excel returns a negative payment value of -£224.78.  This is because a payment is considered as an out-going, and therefore a negative value is returned. 

We need to convert this negative value to a positive value (because this is how we normally think of money).

To avoid negative payment as an answer, we need to edit our formula by placing a minus sign (−) before B4 in the formula; so it looks –B4. To do this, execute the following steps:

• Activate cell G4
• In the formula bar, click before B4 and type a minus sign [-]

The syntax of the PMT function now reads:          =PMT($C$4/12,D5*12,-B4)

• Press the [Enter] to complete the formula
• AutoFill cell G4 through cell G13

Result:

Step 5:           Calculating for the Total Pay Back

Total Pay Back is the total amount that will be paid to the bank.  This is done by multiplying the “No. of Payments (Nper)” by “Monthly Payment (Pmt)”

• In cell H4, type:       =F4*G4
• Press the [Enter] key to complete the formula

The formula result is:          £8,092.14

• AutoFill cell H4 through cell H13
• Select the cell range G4:H13 and convert them into GHC currency format

Result:

Note:

When formatting cells, the resulting number may at times be quite large to fully display in the cell.  In this case, you may see ######## in the cells.  When this happens, expand the column width to display the values in the cells.

Final Result:

Overview:

The Total Pay Back of a loan over 10 years (i.e. GHC445,475.16) does not look so attractive. This means, the longer the duration of the loan, the more interest payment

Watch out for the next tutorial

Comments:

Kindly leave your comments, suggestions and critics on my Contact page because these will help improve the weblog and be of help to others.

Please, don’t forget to share with others.

Thank you.