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)     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
	Particulars	Excel Formulae Code
A	Par Value of Bond	FV	1000	Given
B	Coupon Rate		3.75%	Given
C	Maturity		4	Given
D	Semiannually (12/6 = 2)		2
E=C*D	Period	NPER	8
F = A*B/D	Semiannual Interest	PMT	18.75
G	Price of Bond	PV	973.47	Given
H	Semi-Annual Bond Yield	Rate	2.24%	USE FORMULA-->		=RATE(nper,pmt,pv,fv,0)
I=H*D	Annual Bond Yield	Rate*2	4.48%
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?
A	Growth Rate	0.07	Given
B	Required Return	0.13	Given
C	Dividend Paid (Do)	2.7	Given
D= C*(1+A)/(B-A)	Value of Java's Stock	48.15	USE FORMULA-->		=D32*(1+D30)/(D31-D30)

Please Discuss in case of Doubt

Best of Luck. God Bless

Please Rate Well