Question

# Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use...

 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. You will turn in a complete working Excel spreadsheet with your solution. 1)     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%? What would it be yielding if the price decreased to \$973.47? Assume semiannual compounding for both. 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?

1) Using excel to calculate price of bond.

 A 1 Par Value 1000 2 Coupon Rate 3.75% 3 Number of Years till maturity 4 4 YTM 5.25% 5 Coupon 18.75 (Excel formula=(A1*A2/2) 6 Number of Periods 8 (Excel formula=(A3*2) 7 Price \$946.51 (=PV(A4/2,A6,-A5,-A1)
 A 1 Par Value 1000 2 Coupon Rate 3.50% 3 Number of Years till maturity 4 4 Coupon 17.5 (Excel formula=(A1*A2/2) 5 Number of Periods 8 (Excel formula=(A3*2) 6 Price \$973.47 YTM 4.23% (=2*Rate(A5,A4,-A6,A1)

Max 1 question and its subparts can be solved at a time

Please Discuss in case of Doubt

Best of Luck. God Bless

#### Earn Coins

Coins can be redeemed for fabulous gifts.