How to Calculate the Function Monthly Payment PMT

Save
You can use the PMT function to calculate your monthly mortgage payment.
You can use the PMT function to calculate your monthly mortgage payment. (Image: Jupiterimages/Comstock/Getty Images)

There are many factors that affect a loan’s monthly payment. If you’re considering different options for a mortgage or an auto loan, you can use Microsoft Excel’s PMT function to calculate a loan’s monthly payment by inputting various factors, such as interest rate and loan amount. Once you set up these factors in an Excel worksheet, you can change them to calculate a new loan amount and generate different loan scenarios. You can use this information to determine a range of loan amounts and interest rates that will result in a monthly payment you can afford.

Click in cell A1 and type “Rate,” “Term” and “Loan Amount” in cells A1, A2 and A3, respectively. Press “Enter” after typing in each cell.

Click in cell B1 and type “0.065,” “30” and “$200,000” in cells B1, B2 and B3, respectively. Press “Enter” after typing in each cell. These values correspond to the descriptions in cells A1 through A3. “0.065” is an example of your loan’s annual interest rate as a decimal. “30” is the number of years of your loan. “$200,000” is the beginning loan balance.

Click in cell A5 and type “Monthly Payment.” Press “Enter.”

Click in cell B5 and type “=PMT(B1/12,B2*360,-B3).” Press “Enter.” This assigns the rate, term and loan amount values in cells B1 through B3 to each respective argument in the PMT function, which are rate, nper and pv. The rate is divided by 12 and the term is multiplied by 12 to convert them into monthly amounts. The loan amount has a negative sign to generate a positive answer. The monthly payment in cell B5 equals $1,083.33.

Tips & Warnings

  • The two optional arguments in the PMT function are “fv,” which determines the future value of the loan after its last payment, and “type,” which determines whether you make your payment at the beginning or end of each payment period. You can leave these blank and Excel automatically sets the future value to 0 and the type to an end-of-period payment after interest has accrued, which applies to a typical loan scenario.

Related Searches

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

4 Credit Myths That Are Absolutely False

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!