Download Excel Workbook
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 –
Table of Contents
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
Table of Contents
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.
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.
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.
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
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 Academy Online Course.
