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.
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.
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.
3. Going by the Syntax, select Cell C6 (0.31%) as the Rate.
4. Select, Cell C5 (300) as nper or the number of payments required.
5. Select, Cell B4 ($260,000) as the present value of the loan.
6. Enter or Type 0 (zero) as the Future Value of the loan (to be fully paid back at the end of term).
7. Select 0 (zero) as Type (Paid by End of period).
8. Finally, close the bracket and hit the enter key on the keyboard of your computer to get your monthly mortgage payments.
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.
2. In Function arguments box, you can directly enter Rate, Nper and Pv values to calculate monthly mortgage payments.
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.