Spreadsheet Practice

Lesson 25 of 29, Finance

Loan payments with PMT

What a loan costs per month, and in total.

Pro9 minutes, 3 exercisesPMT

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

PMT(rate, nper, pv) returns the payment per period. Match the units: for monthly payments, divide the annual rate by 12 and multiply the years by 12.

Excel treats money you receive and money you pay as opposite signs, so the payment on a positive loan comes back negative. Put a minus in front of the loan amount to get a positive payment.

Syntax

=PMT(B3/12,B4*12,-B2)Monthly payment
=B6*B4*12-B2Total interest paid

Watch out for

  • Using the annual rate with monthly periods overstates the payment roughly twelvefold.
  • A negative result isn't wrong, it's the sign convention. The grader accepts either sign.

The exercises

  1. Monthly payment. In B6, calculate the monthly payment for the loan in B2:B4.
  2. Total interest. In B7, calculate the total interest paid over the life of the loan.
  3. Rate scenarios. In B10, calculate the monthly payment at the rate in A10, using the loan amount and years above. Fill down to B13.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Monthly payment
  2. Total interest
  3. Rate scenarios
Try these functions in the Formula Lab