The second picture is a spreadsheet model of an example question.

YMMV issues a 11 year $17,000,000 face value bond with a coupon of 3.250%. The yield rate is j(2) = 4.750%. YMMV will also make semi-annual deposits in a sinking fund that pays r(52) = 3.823%. Build a spreadsheet showing the bond amortization table and the account balance for the sinking fund. How much are the sinking fund deposits? How much will the bondholders lose if YMMV defaults after 6 years? A B D E F G H I J K L M N 1 Face Value $10,000.00 Sinking Fund 2 3 4 5 Maturity (yrs) Maturity (prd) 10 20 Nominal Rate Comp Freq 3.00% 365 Coupon Coupon Freq Eff rate per prdr 5.00% 2 2.50% PUT EQUATION HERE Pmt Freq Eff Rate per pmt prd Num SF pmts Pmt Amt 2 1.51124% 20 $431.98 6 7 8 9 10 11 12 13 14 15 PUT EQUATION HERE Yield Comp Eff yield per prd i 4.00% 2 2.00% PUT EQUATION HERE 16 Time Amortization of Premium/Discount Price (t) Premium / Discount Time (sinking Account fund pmt prd) Balance Loss on Default 17 Pmt Amt Interest Amt (in coupon periods) PUT EQUATION PUT EQUATION HERE HERE 18 PUT EQUATION HERE PUT EQUATION HERE PUT EQUATION HERE 0 1 2 3 4 5 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $216.35 $215.68 $214.99 $214.29 $213.58 $212.85 $212.11 $211.35 $210.58 $209.79 $208.98 $208.16 $207.33 $206.47 $205.60 $204.71 $203.81 $202.88 $201.94 $200.98 $33.65 $34.32 $35.01 $35.71 $36.42 $37.15 $37.89 $38.65 $39.42 $40.21 $41.02 $41.84 $42.67 $43.53 $44.40 $45.29 $46.19 $47.12 $48.06 $49.02 $10,817.57 $10,783.92 $10,749.60 $10,714.59 $10,678.89 $10.642.46 $10,605.31 $10,567.42 $10,528.77 $10,489.34 $10,449.13 $10,408.11 $10,366.27 $10,323.60 $10.280.07 $10,235.67 $10,190.39 $10,144.19 $10,097.08 $10,049.02 $10,000.00 $817.57 $783.92 $749.60 $714.59 $678.89 $642.46 $605.31 $567.42 $528.77 $489.34 $449.13 $408.11 $366.27 $323.60 $280.07 $235.67 $190.39 $144.19 $97.08 $49.02 $0.00 GEBEDOO VOLWNO $0.00 $431.98 $870.49 $1,315.62 $1,767.48 $2,226.17 $2,691.79 $3,164.45 $3,644.25 $4,131.30 $4,625.72 $5,127.60 $5,637.07 $6,154.24 $6,679.22 $7,212.14 $7,753.11 $8,302.26 $8,859.71 $9,425.58 $10,000.00 $10,817.57 $10,351.94 $9,879.12 $9,398.97 $8,911.40 $8,416.29 $7,913.52 $7.402.97 $6,884.52 $6,358.04 $5,823.41 Time of default $5,280.51 $4,729.20 $4,169.36 $3,600.85 $3,023.53 $2,437.27 $1,841.93 $1,237.37 $623.44 $0.00 16 $250.00 20 YMMV issues a 11 year $17,000,000 face value bond with a coupon of 3.250%. The yield rate is j(2) = 4.750%. YMMV will also make semi-annual deposits in a sinking fund that pays r(52) = 3.823%. Build a spreadsheet showing the bond amortization table and the account balance for the sinking fund. How much are the sinking fund deposits? How much will the bondholders lose if YMMV defaults after 6 years? A B D E F G H I J K L M N 1 Face Value $10,000.00 Sinking Fund 2 3 4 5 Maturity (yrs) Maturity (prd) 10 20 Nominal Rate Comp Freq 3.00% 365 Coupon Coupon Freq Eff rate per prdr 5.00% 2 2.50% PUT EQUATION HERE Pmt Freq Eff Rate per pmt prd Num SF pmts Pmt Amt 2 1.51124% 20 $431.98 6 7 8 9 10 11 12 13 14 15 PUT EQUATION HERE Yield Comp Eff yield per prd i 4.00% 2 2.00% PUT EQUATION HERE 16 Time Amortization of Premium/Discount Price (t) Premium / Discount Time (sinking Account fund pmt prd) Balance Loss on Default 17 Pmt Amt Interest Amt (in coupon periods) PUT EQUATION PUT EQUATION HERE HERE 18 PUT EQUATION HERE PUT EQUATION HERE PUT EQUATION HERE 0 1 2 3 4 5 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $250.00 $216.35 $215.68 $214.99 $214.29 $213.58 $212.85 $212.11 $211.35 $210.58 $209.79 $208.98 $208.16 $207.33 $206.47 $205.60 $204.71 $203.81 $202.88 $201.94 $200.98 $33.65 $34.32 $35.01 $35.71 $36.42 $37.15 $37.89 $38.65 $39.42 $40.21 $41.02 $41.84 $42.67 $43.53 $44.40 $45.29 $46.19 $47.12 $48.06 $49.02 $10,817.57 $10,783.92 $10,749.60 $10,714.59 $10,678.89 $10.642.46 $10,605.31 $10,567.42 $10,528.77 $10,489.34 $10,449.13 $10,408.11 $10,366.27 $10,323.60 $10.280.07 $10,235.67 $10,190.39 $10,144.19 $10,097.08 $10,049.02 $10,000.00 $817.57 $783.92 $749.60 $714.59 $678.89 $642.46 $605.31 $567.42 $528.77 $489.34 $449.13 $408.11 $366.27 $323.60 $280.07 $235.67 $190.39 $144.19 $97.08 $49.02 $0.00 GEBEDOO VOLWNO $0.00 $431.98 $870.49 $1,315.62 $1,767.48 $2,226.17 $2,691.79 $3,164.45 $3,644.25 $4,131.30 $4,625.72 $5,127.60 $5,637.07 $6,154.24 $6,679.22 $7,212.14 $7,753.11 $8,302.26 $8,859.71 $9,425.58 $10,000.00 $10,817.57 $10,351.94 $9,879.12 $9,398.97 $8,911.40 $8,416.29 $7,913.52 $7.402.97 $6,884.52 $6,358.04 $5,823.41 Time of default $5,280.51 $4,729.20 $4,169.36 $3,600.85 $3,023.53 $2,437.27 $1,841.93 $1,237.37 $623.44 $0.00 16 $250.00 20