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. 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?

Answer 1

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
Please Rate Well