Calculating interest rate of car financing

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 $(2)$ below numerically, e.g. using the secant method.

We have a so called uniform series of $n=60$ constant installments $m=400$.

Let $i$ be the nominal annual interest rate. The interest is compounded monthly, which means that the number of compounding periods per year is $12$. Consequently, the monthly installments $m$ are compounded at the interest rate per month $i/12$. The value of $m$ in the month $k$ is equivalent to the present value $m/(1+i/12)^{k}$. Summing in $k$, from $1$ to $n$, we get a sum that should be equal to $$P=26000-\frac{26000}{4}=19500.$$ This sum is the sum of a geometric progression of $n$ terms, with ratio $1+i/12$ and first term $m/(1+i/12)$. So

$$\begin{equation*} P=\sum_{k=1}^{n}\frac{m}{\left( 1+\frac{i}{12}\right) ^{k}}=\frac{m}{1+\frac{ i}{12}}\frac{\left( \frac{1}{1+i/12}\right) ^{n}-1}{\frac{1}{1+i/12}-1}=m \frac{\left( 1+\frac{i}{12}\right) ^{n}-1}{\frac{i}{12}\left( 1+\frac{i}{12} \right) ^{n}}.\tag{1} \end{equation*}$$

The ratio $P/m$ is called the series present-worth factor (uniform series)$^1$.

For $P=19500$, $m=400$ and $n=5\times 12=60$ we have:

$$\begin{equation*} 19500=400 \frac{\left( 1+\frac{i}{12}\right) ^{60}-1}{\frac{i}{12}\left( 1+\frac{i}{12} \right) ^{60}}.\tag{2} \end{equation*}$$

I solved numerically $(2)$ for $i$ using SWP and got $$\begin{equation*} i\approx 0.084923\approx 8.49\%.\tag{3} \end{equation*}$$

ADDED. Computation in Excel for the principal $P=19500$ and interest rate $i=0.084923$ computed above. I used a Portuguese version, that's why the decimal values show a comma instead of the decimal point.

• The Column $k$ is the month ($1\le k\le 60$).
• The 2nd. column is the amount $P_k$ still to be payed at the beginning of month $k$.
• The 3rd. column is the interest $P_ki/12$ due to month $k$.
• The 4th. column is the sum $P_k+P_ki/12$.
• The 5th column is the installment payed at the end of month $k$.

The amount $P_k$ satisfies $$P_{k+1}=P_k+P_ki/12-m.$$ We see that at the end of month $k=60$, $P_{60}+P_{60}i/12=400=m$. The last installment $m=400$ at the end of month $k=60$ balances entirely the remaining debt, which is also $400$. We could find $i$ by trial and error. Start with $i=0.01$ and let the spreadsheet compute the table values, until we have in the last row exactly $P_{60}+P_{60}i/12=400$.

--

$^1$ James Riggs, David Bedworwth and Sabah Randdhava, Engineering Economics,McGraw-Hill, 4th. ed., 1996, p.43.

An approximate solution can be obtained by using continuously-compounded (rather than monthly-compounded) interest.

Let

• $i$ = the nominal annual interest rate
• $P$ = the principal of the loan
• $m$ = the monthly payment amount
• $N$ = the term of the loan, in years

Let $B(t)$ = the remaining balance of the loan after $t$ years. Then $B'(t)$ = (annualized interest) - (annualized payments) = $i \cdot B(t) - 12m$. Furthermore, we have the initial condition $B(0) = P$, and the payoff condition $B(N) = 0$.

Solving the differential equation $B'(t) = i \cdot B(t) - 12m$ gives $B(t) = Ce^{it} + \frac{12m}{i}$. The initial condition $B(0) = P$ gives $C = P - \frac{12m}{i}$. Solving $B(N) = 0$ for $m$ gives the continuous-interest amortization formula:

$m = \frac{Pi}{12 (1 - e^{-iN})}$

Plugging in $P = 19500$, $m = 400$, and $N = 5$ gives you the equation:

$(19500 i - 4800) e^{5i} = -4800$

which can't be solved algebraically, but solving it numerically gives $i \approx 8.61\%$.

Edit: An algebraic approximation for the solution can be obtained by using the Taylor series $e^x = 1 + x + \frac{x^2}{2} + \frac{x^3}{6} + \frac{x^4}{24} + \cdots$

With the first-degree approximation $e^{-iN} \approx 1 - iN$, the $i$'s cancel out and give $m = \frac{P}{12N}$. This gives you the monthly payment if there were no interest, but it's not very useful for finding the interest rate.

With the second-degree approximation $e^{-iN} \approx 1 - iN + \frac{(iN)^2}{2}$, you get $i \approx \frac{12mN-P}{6mN^2}$. In your specific problem, that gives $i \approx 7.50\%$.

With the third-degree approximation $e^{-iN} \approx 1 - iN + \frac{(iN)^2}{2} - \frac{(iN)^3}{6}$, you get the quadratic equation $(2mN^3)i^2+(-6mN^2)i+(12mN-P) = 0$. Use the quadratic formula. In your problem, you get the two solutions $i \approx 8.79\%$ or $i \approx 51.21\%$. The first one is much more accurate.

• +1 In this blog post Timothy Gowers discussed the following problem: Suppose for simplicity that the interest rate for an interest-only mortgage would be 5% and that this rate never changes. If I take out a repayment mortgage of £50,000 and pay £500 a month, then roughly how long will it take me to pay off the mortgage? where the discrete problem (payments once a month) is replaced by a continuous one (money leaking out of my bank account at a constant rate), as in your answer. – Américo Tavares Jun 5 '13 at 17:33