In the MSE question Calculating interest rate of car financing, Juan Hynix asks:
I want a new car which costs $26.000.
But there’s an offer to finance the car: Immediate prepayment: 25% of the original price
The amount left is financed with a loan: Duration: 5 years, installment of $400 at the end of every month.
So I need to calculate the rate of interest of this loan. Do I need Excel for this exercise? Or which formula could I use for this exercise?
You could use Excel (see below) or you could solve the equation below numerically, e.g. using the secant method.
We have a so called uniform series of constant installments .
Let be the nominal annual interest rate. The interest is compounded monthly, which means that the number of compounding periods per year is . Consequently, the monthly installments are compounded at the interest rate per month . The value of in the month is equivalent to the present value . Summing in , from to , we get a sum that should be equal to
This sum is the sum of a geometric progression of terms, with ratio and first term . So
The ratio is called the series present-worth factor (uniform series).
For , and we have:
I solved numerically for using SWP and got
ADDED. Computation in Excel for the principal and interest rate computed above. I used a Portuguese version, that’s why the decimal values show a comma instead of the decimal point.
– The Column is the month ().
– The 2nd. column is the amount still to be payed at the beginning of month .
– The 3rd. column is the interest due to month .
– The 4th. column is the sum .
– The 5th column is the installment payed at the end of month .
The amount satisfies We see that at the end of month , . The last installment at the end of month balances entirely the remaining debt, which is also . We could find by trial and error. Start with and let the spreadsheet compute the table values, until we have in the last row exactly .
James Riggs, David Bedworwth and Sabah Randdhava, Engineering Economics, McGraw-Hill, 4th. ed., 1996, p.43.