I have a loan at a nominal annual interest rate compounded monthly $i^{(12)} = 12$% and is repaid with $120$ monthly payments starting one month after the loan. The monthly payments are $600$.
I am asked to find the i) loan first, ii) find the total interest paid, iii) find the interest paid in 10th payment and iv) the principal repaid in the 20th payment.
I was not sure if I calculated and used the right formulas.
I calculated the loan by doing
$n = 120/12 = 10$ years
since the nominal interest $i^{(12)} = 12%$
then $i = 0.12/12 = 0.01$
Since there are $120$ monthly payments of $600$ dollars each, I did
$$\require{enclose}
\begin{align}
L = 600 a_{\enclose{actuarial}{10} i} = 600(1-(1/1.12)^{10}/0.01 = 40681.60
\end{align}$$
For the total interest: I took the monthly payments of $600$ dollars multiplied by the $120$ and subtract by the total loan found above to get $31318.40$
For finding the interest paid in 10th payment and the principal repaid in the 20th payment I wasn't sure if I did it correctly using the table form, i did
$$\begin{array}{c|c|c|c|c}
\text{Period} & \text{End Payment} & \text{Interest repaid} & \text{Priniple repaid} & \text{Outstanding balance} \\
\hline
0 & 0 & 0 & 0 & 40681.60 \\
1 & 600 & 406.81 & 193.184 & 40488.42 \\
2 & 600 & 404.88 & 195.11 & 40293.30 \\
3 & 600 & 402.93 & 197.06 & 40096.23 \\
\vdots & \vdots & \vdots & \vdots & \vdots \\
10 & 600 & 386.60 & 213.39 & 38447.07 \\
\end{array}$$
I'm assuming towards the end of the 120th payment, the outstanding should be zero. I don't have a special program other than excel… and calculating a specific number of payments can be tedious. Is there a quicker way to calculate?
Best Answer
As I said in a comment, the amount of the loan should be calculated as $$\require{enclose} L = 600 a^{(12)}_{\enclose{actuarial}{10} i} = 600\left(1-(1/1.01)^{120}\right)/0.01 = 41820.31 $$
You calculation of the total interest is correct. It's just the total payments less the amount of the loan.
The interest paid in the $10$ payment is $1\%$ of the amount of the loan outstanding after $9$ payments. Since there are then $111$ payments left, you do it it just as you calculated the amount of the loan, but with $111$ in the exponent instead of $120$.
For the amount of principal paid in the $20$ payment, calculate the amount of interest in the payment as above. The amount of principal in the payment is $600$ minus the amount of interest in the payment.