Question

The face value of any bond below is $100. For coupon bonds, coupons are paid every 6 months.

3. (4 points) A new large screen HDTV costs $4000. 10% is required for the down payment. The remaining 90% will be financed by a 12-month loan with annual interest rate 12%. The first payment is due in a month. What is the monthly payment? For each of the twelve payments, calculate interest payment and principal payment using Excel (keep 2 digits after the decimal point).

Answer 1

Loan amount = TV price * 90% = $4,000 * 90% = $3,600

Monthly loan payment is calculated using PMT function in Excel :

rate = 12% / 12   (converting annual rate into monthly rate)

nper = 12 (1 year loan with 12 monthly payments)

pv = 3600 (loan amount)

PMT is calculated to be $319.86

A1 X for =PMT(12%/12,12,3600) B C D E F A 1 ($319.86)

Interest in any month = principal outstanding at beginning of month * 12% / 12

Principal portion of monthly payment = monthly payment minus interest portion of payment

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment

$319.86 Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end 1 $ 3,600.00 $319.86 $ 36.00 $ 283.86 $ 3,316.14 2 $ 3,316.14 $319.86 $ 33.16 $ 286.69 $ 3,029.45 3 $ 3,029.45 $319.86 $ 30.29 $ 289.56 $ 2,739.89 4 $ 2,739.89 $319.86 $ 27.40 $ 292.46 $ 2,447.43 5 $ 2,447.43 $ 319.86 $ 24.47 $ 295.38 $ 2,152.05 6 $ 2,152.05 $319.86 $ 21.52 $ 298.34 $ 1,853.72 7 $ 1,853.72 $319.86 $ 18.54 $ 301.32 $ 1,552.40 8 $ 1,552.40 $319.86 $ 15.52 $ 304.33 $ 1,248.07 9 $ 1,248.07 $319.86 $ 12.48 $ 307.37 $ 940.69 10 $ 940.69 $ 319.86 $ 9.41 $ 310.45 $ 630.24 11 $ 630.24 $319.86 $ 6.30 $ 313.55 $ 316.69 13 12 $ 316.69 $ 319.86 $ 3.17 $ 316.69 $ 0.00

А G H =PMT(12%/12,12,-3600) 1 Month 2 1 32 4 3 в Principal outstanding at beginning Payment 3600 =$H$2 =F2 =$H$2 =F3 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =F10 =$H$2 =F11 =$H$2 =F12 =$H$2 Interest =B2*12%/12 =B3*12%/12 =B4*12%/12 =B5*12%/12 =B6*12%/12 =B7*12%/12 =B8*12%/12 =B9*12%/12 =B10*12%/12 =B11*12%/12 =B12*12%/12 =B13*12%/12 76 8 7 9 8 10 9 11 10 12 11 13 12 Principal outstanding at end =B2-E2 =B3-E3 =B4-E4 =B5-E5 =B6-E6 =B7-E7 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13 Principal =C2-D2 =C3-D3 =C4-D4 =C5-D5 =C6-D6 =C7-D7 =C8-D8 =C9-09 =C10-D10 =C11-D11 =C12-D12 =C13-D13