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?
Best Answer
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 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.