Pinterest Pixel

Mastering PMT Function in Excel – 3 Examples

Microsoft Excel is a powerful tool that offers many functions to help users perform complex calculations and... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

Mastering PMT Function in Excel - 3 Examples | MyExcelOnline Mastering PMT Function in Excel - 3 Examples | MyExcelOnline

Microsoft Excel is a powerful tool that offers many functions to help users perform complex calculations and analyses. One such function widely used in financial modeling and planning is the PMT function. The PMT function calculates the periodic payment against an investment or a loan at a constant interest rate for a specified time. 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 the total amount that a series of future payments is worth now; also known as the principal.
  • Fv – Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
  • Type – Optional. The number 0 (zero) or 1 and indicates when payments are due.

In this article, we will cover the following three examples in detail –

Let us explore the methods one by one!

Download the Excel Workbook below to follow along and understand How to Use PMT Function in Excel – download excel workbookPMT-Function-in-Excel.xlsx

 

Simple Loan Calculation

Suppose you took a loan of $10,000 with an annual interest rate of 5% for 3 years. You can use the financial formula in Excel to calculate the monthly payment that you need to make. The PMT formula that you can use for this example –

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

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.

PMT Function in Excel

Once you run this formula in Excel, it 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.

PMT Function in Excel

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

 

Payment at the Beginning

The PMT function assumes that payments are made at the end of each period by default. However, if payments are made at the beginning of each period, you can specify this by using the optional type argument in the PMT function.

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

Suppose you have a $20,000 loan with an annual interest rate of 5% paid over 3 years, and the payments are due at the beginning of each period. You can use the PMT function as follows –

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

PMT Function in Excel

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.

 

Microsoft Excel‘s PMT function is a valuable tool for financial modeling and planning, allowing users to calculate periodic payments for loans or investments with ease. In this article, we explored three essential methods: Simple Loan Calculation, Loan with Balloon Payments, and Payment at the Beginning which empowers users to master this powerful financial tool.

Learn more about Formulas with our 101 Advanced Excel Formulas & Functions Examples

If you like this Excel tip, please share it
Mastering PMT Function in Excel - 3 Examples | MyExcelOnline Mastering PMT Function in Excel - 3 Examples | MyExcelOnline
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  How to Calculate Returns with Excel MIRR Function

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...