Question

A $7,500 demand loan was taken out on March 4 at a fixed interest rate of 7.72% with fixed monthly payments of $1,200. The first monthly repayment is due April 4 and the 4th of every month thereafter. Prepare a full repayment schedule for the loan.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A B C D E F G Loan Amount Annual interest rate Monthly interest rate Monthly payment $7,500.00 7.72% 0.6433% $1,200.00 Month 1 2 3 4 5 6 7 Full Payment Schedule of Loan Monthly Interest | Principal | Outstanding Date payment paid paid Balance 4-Apr $1,200.00 $48.25 $1,151.75 $6,348.25 4-May $1,200.00 $40.84 $1,159.16 $5,189.09 4-Jun $1,200.00 $33.38 $1,166.62 $4,022.47 4-Jul $1,200.00 $25.88 $1,174.12 $2,848.35 4-Aug $1,200.00 $18.32 $1,181.68 $1,666.68 4-Sep $1,200.00 $10.72 $1,189.28 $477.40 4-Oct $480.47 $3.07 $477.40 $0.00

Cell reference -

A в с D E F Loan Amount 7500 Annual interest rate 0.0772 Monthly interest rate =C3/12 Monthly payment 1200 Full Payment Schedule of Loan Month Date Monthly payment Interest paid Principal paid Outstanding Balance 43925 =$C$5 43955 =$C$5 43986 =$C$5 44016 =$C$5 44047 =$C$5 44078 =$C$5 44108 =G14+E15 =C2*C4 | =G9*$C$4 =G10*$C$4 =G11*$C$4 =G12*$C$4 | =G13*$C$4 =G14*$C$4 =D9-E9 =D10-E10 =D11-E11 =D12-E12 I=D13-E13 =D14-E14 =D15-E15 =C2-F9 =G9-F10 =G10-F11 =G11-F12 =G12-F13 =G13-F14 G14-F15

Hope it will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.