Question

An amount of $200,000 is borrowed for 5 years at a rate of 12%. Make an amortization schedule showing the quarterly payment, the quarterly interest on the outstanding balance, the portion of the payment going toward reducing the debt, and the balance.

Answer 1

formula for B8: =IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

drag down for other rows for column B

formula for C8: =IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

drag down for other rows for column C

formula for D8: =IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

drag down for other rows for column D

E8: =C5+D8

E9: =IF(A9<=$C$3*$C$4, E8+D9, "")

drag E9 down for other values of column E