Question

You borrowed an X amount of money from a local bank to be repaid over N months at an interest rate i (assume your own numbers for X, i, N).

1. Create a table (using Excel) showing each month’s interest in $ (I), principal repayment, and amount of principal remaining at the end of each month.
2. Suppose that you decided to pay out the remaining principal all at once after few monthly payments (< N), how much will you pay? Use the P/A relationship to answer this part and compare to the numbers in the table you established in part (a).

Answer 1

Lets say we borrow 12000 to be repaid over 12 months at an interest rate of 12% per annum (1% per month).

The payment schedule, created in excel, is shown below. A formula view is shown after, for clarification. Explanation follows.

Formula View-

The formla PMT gives us the EMI. PPMT gives us the principal. IPMT gives us interest. Balance is previous balance-principal payment (the sign is plus because the principal amounts are negative, shown in red).

B. Lets say we decide to pay the whole thing in one go after EMI number 6. The balance remaining at that time is $6179. We know that P/A is Uniform Series Present Worth Factor. The EMIs we have are 1066.19. So present worth of the remaining 6 EMIs is

1066.19(P/A,1%,6)

Using a compound interest table, we get that the compound factor here would be 5.79548. This factor can also be calculated manually by the following formula-

Using the factor, we get

1066.19*5.79548= 6179.083

As can be seen, we would be paying exactly same as what is remainig in balance.