When buying a car (new or used), few people immediately sign a check for the full amount; almost all of them require funding. Before entering into a loan agreement with a bank, through the dealer or with another credit institution, you need to calculate the installments to understand what impact they will have on your monthly budget. This article shows you how to calculate the installments with the help of an Excel spreadsheet and how to consider some factors that change the financed capital.
Steps
Part 1 of 2: Determine the Capital to Fund
Step 1. If this applies to you, deduct the trade-in value of your used car from the price of the new car
Step 2. Calculate any fees required for matriculation, change of ownership and other ancillary costs
Add this value to the purchase price. For example, if there is a 7% tax for the purchase of the new car which has a price of € 15,000, you will have to add another € 1,050, thus bringing the price to € 16,050.
This type of tax is independent of the value of your used in trade-in and is applied to the total value of the new car
Step 3. Add any fees the dealer charges
This includes putting it on the road, transport costs or managing the financing file.
Step 4. Subtract the value of the deposit from the total
Remove the value of the down payment you want to pay from the total amount to find the capital to finance.
Part 2 of 2: Using an Excel Worksheet for Calculating Installments
Step 1. Use Excel to determine the installments
Use the '= PTM' function. The following example takes into consideration a loan of € 15,090 in 48 months with an interest of 7%.
Step 2. Open Microsoft Excel and type these descriptions in the first 4 rows of column A:
- Installment.
- Number of Installments.
- Present value.
- Future Value.
Step 3. Enter the following values in column B next to each description:
- 7, 00%
- 48
- 15.090
- Zero
Step 4. Enter the formula “= PMT (B1 / 12, B2, B3, B4)” in a cell below the numbers
- Type "= PMT (" and click on the cell with 7, 00% so "B1" appears after the opening parenthesis.
- Type “/ 12,” (including comma) and click on the cell with 48 to see “B2” appear.
- Type a comma after "B2" and click on the cell with 15.090 to make "B3" appear.
- Type a comma after "B3" and click on the cell with the 0 to make "B4" appear.
- Close the parenthesis at the end of the formula.
Step 5. Press “Enter” and the formula will be replaced by the value of the monthly payment, which is € 361.35
Step 6. Edit the data
Change the variables according to your specific case, such as the amount financed or the number of installments, to see how the value of the installments changes.
Advice
- Check the type of interest that is applied. In most cases, an annual percentage rate of charge (APR) is used. However, some financial companies use the nominal annual rate or TAN. An interest compounded monthly at 7% corresponds to an APR of 7% but a slightly higher TAN, 7.22%.
- Compare the interest rates that are offered by various banks, financial companies, dealers and on the internet. A few tenths of a percent difference can save you hundreds or even thousands of euros in interest. Indirect financing from the concessionaire can be cheaper, if it is a reliable dealer: remember, however, that he receives a commission on the loan.
