Question

Workbook Views Show Zoom B15 G A B С D E F 1 On January 1, Ruiz Company issued bonds as follows: 2 Face Amount $ 500,000 3 Number of Years: 15 4 Stated Interest Rate: 8% 5 Interest payments per year 2 6 (Note: the bonds pay interest semi-annually.) 7 8 Required: 9 1) Given the different market interest rates below, calculate the following items. 10 Calculate the bond selling price USING THE EXCEL PV FUNCTION (fx). Note: Ente 11 function arguments as cell references. 12 13 a) Market Interest Rate: 9% 14 Semi-annual interest payment: $ 20,000 15 Bond Selling Price: 16 17 b) Market Interest Rate: 6.0% 18 Semi-annual interest payment: $ 20,000 19 Bond Selling Price: 20 21 2. Use the Excel IF function to answer either "Premium" or "Discount" to the following items. 22 23 The bond in (a) sold at a: 24 The bond in (b) sold at a: 25 26 7

Answer 1

Bond value is the present value of all the future coupon cash inflow plus inflow on redemption of bond at the market rate of discount

Please find the Image attached

А G 4 2 B C D E F 1 Face Amount 500000 2 Number of Year 15 3 Stated Interest Rate 0.08 Interest Payment per year 5 6 Required 7 8 9 10 11 12 13 a) Market Interest Rate 0.09 14 Semiannully Interest =(+$B$1$B$3/$B$4) 15 Burid Selling Price --PV(B13/$B$4,$B$2* $B$4,-B14,-$B$1) 16 17 Market Interest Rate 0.06 18 Semiannully Interest =(+$B$1$B$3/$B$4) 19 Bond Selling Price =+PV(B17/$B$4,$B$2*$B$4,-B18,-$B$1) 20 21 22 2. Use the Excel If Function 23 24 The bond in (a) sold at a =+IF(B15>B1,"Premium","Discount") 25 The bond in (b) sold at a =+IF(B19>B1,"Premium","Discount") 26 27 You can put the s, in D1, D2,D3 and 14 in formula cell reference to make it absolute reference, so that when the formula is copy to 28 next, it will not change, or else you can avoid it and make new formula to calculate value of bond in B19) 29 30 NPER in excel is for number of year , Market rate is divided by 2 as it is paid semianually, and number of year is multiply by 2 interest 31 32 Coupon and Redemption amount are to be taken negative as per formula for Future Value ie FV 33 34 35 36 37 Sheet1 Sheet2 Sheet3 Ready

B C D E F G А 1 Face Amount 2 Number of Year 3 Stated Interest Rate 4 Interest Payment per year 5 6 Required 7 $500,000 15 8% 2 8 9 10 11 12 13 a) Market Interest Rate 9% 14 Semiannully Interest 20000 15 Bond Selling Price $459,277.78 16 17 Market Interest Rate 6% 18 Semiannully Interest 20000 19 Bond Selling Price $598,002.21 20 21 22 2. Use the Excel If Function 23 24 The bond in (a) sold at a Discount 25 The bond in (b) sold at a Premium 26 27 You can put the $, in B1, B2,B3 and 14 in Formula cell reference to make it absolute reference, so that when the formula is copy to next, it will not change 28 or else you can avoid it and make new formula to calculate value of bond in B19) 29 30 NPER in excel is for number of year , Market rate is divided by 2 as it is paid semianually, and number of year is multiply by 2 interest payment is twice in 31 32 Coupon and Redemption amount are to be taken negative as per formula for Future Value ie FV 33 34 35 36 37 Sheet1 Sheet2 Sheet3