Calculate a Loan Payment

February 04, 2022 - by Bill Jelen

Problem: I am considering buying a car. I want to calculate the loan payment.

Price, Term and Rate in A1:A3. B1 says 25995. B2 says 60. B3 says 5.25% — Figure 318. Set up the price, term, and interest rate..

Strategy: To calculate your car loan payment, you can use the PMT function. Follow these steps:

1. Enter price, term in months, and annual percentage rate in cells A1:B3. The PMT function has three required arguments: the interest rate, the number of payments in the loan, and the original loan amount.

Gotcha: The interest rate must be entered as a percentage. If you are planning on monthly payments (which is normal), you have to divide the annual percentage rate by 12.

Gotcha: In financial terms, the bank is loaning you $25,995—a positive amount coming to you. Thus, the payments that you make to the bank are really a negative amount—money leaving your wallet. For this reason, the result of the PMT function will be negative. However, you can precede the third argument of the PMT function with a minus sign in order to return a positive payment amount.

2. Enter the formula =PMT(B3/12,B2,-B1) in cell B5.

Calculate the monthly loan payment =PMT(B3/12,B2,-B1). The payment is $493.54. — Figure 319. The PMT function calculates the monthly payment.

This article is an excerpt from Power Excel With MrExcel

Title photo by Cory Rogers on Unsplash