Car loan payment formula in Excel

Build a monthly car loan payment in Excel with the PMT function, set the rate, term and loan amount correctly, and see how each input changes the result.

3 min read3 viewsCar loan payment formula in Excel
— · 0 votes
Contents
  1. Setting rate, nper and pv
  2. Why the payment comes out negative
  3. Checking the result in Google Sheets
  4. Questions about the car loan payment formula
  5. What is the monthly car payment formula in Excel?
  6. How do I write the car loan repayment formula in Excel for a yearly rate?
  7. Does the PMT function work in Google Sheets as well?
  8. Which arguments do I need for the PMT function?

The car loan payment formula in Excel is the PMT function, which returns the fixed monthly amount for a loan from the interest rate, the number of periods and the amount borrowed. You type =PMT(rate, nper, pv) into a cell, and the result is the payment that repays the loan with interest over the term. The guide below shows how to set the rate and term so the answer matches a car loan rather than a general annuity.

Setting rate, nper and pv

A car loan payment formula in Excel needs the rate per period, not the annual rate. Divide the yearly interest rate by 12 so a monthly payment uses a monthly rate, which is the rate divided by 12 rule. The nper argument is the total number of monthly payments, so multiply the loan term in years by 12. The pv argument is the present value, which for a car loan is the amount financed after any down payment or trade-in.

Why the payment comes out negative

The PMT function returns a negative number because Excel treats money paid out as a negative cash flow. A car loan payment formula in Excel follows that sign convention, so the payment appears with a minus sign by default. You can wrap the function in a negative sign or use a minus before pv to show a positive payment. Keep the sign choice consistent if you compare several loan options side by side.

Checking the result in Google Sheets

The same car loan payment formula works in Google Sheets, because the PMT function takes the same three arguments there. Enter the monthly rate, the number of payments and the loan amount in separate cells, then reference those cells in the formula. Changing a cell updates the payment at once, which helps you test a shorter term or a larger down payment. Spreadsheet results are estimates, so confirm the final figure with the lender.

Questions about the car loan payment formula

What is the monthly car payment formula in Excel?

The monthly car payment formula in Excel is =PMT(rate, nper, pv), where rate is the monthly interest rate, nper is the number of monthly payments and pv is the amount financed. Excel returns the payment as a negative number, which you can flip with a minus sign. The formula assumes equal payments and a fixed interest rate for the whole term.

How do I write the car loan repayment formula in Excel for a yearly rate?

The car loan repayment formula in Excel takes a monthly rate, so divide the yearly rate by 12 before you pass it to PMT. A yearly rate of 6 percent becomes 0.06 divided by 12 in the rate argument. The nper argument must then count months, not years, to match the monthly rate.

Does the PMT function work in Google Sheets as well?

Yes, the PMT function works in Google Sheets with the same three arguments as in Excel. You enter the monthly rate, the number of payments and the loan amount, and Sheets returns the payment for that loan. Cell references update the result when you change an input, so you can compare terms quickly.

Which arguments do I need for the PMT function?

The PMT function needs three arguments for a car loan: rate, nper and pv. Rate is the interest rate per period, nper is the total number of periods and pv is the amount borrowed. Two optional arguments, fv and type, are left out for a standard loan that ends at zero and pays at the end of each period.

Read next

Latest articles