Download ArticleDownload ArticleThis article was co-authored by Bryan Hamby. Bryan Hamby is the owner of Auto Broker Club, a trusted auto brokerage in Los Angeles, California. He founded Auto Broker Club in 2014 out of a passion for cars and a unique talent for customizing the car dealership process to be on the buyer’s side. With 1,400+ deals closed, and a 90% customer retention rate, Bryan’s focus is to simplify the car buying experience through transparency, fair pricing, and world class customer service.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 266,537 times.
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
Download Article
Launch Microsoft Excel.
Open a new worksheet and save the file with a descriptive name such as "Car Loan."[1]Advertisement
Create labels for the cells in A1 down through A6 as follows: Car sale price, Trade-in value, Down payment, Rebates, Additional charges and Amount financed.[2]
Enter the amounts for each item from your proposed car loan in cells B1 down through B5.- The car sale price is negotiated with the dealership.
- The dealer incentive, rebates and additional features items may not apply to your situation.
Calculate the amount financed in cell B6 by entering "=B1-B2-B3-B4+B5" in the cell, without quotation marks, and pressing "Enter."
Make labels for the loan details in cells D1 down through D4 as follows: Amount financed, Interest rate, Loan Term and Payment amount.[3]
Fill in the information for the loan details in cells E1 down through E3.- 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.
Figure the payment amount by entering the following formula, without the quotation marks, in cell E4: "=PMT(E2/12,E3,E1)."[4]
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.
Review your entries and results and make adjustments to the variables.- 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.
Expert Q&A
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
Things You'll Need
- Computer
- Excel
- Car transaction and loan details
References
- ↑ https://support.microsoft.com/en-au/office/save-your-workbook-92e4aae0-452d-497f-a470-570610ff720a
- ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
- ↑ https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
- ↑ https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
- ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
About This Article
Reader Success Stories
"These instructions also work very well in Google Sheets where I first created it. I then recreated it in Excel for giggles and grins. The instructions are straightforward and easy to follow."..." more
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
