Question

A $1200 loan with a finance charge of $108 is to be repaid in 12 equal monthly installments. By using excel (provide excel spreadsheet in answer) find the outstanding loan balances immediately after the fourth payment using :
a) maximum yield method
b) minimum yield method
c) constant ratio method
d) direct ratio method

Answer 1

a) maximum yield method :

Here Interest will be paid at the first Month Itself.

Principal paid on first Month = Monthly Payment - Interest Payment

Monthly Payment = ( Loan Amount + Finance Charge) / No of Installments = ( 1200 + 108 ) / 9 = 109

Ans : Outstanding Balance Immediately after the fourth Payment will be = 872

b) minimum yield method :

Here Interest will be paid at the last Month .

Ans : Outstanding Balance Immediately after the fourth Payment will be = 764

c) constant ratio method

Here Interest and Principal are Paid at constant Rate Each Month.

Interest in Each Month = Total Interest / No of Installments = 108 / 12

Principal in Each Month = Loan Amount / No of Installments = 1200 / 12

Ans : Outstanding Balance Immediately after the fourth Payment will be = 800

d) direct ratio method   = Rule of 78

Here S = Sum of Total Months = ( 1 + 2 + 3 + ..... + 12) = 78

Interest Paid in Each Month = ( Month No / S) * Total Interest

Principal Paid in Each Month = Monthly Payment - Interest Paid in Each Month

Where Monthly Payment = ( Loan Amount + Finance Charge) / No of Installments = ( 1200 + 108 ) / 9 = 109

Ans : Outstanding Balance Immediately after the fourth Payment will be = 778