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?
 A: 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. 
A: 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.
