How to Calculate a Car Loan in Excel

PDF download Download Article
PDF download Download Article

Microsoft's Excel spreadsheet program can be used for many different types of business and personal applications. For instance, you can use Excel to calculate car loan transactions and payment amounts as well as the total interest paid over the life of a loan. In addition, you can use Excel to compare multiple scenarios in order to make sound financial decisions. Here's how to calculate a car loan in Excel before you make a commitment.

Steps

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/b\/b8\/Calculate-a-Car-Loan-in-Excel-Step-1-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-1-Version-3.jpg","bigUrl":"\/images\/thumb\/b\/b8\/Calculate-a-Car-Loan-in-Excel-Step-1-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-1-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/9\/96\/Calculate-a-Car-Loan-in-Excel-Step-2-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-2-Version-3.jpg","bigUrl":"\/images\/thumb\/9\/96\/Calculate-a-Car-Loan-in-Excel-Step-2-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-2-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    [1]
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/f1\/Calculate-a-Car-Loan-in-Excel-Step-3-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-3-Version-3.jpg","bigUrl":"\/images\/thumb\/f\/f1\/Calculate-a-Car-Loan-in-Excel-Step-3-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-3-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Car sale price, Trade-in value, Down payment, Rebates, Additional charges and Amount financed. [2]
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/39\/Calculate-a-Car-Loan-in-Excel-Step-4-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-4-Version-3.jpg","bigUrl":"\/images\/thumb\/3\/39\/Calculate-a-Car-Loan-in-Excel-Step-4-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-4-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • The car sale price is negotiated with the dealership.
    • The dealer incentive, rebates and additional features items may not apply to your situation.
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/4\/4c\/Calculate-a-Car-Loan-in-Excel-Step-5-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-5-Version-3.jpg","bigUrl":"\/images\/thumb\/4\/4c\/Calculate-a-Car-Loan-in-Excel-Step-5-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-5-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Calculate the amount financed in cell B6 by entering "=B1-B2-B3-B4+B5" in the cell, without quotation marks, and pressing "Enter."
  6. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/6c\/Calculate-a-Car-Loan-in-Excel-Step-6-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-6-Version-3.jpg","bigUrl":"\/images\/thumb\/6\/6c\/Calculate-a-Car-Loan-in-Excel-Step-6-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-6-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Amount financed, Interest rate, Loan Term and Payment amount. [3]
  7. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/6e\/Calculate-a-Car-Loan-in-Excel-Step-7-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-7-Version-3.jpg","bigUrl":"\/images\/thumb\/6\/6e\/Calculate-a-Car-Loan-in-Excel-Step-7-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-7-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • Type "=B6," without quotation marks, in cell E1 to copy over the amount financed.
    • Make sure your interest rate is entered as a percentage in cell E2.
    • Enter the loan term in months in cell E3.
  8. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/fc\/Calculate-a-Car-Loan-in-Excel-Step-8-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-8-Version-3.jpg","bigUrl":"\/images\/thumb\/f\/fc\/Calculate-a-Car-Loan-in-Excel-Step-8-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-8-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    "=PMT(E2/12,E3,E1)." [4]
  9. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/17\/Calculate-a-Car-Loan-in-Excel-Step-9-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-9-Version-3.jpg","bigUrl":"\/images\/thumb\/1\/17\/Calculate-a-Car-Loan-in-Excel-Step-9-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-9-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Add up the total interest paid over the life of the loan in cell E5 by entering the following formula, without quotation marks: "=(-E4*E3)-E1. In this step we must add the Amount Financed (E1) - this may seem counterintuitive, but because Excel correctly treats our calculated Payment as a cash outflow and assigns it a negative value, we must add back the Amount Financed to reach a Total Interest Paid amount." [5]
    • This formula calculates the total of all payments less the amount financed to arrive at the total interest paid over the life of the loan.
  10. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/2e\/Calculate-a-Car-Loan-in-Excel-Step-10-Version-3.jpg\/v4-460px-Calculate-a-Car-Loan-in-Excel-Step-10-Version-3.jpg","bigUrl":"\/images\/thumb\/2\/2e\/Calculate-a-Car-Loan-in-Excel-Step-10-Version-3.jpg\/aid1472458-v4-728px-Calculate-a-Car-Loan-in-Excel-Step-10-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • You can see how the scenario would look with a higher or lower interest rate, a shorter or longer loan term or with a larger down payment.
    11. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Make sure you are entering the annual percentage rate in cell E2. Note that the interest rate is divided by 12 in the formula in cell E4 to represent a monthly interest rate for each period in the term.
        Thanks
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Things You'll Need

      • Computer
      • Excel
      • Car transaction and loan details

      You Might Also Like

      How to
      Recover a Corrupt Excel File
      How to Merge Cells in Microsoft Excel: A Quick Guide
      Easily Create a Drop-Down List in Microsoft Excel: Setup & Customization
      How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Unprotect an Excel Sheet
      How to Create a Graph in Excel
      How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      How to
      Link Sheets in Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to Unhide Multiple Rows in Microsoft Excel
      How to Combine Columns in Excel Without Losing Data
      How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-au/office/save-your-workbook-92e4aae0-452d-497f-a470-570610ff720a
      2. ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
      3. ↑ https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
      4. ↑ https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
      5. ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 270,631 times.

      Reader Success Stories

      • Dale Stiles

        Apr 11, 2016

        "These instructions also work very well in Google Sheets where I first created it. I then recreated it in Excel for ..." more
      Share your story

      Is this article up to date?

      Advertisement