Mastering PMT Function in Excel – 3 Examples

Introduction to PMT Function

The PMT function calculates the amount you need to pay each payment period to fully repay a loan. The syntax of the PMT function is :

=PMT (rate, nper, pv, [fv],[type])

where,

• Rate – Required. The interest rate for the loan.
• Nper – Required. The total number of payments for the loan.
• Pv – Required. The present value or loan amount.
• Fv – Optional. The future value, or a cash balance after the last payment. Default is 0.
• Type – Optional. Payment timing. 0 = End of period (default), 1 = Beginning of period.

Top 3 Examples for PMT Function

Simple Loan Calculation

Suppose you borrow $10,000 for 3 years at 5% annual interest. You can use the PMT function to calculate the monthly payment that you need to make.

=PMT(5%/12,3*12,10000)

where,

• 5%/12 represents the monthly interest rate.
• 3*12 denotes the total number of payment periods.
• 10000 signifies the present value of the loan.

Excel will provide the monthly payment i.e. $300 of the loan.

Loan with Balloon Payment

Balloon Payment refers to a type of loan where the borrower makes smaller regular payments throughout the loan period, and a significant, larger payment—the balloon payment—is due at the end. Let us consider a scenario where you take a loan of $20,000 with a 6% interest rate over 4 years but with a balloon payment of $5,000 at the end.

The PMT function can be used to calculate the monthly payments that you need to make –

=PMT(6%/12,4*12,20000,-5000)

where,

• 6%/12 represents the monthly interest rate.
• 4*12 denotes the total number of payment periods.
• 20000 signifies the present value of the loan.
• 5000 is the balloon payment at the end.

Once you run this formula in Excel, it will provide the monthly payment i.e. $377 of the loan.

Payment at the Beginning

Some loans require payments at the beginning of each month. You can specify this by using the optional type argument in the PMT function.

• If the type is 0 or omitted, payments are at the end of the period.
• If the type is 1, payments are made at the beginning.

=PMT(5%/12,3*12,10000,,1)

where,

• 5%/12 represents the monthly interest rate.
• 3*12 denotes the total number of payment periods (3 years * 12 months).
• 10000 signifies the present value of the loan.
• FV argument is omitted.
• 1 is the type argument because payments need to be paid at the beginning of the period.

Executing this formula in Excel would give you the monthly payment i.e. $298 for the loan, taking into account payments made at the beginning of each period.

 

FAQs

Q1: What does the PMT function do in Excel?

The PMT function can be used to calculate the amount that needs to be paid each period for a loan or investment based on a constant interest rate.

Q2: Why do you divide the interest rate by 12?

You divide by 12 when making monthly payments. This is done because the rate must match the payment period.

Q3: What is the type argument in the PMT function?

The type argument can be used to indicate the payment timing.

• Type 0 if the payments are due at the end of each period.
• Type 1 if the payments are due at the beginning.

Q4: Can I use PMT for loans with a balloon payment?

Yes, you can use the PMT function to calculate payments for a loan with a balloon payment. Enter the balloon amount as the fv argument in the formula.

Q5: Why is the PMT result shown as a negative number?

Excel treats loan payments as money leaving you. So the result is displayed as a negative value.