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.
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.
The PMT function applies only to loans that apply the payment to principal and interest with a fixed interest rate.
- Jupiterimages/Comstock/Getty Images