Car financing calculator
car dealers are usually associated with some car finance companies. These finance companies enable the car purchaser to repay the borrowed amounts over a fixed period, in equated monthly installments, quite like the home loans.
There are two ways of calculating car finance. These differ based on interest rate regimes. When interest rates harden, lenders are inclined to calculate interest on straight-line method.
For example a car is bought by borrowing $1000 at 10 percent per annum interest. The loan is repayable in 3 years. Interest is to be calculated on straight-line method.
So we have principal P = 1000
Interest rate I = 10/100
And
Term T = 3
For calculating EMIs under straight line method the first step is to calculate the interest for the entire period. i.e.,
P x I x T
Substituting the data, we get
1000 x 10/100 x 3 = 300
The next step is adding this back to the principal
P + 300 = 1300
This total is then divided by term multiplied by the number of months in the year.
i.e.,
1300/(T x 12) or 1300/ (36) = 36.11
Therefore, the EMI in such case would be $36.11.
The other scenario is when interest rates are falling rapidly.
In this situation, the financiers offer car loans on reducing balance method. In such cases, the interest component in the installment keeps coming down and the principal component keeps on increasing as the time passes.
An Excel sheet would be better for calculating EMIs under this method.
For this, the first step would be to give legends.
At A1 type Month number
At B1 type Principal
At C1 type Interest rate
At D1 type Interest component
At E1 type Principal component
At F1 type Equated monthly Installment
At G1 type Balance principal
At A2 type 1
At B2 type the principal amount as per the data i.e., 1000
At C2 type the interest rate as per the data i.e., 10/100
At D2 type +B2*C2/12
At E2 type +F2-D2
At F2 type +D2 + 100 (this is tentative amount, which will be corrected later with permutations and combinations)
At G2 type +B2-E2
At A3 type +A2+1
At B3 type +G2
At C3 type + C2
At D3 copy the formula in D2
At E3 copy the formula in E2
At F3 type +F2
At G3 copy the formula in G2
Now the entire 3rd row, copy and drag down till the column A contains 37 (cell A38)
At I1 type +B38
Now for installment, go to cell F2. Try any amount greater than D2. Watch the content at Cell I1, which now holds the content of cell B38. Because B38 is not visible on the screen without scrolling, it has been brought up so that permutations become easy. When I1 reaches closest to zero, we would have arrived at the EMI on the vehicle for the given data.
Similar changes with interest rates can be made in this car - financing calculator.
There are other factors that need to be considered while availing car finance. These include turnover taxes, and the prepayment of installments. In our example, if the financier had offered to release the amount only after three installments were paid upfront, effective loan received from the financier would be much lower, and therefore, the interest on which such loan is received would be much higher.
For arriving at the correct interest rate in such case, the three installments have to be deducted from the principal amount at cell B2 and permutations and combinations of Cell C2 determine the interest rate.
Other Articles
