Question

Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. 1) a. What is the price of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.75% and is yielding 5.25%? b. What is the YTM of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.75% and price of 973.47? 2) Coffee shop chain Java the Hut just paid a $2.70 dividend. There are two possible pricing scenarios: a. Scenario 1: You expect the stock will grow at 7% per year forever. Assuming 13% required return, what is the value of Java's stock? b. Scenario 2: For the next two years, you think it will grow by 12% a year, getting it back to its pre-recession level. After that, you expect it to grow at 5% a year forever. Assuming 13% required return, what is the value Java's stock?

Answer 1

A B с D E H K Q1. b Par value Time (in yrs.) Coupon rate Yield Rate Compounding frequency $1,000.00 4 3.75% 5.25% 2 As it is semiannual compounding Par value Time (in yrs.) Coupon rate $1.000.00 4 3.75% Compounding frequency 2 As it is semiannual compounding Periods (N) Periodic Coupon rate (R) Periodic Yield rate (Y) Redemption Value (R.V.) Periodic Coupon amount 8 1.8750% 2.6250% $1,000.00 $18.75 Periods (N) Periodic Coupon rate (R) Price (P) Redemption Value Periodic Coupon amount 8 1.8750% $973.47 $1,000.00 $18.75 Present value of Coupon amount Present value of Redemption amount $ 133.73 $812.78 2 Q1. a 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 Price of bond $ 946.51 Since there are 8 periods therefore we create a table having 9 rows. Period Value 0 -973.47 1 $18.75 2 $18.75 3 $18.75 4 $18.75 5 $18.75 6 $18.75 7 $18.75 8 $18.75 9 $1,018.75 IRR 2.203% YTM 4.40696

B с D E G H 1 J K. Q2. b Growth rate (G) Required Rate (R) Dividend just paid (D) 7.00% 13.00% $2.70 Growth rate (G) Required Rate (R) Dividend just paid (D) Growth from Year 3 (9) 12.00% 13.00% $2.70 5.00% Value of stock $48.15 Year $3.02 Value of stock=D, R-G (1+G) 1D 2D 3D $3.39 $3.56 A 30 31 32 Q2. a 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 Value of stock at end of Year 2 $46.68 (1+G2 0 0 Value of stock today $41.88 (1+G Value of stock=D (1+3) ? +D ° (1+R) (R-g)(1+R)?) D D D Value of stock (1+R) (1+R) (R-9) 1+R) ?) (1+R22+D. (1+G) - + + 52 53 54 55 56

A B с D E G H Н. 1 K LA Q1. a Par value Time (in yrs.) Coupon rate Yield Rate Compounding frequency $1,000.00 01. b 4 3.75% 5.25% 2 As it is semiannual compounding Par value Time (in yrs.) Coupon rate $1,000.00 4 3.75% Compounding frequency 2 As it is semiannual compounding =C3 C6 =C4/C6 Periods (N) Periodic Coupon rate (R) Periodic Yield rate (Y) Redemption Value (R.V.) Periodic Coupon amount -C5/C6 Periods (N) Periodic Coupon rate (R) Price (P) Redemption Value Periodic Coupon amount =13*16 =14/16 $973.47 =12 =111*19 =C2 =C11C9 Present value of Coupon amount Present value of Redemption amount 1 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 --PV(C10, C8, C12,0,0) =C11/(1+C10) C8 Price of bond C14+C15 Since there are 8 periods therefore we create a table having 9 rows. Period Value 0 =-110 1 =$I$12 2 =$I$12 3 =$I$12 4 =$I$12 5 5 =$1$12 6 =$I$12 7 =$I$12 8 =$I$12 9 =112+111 IRR =IRR(116:125) YTM =127*16

B с D E F G H Q2. b Growth rate (G) Required Rate (R) Dividend just paid (D) 7.00% 13.00% $2.70 Growth rate (G) Required Rate (R) Dividend just paid (D) Growth from Year 3 (9) 12.00% 13.00% $2.70 5.00% Value of stock =C34*(1+C32)(C33-C32) Year 1D 2D 3D (1+G (R-G =$1$34*(1+$1$32) G37 =$1$34*(1+$1$32) G38 =138*(1+135) Value of stock=D A 30 31 32 Q2. a 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Value of stock at end of Year 2 =139*(1+135)(133-135) Value of stock today =137/(1+133)+138/(1+133)^2+141/(1+133)^2 Value of stock=D (1+G (1+9) -D (1+R) (1+R (R-9) 1+R)?) D D D (1+G2 +D. (1+G? Value of stock= 1+) * (1+R)2 + ((R-9)(1+R)*)