WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

The PMT Function in Excel

December 31, 2021
3.8K Views

Understanding Excel's PMT function is crucial for various financial calculations, including mortgages, loans, and investments. However, deciphering its arguments and navigating Excel's interface can be challenging.

In this article, we'll provide you with a comprehensive guide to help you master the PMT function.

#1. What Is the PMT Function in Excel

the PMT Function in Excel


The PMT function in Excel stands for "Payment." It's a powerful tool used for calculating periodic loan payments, making it particularly useful for tasks involving mortgages, loans, and investments. This function assists in determining the regular payment amounts needed to repay a loan or to reach a financial goal within a specified time frame.

The syntax of the PMT function typically involves the following elements:

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

PMT Function


Arguments:

  • rate: The interest rate per period. For example, if the annual interest rate is 6%, the monthly interest rate would be 6%/12 = 0.5%.

  • nper: The total number of payment periods. For example, if the loan term is 30 years, the total number of monthly payment periods would be 30*12 = 360.

  • pv: The present value of the loan, or the amount of money that the borrower receives.

  • [fv]: (Optional) The future value of the loan, or the amount of money that the borrower wants to have at the end of the loan term. If fv is omitted, it is assumed to be 0.

  • [type]: (Optional) A number that indicates when payments are due. If type is omitted, it is assumed to be 0, which means that payments are due at the end of the period. If type is 1, payments are due at the beginning of the period.

Example:

The following example shows how to use the PMT function to calculate the monthly mortgage payment for a $300,000 loan with a 5% interest rate and a 30-year term:

=PMT(0.05/12, 30*12, 300000)

This formula will return the value 1,304.32, which is the monthly mortgage payment.

#2 How to Use PMT Function in Excel (with Examples)

Use PMT Function


From weekly to semi-annual payments, and even mortgage calculations, we'll walk you through each scenario step-by-step. By the end of this section, you'll have a clear understanding of how to apply the PMT function in various financial situations.

Calculate payments to pay off a debt.

Weekly payment:

=PMT(8%/52, 3*52, 5000)

This formula will return the value 36.08, which is the weekly payment for a $5,000 loan with an 8% interest rate and a 3-year term.

Weekly payment


Monthly payment:

=PMT(8%/12, 3*12, 5000)

This formula will return the value 156.68 which is the monthly payment for a $5,000 loan with an 8% interest rate and a 3-year term.

Monthly payment


Quarterly payment:

=PMT(8%/4, 3*4, 5000)

This formula will return the value 472.8, which is the quarterly payment for a $5,000 loan with an 8% interest rate and a 3-year term.

Quarterly payment


Semi-annual payment:

=PMT(8%/2, 3*2, 5000)

This formula will return the value 953.81, which is the semi-annual payment for a $5,000 loan with an 8% interest rate and a 3-year term.

Semi-annual payment

Calculate the monthly payment for a mortgage loan.

Example:

Assuming a $200,000 mortgage, an annual interest rate of 4%, and a 30-year term, the formula would be =PMT(4%/12,30*12,200000).

monthly payment for a mortgage loan

Calculate how much to save monthly to reach a savings goal.

Example:

If you want to save $50,000 in 5 years, with an annual interest rate of 3%, the formula would be =PMT(3%/12,5*12,0,-50000).

save monthly to reach a savings goal

#3 How to Create a PMT Calculator in Excel

Creating a PMT calculator in Excel can streamline your financial planning and decision-making process. In this section, we'll guide you through the steps to craft your own personalized PMT calculator.

Download calculator templates online

WPS templates website


There are many PMT calculator templates available online that you can download and use. Here are a few recommendations:

These templates are all easy to use and provide a variety of features, such as the ability to calculate payments for different types of loans, including mortgages, car loans, and student loans.

Make a simple loan payment calculator step-by-step.

If you want to create your own PMT calculator in Excel, you can follow these steps.

Step 1: Open Excel

Open Excel and create a new workbook.

Open Excel


Step 2: Set Up Your Worksheet

In your new workbook, create the following columns and labels:

A

B

C

D

E

Loan Amount

Interest Rate (%)

Loan Term (months)

Monthly Payment

Monthly Interest Rate

Step 3: Enter Initial Values

  • In cell B1, enter "Loan Amount".

  • In cell C1, enter "Interest Rate (%)".

  • In cell D1, enter "Loan Term (months)".

  • In cell E1, enter "Monthly Payment".

Step 4: Input Values

  • In cell A2, input the loan amount (e.g., 10000).

  • In cell B2, input the interest rate (e.g., 5).

  • In cell C2, input the loan term in months (e.g., 24).

Step 5: Calculate Monthly Interest Rate

In cell E2, input the formula =B2/12 to calculate the monthly interest rate. This formula divides the annual interest rate by 12 (the number of months in a year).

Step 6: Calculate Monthly Payment

In cell D2, input the formula =PMT(E2, C2, -A2) to calculate the monthly payment. This formula uses the PMT function to calculate the monthly payment based on the provided interest rate, loan term, and loan amount.

Step 7: Format the Cells

You may want to format the cells to display currency or percentages. Select the cells, right-click, and choose the appropriate format from the options.

Step 8: Test the Calculator

Now, you can change the values in cells A2, B2, and C2 to see how it affects the monthly payment.

Here's an example of a loan payment calculator table

example of a loan payment calculator table

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

Best Alternative to Microsoft Excel - WPS Spreadsheet 

WPS Spreadsheet icon


If you're seeking a powerful alternative to Microsoft Excel, look no further than WPS Spreadsheet, a key component of the comprehensive WPS Office suite. It offers a wide range of features, including:

  • A user-friendly interface that is similar to Excel

  • The ability to open and edit Excel .xls or .xlsx files

  • A variety of spreadsheet functions and formulas

WPS Spreadsheet also offers a number of features that are not available in Excel for free, including:

  • Co-editing within Word documents, spreadsheets, and presentations

  • The ability to share documents with a group and allow them to edit them with your permission

  • WPS AI, which enhances WPS Office by providing advanced features such as document analysis, efficient formatting, and intelligent content recommendations.

WPS Spreadsheet is a powerful and versatile spreadsheet application that is a great alternative to Microsoft Excel. It offers a wide range of features, including co-editing, document sharing.

FAQs

Are there other functions related to financial calculations in Excel?

Absolutely, Excel offers a range of financial functions that cater to various aspects of monetary calculations. Some notable ones include PV (Present Value), FV (Future Value), and RATE (Interest Rate). These functions can be combined with PMT for more comprehensive financial analysis.

How does the PMT function handle additional costs like taxes or insurance?

The PMT function specifically calculates payments based on provided arguments such as principal, rate, and period. However, it doesn't account for additional costs like taxes or insurance. These would need to be factored in separately, as they are not within the scope of the PMT function.

How accurate are the results from the PMT function?

The PMT function provides highly accurate results based on the inputted arguments. It's crucial, however, to consider any other factors that could affect the actual payment amount, such as taxes, insurance, and associated fees. To ensure precision, always tailor the PMT function and its arguments to your specific financial scenario.

Summary

In this comprehensive guide, we delve into Excel's PMT function, a vital tool for precise financial calculations. From understanding its basic uses to practical examples, readers gain a thorough grasp of PMT's capabilities. Additionally, we highlight the advantages of WPS Office, offering powerful features like co-editing and intelligent content recommendations, making it an excellent alternative to Microsoft Excel. Master PMT and unlock the full potential of WPS Office for seamless financial management.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.