I just need help on Q3-a and Q3-b on yellow boxes
G H K L M N O P Q B 1 P1-Q3: - D E Do not change the format Note: Case Study 3 Instruction asks you to complete elther Q3-a or 03-6. So, you can complete 03 a and Q3-c or 23-band 23-c. The total score is 5 points. If you complete 03-8 and 23-c, there are 28 cells that you need to input correct answers. If you complete 03-band 93-c, there are 29 cells that you need to input correct answers. The rubric for grade is posted on the Blackboard and is available under "Check Grades." $80.000.000 30 7 8.00% 8.00% Capital to be raised: Number of years to maturity call the bonds in how many years: Coupon bond YTM (Yield To Maturityl: Annual coupon rate Coupon per year: Par value for Face value): Zero coupon bond: YTM (Yleld To Maturity): Annual coupon rate: Face (Par) value: Tax rate: $1,000 8.00% 0.009 $1,000 35.00 Q3-c 2 3 4 5 6 7. 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 3B 39 40 41 42 43 44 1/1/2020 1/1/2027 1/1/2050 8.00% 2.00% Q3-a Make-whole Call Price Settlement date: Call date: Maturity date: Annual coupon rate: YTM (Yield To Maturity): Treasury rate on the call date: Make-whole call rate: Face value (as% of par) Coupons per year Make-whole Call Price: (% of patl... Make whole Call Price in $: ******** ** Make-whole Call Price 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 8.00% 8.00% 8.00% 8.00% B.0096 8.0096 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 0.00% 8.00% 5.50% 6.00% 6.5096 7.00% 7.50%! 8.00% 8.50%! 9.00% Q3-b Make-whole Call Price Settlement date: 1/1/2020 Call date: 1/1/2027 Maturity date 1/1/2050 Number of years to maturity 23 Annual coupon rate: 8.00% Annual coupon PMT: YTM (Yield To Maturity: B.00% ***** Treasury rate on the call date; 5.00 Make-whole call rate: Face value in s $1.000.00 Coupons per year: Make-whole Call Price in $: Settlement date: Call date Maturity date Annual coupon rate: YTM (Yield To Maturity): Treasury rate on the call date: Make-whole call rate: Face value (as at par) Coupons per year: Make-whole Call Price: % of par) Make whole Call Price in $: 5.00% 100 100 100 23 100 2 100 2 100 2 100 2 100 2 100 2 Make-whole Call Price in $: $1 $1 $1 Part 1-Q3: Requirements (5 points: a. Use the template (Case Study 3, spreadsheet: P1-Q3) posted on the Blackboard to complete CS3. b. Read the following article: Make-Whole Call Provisions" C. Complete either "Q3-a" or "Q3-b, which asks you to find the call price of the bond if East Coast calls the bonds in seven years. For Q3-a, you can use the Excel function to find the make-whole call price with the given treasury rate on the call date. Use the Excel formula to complete this task. Formula: =PRICE(settlement, maturity, rate, yld, redemption, frequency,[basis])" Here, the settlement date is Call date because the issuer buys back the bond on the call date. Here, the "Make-whole call rate" is used for the "yld" in the excel function. O o For Q3-b, you can use the equation to find the make-whole call price with the given treasury rate on the call date. Use the formula listed below Bond price Present value of the coupons + Present value of the face value 1 Face Value x (1 + r) (1+r) The bonds in this case study pay semi-annual coupon. So, your equation above must be modified as follow. Coupon PMT xx(1-a+7) r Bond price Present value of the coupons + Present value of the face value 1 1 ( 1077) ( + *+ Coupon PMT Face Value x 2 (1+) (1+5)*2 Here, T is the number of periods from the call date to the maturity date. If the coupon payment is paid semi-annually and the number of years between the call and maturity dates is 18, T is 36 = 18*2. Here, the Make-whole call rate is used for the yld in the excel function. d. Complete Q3-c Find the make-whole call price by using various expected treasury rates (from 5.5% to 9.0%) on the call date gi in the spreadsheet. The make-whole call rate is equal the treasury rates on the call date plus 0.5%. Using the Excel formula can save time to complete this task. Complete the graph given in the spreadsheet (Once you fill up all the boxes, the graph will appear.) G H K L M N O P Q B 1 P1-Q3: - D E Do not change the format Note: Case Study 3 Instruction asks you to complete elther Q3-a or 03-6. So, you can complete 03 a and Q3-c or 23-band 23-c. The total score is 5 points. If you complete 03-8 and 23-c, there are 28 cells that you need to input correct answers. If you complete 03-band 93-c, there are 29 cells that you need to input correct answers. The rubric for grade is posted on the Blackboard and is available under "Check Grades." $80.000.000 30 7 8.00% 8.00% Capital to be raised: Number of years to maturity call the bonds in how many years: Coupon bond YTM (Yield To Maturityl: Annual coupon rate Coupon per year: Par value for Face value): Zero coupon bond: YTM (Yleld To Maturity): Annual coupon rate: Face (Par) value: Tax rate: $1,000 8.00% 0.009 $1,000 35.00 Q3-c 2 3 4 5 6 7. 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 3B 39 40 41 42 43 44 1/1/2020 1/1/2027 1/1/2050 8.00% 2.00% Q3-a Make-whole Call Price Settlement date: Call date: Maturity date: Annual coupon rate: YTM (Yield To Maturity): Treasury rate on the call date: Make-whole call rate: Face value (as% of par) Coupons per year Make-whole Call Price: (% of patl... Make whole Call Price in $: ******** ** Make-whole Call Price 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2020 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2027 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 1/1/2050 8.00% 8.00% 8.00% 8.00% B.0096 8.0096 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 8.00% 0.00% 8.00% 5.50% 6.00% 6.5096 7.00% 7.50%! 8.00% 8.50%! 9.00% Q3-b Make-whole Call Price Settlement date: 1/1/2020 Call date: 1/1/2027 Maturity date 1/1/2050 Number of years to maturity 23 Annual coupon rate: 8.00% Annual coupon PMT: YTM (Yield To Maturity: B.00% ***** Treasury rate on the call date; 5.00 Make-whole call rate: Face value in s $1.000.00 Coupons per year: Make-whole Call Price in $: Settlement date: Call date Maturity date Annual coupon rate: YTM (Yield To Maturity): Treasury rate on the call date: Make-whole call rate: Face value (as at par) Coupons per year: Make-whole Call Price: % of par) Make whole Call Price in $: 5.00% 100 100 100 23 100 2 100 2 100 2 100 2 100 2 100 2 Make-whole Call Price in $: $1 $1 $1 Part 1-Q3: Requirements (5 points: a. Use the template (Case Study 3, spreadsheet: P1-Q3) posted on the Blackboard to complete CS3. b. Read the following article: Make-Whole Call Provisions" C. Complete either "Q3-a" or "Q3-b, which asks you to find the call price of the bond if East Coast calls the bonds in seven years. For Q3-a, you can use the Excel function to find the make-whole call price with the given treasury rate on the call date. Use the Excel formula to complete this task. Formula: =PRICE(settlement, maturity, rate, yld, redemption, frequency,[basis])" Here, the settlement date is Call date because the issuer buys back the bond on the call date. Here, the "Make-whole call rate" is used for the "yld" in the excel function. O o For Q3-b, you can use the equation to find the make-whole call price with the given treasury rate on the call date. Use the formula listed below Bond price Present value of the coupons + Present value of the face value 1 Face Value x (1 + r) (1+r) The bonds in this case study pay semi-annual coupon. So, your equation above must be modified as follow. Coupon PMT xx(1-a+7) r Bond price Present value of the coupons + Present value of the face value 1 1 ( 1077) ( + *+ Coupon PMT Face Value x 2 (1+) (1+5)*2 Here, T is the number of periods from the call date to the maturity date. If the coupon payment is paid semi-annually and the number of years between the call and maturity dates is 18, T is 36 = 18*2. Here, the Make-whole call rate is used for the yld in the excel function. d. Complete Q3-c Find the make-whole call price by using various expected treasury rates (from 5.5% to 9.0%) on the call date gi in the spreadsheet. The make-whole call rate is equal the treasury rates on the call date plus 0.5%. Using the Excel formula can save time to complete this task. Complete the graph given in the spreadsheet (Once you fill up all the boxes, the graph will appear.)
