Microsoft Excel is a very versatile software that can be used for many purposes, both in the office and in private life. For example, you can use an Excel sheet to calculate the installments and manage the financing payments for your new car, as well as to know in advance the amount of interest you will have to pay based on the duration of the financing plan. You can also use Excel to simulate different scenarios, simplifying the need to make the decision that best suits your financial situation. This tutorial shows you how to create a model to calculate a financing plan for your new car, before signing up for the real one.
Steps
Step 1. Launch Microsoft Excel
Step 2. Create a new worksheet and save it with a descriptive name, for example 'Financing_Car'
Step 3. Enter your data labels in the cell range 'A1-A6' as follows:
Car Sale Price, Trade-in Value, Down Payment, Discount, Additional Costs, Financed Amount.
Step 4. Enter the amount of each individual item in the cell range 'B1-B5', according to the terms of the car purchase agreement
- The purchase price of the car negotiated with the dealer.
- The incentives applied by the dealer, any discount and any other aspect that does not impact the final price of the car.
Step 5. Calculate the total amount to finance by entering the following formula in cell 'B6':
'= B1-B2-B3-B4 + B5' (without quotes). When finished press the 'Enter' key.
Step 6. In the cell range 'D1-D4', enter the data labels related to the financing plan as follows:
Amount to be financed, Interest rates, Number of installments of the loan, Total to be paid.
Step 7. Fill in the cell range 'E1-E3' with your loan information
- Inside cell 'E1', type the following formula '= B6' (without quotes). In this way the total amount to be financed typed in cell 'B6' will automatically be reported in cell 'E1'.
- In cell 'E2', enter the interest rate applied to your loan expressed as a percentage.
- In cell 'E3', enter the number of installments that make up your loan.
Step 8. Calculate the total amount to be paid by entering the following formula in cell 'E4':
'= PMT (E2 / 12, E3, E1)' (without quotes).
Step 9. Inside cell 'E5', enter the formula for calculating the interest to be paid based on the duration of the loan:
'= (- E4 * E3) -E1' (without quotes).
