• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
  • Windows
  • iOS
  • Mac
  • Android
  • Office

How to Use Excel PMT Function

By: Waseem Patwegar

The PMT Function in Excel returns the periodic repayment amount for a loan. You will find below the steps to use Excel PMT Function to calculate monthly mortgage payments.

Use Excel PMT Function

Excel PMT Formula

You can get the exact Syntax of the PMT Function in Microsoft Excel by typing =PMT in any cell of an Excel worksheet.

Syntax of PMT Function = PMT(Rate, NPER, PV, [FV], [Type])

  • Rate: Interest Rate
  • NPER: The number of periods required to pay the loan
  • PV: The present value of the loan
  • [FV]: [Optional] – The future value or cash balance that you want after the last payment is made. Excel assumes a default value of 0 in case you do not specify anything
  • [Type]: [Optional] – Excel assumes a default value of “0” which means the payments are due on the last day of the month. You can specify the value “1” in case the payments are due on the first day of the month.

1. Find Monthly Mortgage Payments Using Excel PMT Formula

In this example, let us try to find monthly mortgage payments required to pay back a loan of $260,000 obtained at fixed interest rate of 3.69% per annum.

Similar to housing loans from a bank, the borrowed amount is to be fully paid back in 25 years using fixed monthly payments.

1. The first step is to organize the available data (Loan Amount, Period of loan, Interest Rate) in an Excel Worksheet and convert the data into monthly values.

Data For Monthly Mortgage Payment Calculation

As you can see in above image, the Period of Loan (Amortization period) and Interest Rate have been converted into their equivalent monthly values.

2. Next, place curser in Cell C8 (where you want the result) and type =PMT – This will make Excel provide the Syntax of PMT Function that you can follow in the next steps.

Syntax of Excel PMT Function

3. Going by the Syntax, select Cell C6 (0.31%) as the Rate.

Input Rate in Excel PMT Formula

4. Select, Cell C5 (300) as nper or the number of payments required.

Input nper in Excel PMT Formula

5. Select, Cell B4 ($260,000) as the present value of the loan.

Input Present Loan Value in Excel PMT Formula

6. Enter or Type 0 (zero) as the Future Value of the loan (to be fully paid back at the end of term).

Enter Future Loan Value in Excel PMT Formula

7. Select 0 (zero) as Type (Paid by End of period).

Enter Type in PMT Formula

8. Finally, close the bracket and hit the enter key on the keyboard of your computer to get your monthly mortgage payments.

Monthly Mortgage Payments Using Excel PMT Function

As you can see from above steps, all that is required to use the PMT Formula in Excel is to follow the Syntax of the function.

2. Quick Way to Use PMT Formula

Here is an alternate and a quick way to use the Excel PMT formula to calculate monthly mortgage payments.

1. Open Microsoft Excel Worksheet.

2. Click on Formulas tab in the top Menu bar > select Financial from ‘Function Library’ section and double-click on PMT in the drop-down menu.

Get PMT Function from Formulas Tab in Excel

2. In Function arguments box, you can directly enter Rate, Nper and Pv values to calculate monthly mortgage payments.

PMT Function Arguments Box in Excel

Tip: Instead of typing values, you can also point directly to Cells in the worksheet where the values for rate, Nper and Pv are located.

Related
  • How to use Excel INDEX MATCH Function
  • How to Use VLOOKUP Function in Excel
  • How to Use Excel SUMIFS Function

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar

Twitter
Facebook



Recent Posts

  • How to Restart iPhone Without Using Power Button
  • iMessage Notifications Not Working on iPhone
  • How to Stop Skype from Starting Automatically in Windows 11
  • How to Create System Restore Point in Windows 11
  • How to System Restore Windows 11 Computer



About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved