finance hw

ps_4.xlsx

Home >Business & Finance homework help >finance hw

Directions

Finance 3910			Spring 2015
Problem Set 4
Directions: Rename this file by entitling it "Name_PS_4.xls" Example --> .xls. Complete all questions found below. Assessment will be based on the following:
	· The correctness of the answer or solution.
	· Whether the solution answers the question(s) asked or the question is completed as instructed
	· Correctness of the formulas used in completing each question
	· Flow of the Sheet (do all formulas flow through the sheet correctly, in other words,
	minimize the number of direct numerical inputs required by the user)
	· Correctly label the sheet
	· Presentation (Do not create sloppy sheets)
	· Include assumptions (only when needed)
	· Follows all directions
Make sure your exam prints correctly on 8.5 x 11.0” paper either portrait or landscaped. Upon completion (after scanning for viruses), upload your file into the dropbox in D2L no later than 12:30 p.m. (day class) and 5:30 p.m. (night class) on Wedesday April 8th. DO NOT ENLARGE THE COLUMNS. Turn in a printed and staple hard copy during class the same day.
		Student Name:
		Replace with Student's Name
~ DO NOT USE ANY OF THE BOOK MACROS (i.e. THE FAME FORMULAS)
1. On the tab "Problem 1" work number 1 on page 259.
2. On the tab "Problem 2" work number 2 on page 300.
~ for part d --> add a calculation of convexity.
~ for part e --> I've customized it a bit, so follow the directions in the worksheet and not the book.
3. On the tab "Problem 3" work the problem presented.

Problem 1

1. Using the shells below, work number 2 on page 259. Feel free to use all space necessary up through column J (i.e. do not use column N) to answer this question. I've added some parts to the problem that are not in the book. Yellow cells should be considered extra work area.
Single-Stage Growth Data
Stock Price	$ 20.00
D0	$ 0.60
Growth Rate	5%
Required Return	9%
Value
Two-Stage Growth Data
Stock Price	$ 20.00
D0	$ 0.60
Growth Rate 1	15%
Growth Rate 2	5%
Rapid Growth Time	3.00 years
Required Return	9%
2-stage Value
Three-Stage Growth Data
Stock Price	$ 20.00
D0	$ 0.60
Growth Rate 1	15%
Growth Rate 2	5%
Rapid Growth Time	3.00 years
Transition Period	5.00 years
Required Return	9%
Three-Stage Value
H-Model Value
Valuation
Single-Stage	0	if statement here
Two-Stage	0	if statement here
Three-Stage	0	if statement here
H-Model	0	if statement here
Data Table within this box -->
XY Scatter over this box -->

Problem 2

1. Using the information below, work number 2 on page 300. Feel free to use all space necessary up through column M (i.e. do not use column N) to answer this question. I've added some parts to the problem that are not in the book. Yellow cells should be considered extra work area.
		Bond A	Bond B	Bond C
Settlement Date						Make sure to input any values as a percentage of par and format any percentages appropriately.
Maturity Date
Coupon Rate
Market Price
Face Value
Required Return
Part a					<-- intrinsic value
					<-- under/overvalued?
					<-- interest
Part b					<-- current yield
	Is the CY the total return that you would earn each year? Explain.
	If you were on a fixed income, would you care about this number?
Part c	YTM			<-- yield to maturity
	How do the YTMs compare to the current yields of the bonds? What does this indicate about these bonds?
Part d	Duration				<-- using Excel function
	M Dur				<-- using Excel function
	M Dur				<-- using approximation formula
	Convexity				<-- using the full formula
	Convexity				<-- using approximation formula or the full formula
Part e	Work these as an alternative to the Part e in the book.
Based on your modified duration calculation in part d, what is the approximate price change for each bond if yields increase by 75 basis points?
	Bond A			Bond B			Bond C
Based on your modified duration calculation in part d, what is the approximate price change for each bond if yields decrease by 25 basis points?
	Bond A			Bond B			Bond C
Based on your convexity calculation in part d, what is the approximate price change for each bond if yields increase by 1%?
	Bond A			Bond B			Bond C
Based on your convexity calculation in part d, what is the approximate price change for Bonds A & B if yields decrease by 2%?

	Bond A			Bond B			Bond C
Which method (duration or convexity) in part e is the more accurate of the two sets of estimates and why?

Problem 3

2. Locate an active corporate bond of your choice with at least 12 years to maturity and complete the following. Include a print out or screen shot of your bonds info in the packet you submit. You may use a Bloomberg Terminal or the FINRA Advanced Bond Search tool to locate bond. http://finra-markets.morningstar.com/BondCenter/Screener.jsp?type=advanced
Use the day of your download as the settlement date.
		Bond
Settlement Date
Maturity Date
Coupon Rate
Market Price
Face Value
Required Return
Part a: What was the closing price of the bond on the settlement date?
Part b: Calculate the current yield of the bond on the settlement date?
Part c: Calculate the yield to maturity of the bond on the settlement date?
Part d: What was the duration, modified duration and convexity of the bond? For Mod Duration and Covexity use either the full or approximation formulas.
	Duration
	Modified Duration
	Convexity
THE END