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$.

enter image description here

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$.

enter image description here

enter image description here


$^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.


  • $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.

  • $\begingroup$ +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. $\endgroup$ – Américo Tavares Jun 5 '13 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.