A borrower is repaying a loan at 5% effective with payments at the end of each year for 12 years, such that the payment at the end of the first year is 220, at the end of the second year is 210 and so forth until the payment at the end of the12th year is 110. (i) Find the amount of the loan. (ii) Find each of the principal and the interest in the fifth payment. Use (Da)n=(n-an)/i(decreasing annuity formula)
I plugged in the values into that formula got 1513.68 for the amount of loan, which i am not sure if it is right. and i have no clues how to do the second question.
thank you in advanced for your help.
The excel is wrong. Keep in mind that the payment first goes towards interest for the year - then towards repayment of the principal.
A more theoretic approach of solving it:
Consider the loan amount as the sum of the present value of two different annuities. I will be using standard annuity notation.
$B_0 = 100a_{12|0.05} + 10Da_{12|0.05}$
Which gives me the answer $\$1513.674836.$
Now: let us move onto the second part of the question.
We can determine with ease that the total interest and principal repayment of the 5th period is indeed $\$180$. Let us use the retrospective approach (backward looking) to determine the loan balance at that time.
Suppose $B_4$ is the current balance right after the 4th payment. We can derive this number subtracting the future value of all payments to date. In this case, the present value at time 4 can be determined once again by:
$B_4 = B_0(1+0.05)^4 - 220(1+0.05)^3 - 210(1+0.05)^2 - 200(1+0.05) - 190$
Which gives me the answer $\$953.6787236$.
From here, finding the interest and principal portions of the payment becomes trivial. The interest, $I$ earned on the balance at time 5 is simply $iB_4$ and the principal portion is simply $\$180 - iB_4$.
Which gives the answers interest: $\$47.68393618$ and principal: $\$132.3160638$.
Hope this answers your question in a form you can understand, without the use of excel spreadsheets to simply construct an amortization schedule. Note: in determining $B4$, you could have used the formula for $s_{n|i}$ and $Ds_{n|i}$ but I find it easier to just subtract the four terms.
Good luck!