cf_assessment_3_template.xlsx

Part 1 – Bonds Valuation Calcs

BUS-FPX3062 Assessment 3 Template
Part 1: Bond Valuation
Solve the following problems and answer the last question. Example problems can be found on the "Bond Valuation Example" tab below. Create appropriate formulas using the supplied values in the corresponding cells so Excel can calculate the answer.
1. Compute the price of a 3.8% coupon bond with 18 years left to maturity and a market interest rate of 7%. Compute the price again if interest payments are paid semi-annually (solve using semi-annual compounding). Par value is $1000. [Annual Compounding Answer] [Semi-annual Compounding Answer]
2. A 6.50% coupon bond with 18 years left to maturity is offered for sale at $1,035.25. What yield to maturity [interest rate] is the bond offering? Assume interest payments are paid semi-annually, and solve using semi-annual compounding. Par value is $1000. [Annual Compounding Answer] [Semi-annual Compounding Answer]
3. You have just paid $1,135.90 for a bond, which has 10 years before it, matures. It pays interest every six months. If you require an 8% return from this bond, what is the coupon rate on this bond? Par value is $1000. [Answer here]
4. A company XYZ issued 30 year bonds with 10% annual coupon rate at their par value of $1000 in 2010. The Bonds had a 7% call premium, with 5 years of call protection. Today XYZ called the bonds. Compute the realized rate of return for an investor who purchased the bonds when they were issued in 2010. Briefly explain why the investor should or should not be happy. [Answer here] [Explanation here]
5. ABC Corporation outstanding bonds have a par value of $1000, 8% coupon and 15 years to maturity and a 10% YTM. What is the bond's price? [Answer here]
6. What does a call provision (call feature) allow (bond) issuers to do and under what circumstances would they do it? [Answer here]

Part 2 – Stocks Valuation Calcs

Part 2: Stock Valuation
Solve the following problems and answer the last question. Create appropriate formulas using the supplied values in the corresponding cells so Excel can calculate the answer. Example problems can be found on the "Stock Valuation Example" tab below. Show your work. ß
1. On March 5, 2013, the Dow Jones Industrial Average set a new high. The index closed at 14,253.77, which was up 125.95 that day. What was the return (in percent) of the stock market that day? [Answer here]
2. Your discount brokerage firm charges $9.50 per stock trade. How much money do you need to buy 300 shares of Time Warner, Inc. (TWX), which trades at 22.62? [Answer here]
3. Financial analysts forecast XYZ company's growth for the future to be a constant 8%. XYZ's recent dividend was $0.88. What is the value of XYZ stock when the required return is 12%? [Answer here]
4. A preferred stock from ABC pays $3.55 in annual dividends. If the required return on the preferred stock is 6.7%, what is the value of the stock? [Answer here]
5. QRST has earnings per share of $1.56 and a P/E ratio of 32.48. What's the stock price? [Answer here]
6. Explain why the S&P 500 Index might be a better measure of stock market performance than the Dow Jones Industrial Average. [Answer here]

Bond Valuation Example

TVM and Bonds Examples
Practice Problem Worksheet Using Excel Formulas
An understanding of bond characteristics and of bond cash flows is necessary to understand the time value of money effects on bonds and their valuation. Accordingly, it is recommended you employ your knowledge of time value of money principles (just studied in Week 2) and you also need to thoroughly read and understand Chapter 7, "Valuing Bonds," from your M: Finance text before reviewing the following problems and solutions.
Bond Valuation
Solving for the Bond Valuation:
Bond Val 1) Here is a simple bond valuation problem:
What is the price of a 8.0% coupon bond with 12 years left to maturity and a current market rate of interest of 6.8%. The correct answer is $1,097.37 determined below. Assume semi-annual interest payments and assume $1,000 par value, unless advised otherwise.
Solution: First, identify this as a bond valuation problem and we are to find the bond's present value. (The bond's present value is requested by such wording as "what is the price" or "compute the price." Inferred in such statements is "what is the price NOW," i.e., what is it's present value.)
Important Notice: Be advised most bonds usually pay interest on a semi-annual basis, assume this for any bond problem in this course unless you are advised otherwise. Accordingly, we will use the standard Excel, financial formula, and we will adjust our input figures to account for semi-annual compounding.
Important Notice: Make the following adjustments to the inputs into the financial formula function to adjust for semi-annual compounding:
1) Adjust the interest rate: The 6.8% annual market rate divided by 2 compounding periods per year equals a 3.4% semi-annual rate. Enter 3.4% as the interest rate.
2) Adjust the number of time periods: In a 12 year annual time period, there are 24 semi-annual time periods (12 x 2 = 24) Enter 24 as the number of periods.
3) Adjust the coupon (interest) payment: The 8.0% coupon equates to an annual coupon interest payment of $80. ($1,000 par value (assume $1,000 par unless otherwise indicated) times the 8.0% coupon rate = $80 annual interest payment. There are two semi-annual periods on a year, so adjust the $80 annual payment to reflect a $40 semi-annual payment. (Enter 40 as the payment.)
4) FV and PV amounts do not require compounding frequency adjustments.
Solution: Next identify the variables to be entered into the formula:
The interest rate: The adjusted market rate of interest = 3.4% (or 0.034 expressed as a decimal)
The number of time periods: The adjusted time periods are N = 24
The annuity payment: The adjusted annuity payment is $40: PMT = $40
The future value: FV = 1,000 (This represents the {future} value of the bond at maturity.)
Solution: Next using the Excel, Formulas, Financial, PV formula that reads as follows:
PV = (Int rate, Number of periods, annuity payment, Future Value)
Solution inputting the numbers into the Excel PV formula in the exact order prescribed by the formula (click on Cell B56 to see the contents of the cell as inputted):
PV = ($1,097.37) (Note that PV is solved showing it as a negative number. This is because of the specific mathematical formula that is used to solve the equation and represents that $1,097.37 must be paid out (an outflow) in order to receive the series of $40 interest payment (inflow) and to receive the $1,000 par value payment (inflow) at maturity. Even though the Excel mathematical equation solves the PV as a negative number, one should simply refer to the PV as $1,097.37 … without reference to it as a negative number.)
Bond Val 2) Here is a bond valuation practice problem:
What is the price of a 7.0 percent coupon bond with 6 years left to maturity and a current market rate of interest of 8.2 percent. Assume interest payments are paid semiannually. The correct answer is $944.01 … determined below.
PV = ($944.01)
Bond Val 3) Here is a bond valuation practice problem for a zero coupon bond:
What is the price of a zero coupon bond that matures in 14 years and the market rate of interest of 6.4 percent. The correct answer is $413.97 … determined below. Assume semi-annual compounding.
Calculate this problem the same as the above two bond valuation problems; however, there is no interest paid throughout the life of the bond (hence its name zero coupon.) So, enter "0" in the payment portion of the formula.
PV = ($413.97)
Bond Yield to Maturity
Solving for Bond Yield to Maturity
BOND YTM 1) Here is a simple bond yield to maturity (YTM) problem:
A 5.0 percent coupon bond with 9 years left to maturity is offered for sale at $952.52. What is this bond's yield to maturity. Assume interest payments are paid semiannually and the par value of the bond is $1,000. The correct yield to maturity answer is 5.68% … determined below.
Solution: First, identify this is a bond yield to maturity problem (this is determined because we are asked to solve for the bond's yield to maturity.)
Solution: Next identify the known variables and adjust for semi-annual compounding:
The number of time periods: The adjusted time periods are N = 18 (9 x2)
The annuity payment: The adjusted coupon payment is ($1,000 x .05 = $50); ($50 / 2) : PMT = $25
The present value: PV = 952.52 (This represents the current price {present value} of the bond.) Enter present value amount as a negative number to differentiate cash outflows from inflows.
The future value: FV = 1,000 (This represents the {future} value of the bond at maturity.)
Solution: Next using the Excel, Formulas, Financial, RATE formula that reads as follows:
Rate = (Number of periods, coupon payment amount, Present Value {enter PV as a negative number to differentiate cash inflows from outflows}, Future Value)
Partial solution inputting the numbers into the Excel RATE formula in the exact order prescribed by the formula (click on Cell C115 AND C116 to see the contents of the cell as inputted):
semi annual RATE 3% You need to adjust the C115 cell to allow showing additional decimals (at least 4 decimal places) as reflected in cell C116.
semi annual RATE 2.84%
WARNING … WE ARE NOT YET FINISHED WITH THIS PROBLEM!
What we have just calculated is a semi-annual rate. Since yield to maturity always means an annual return, we need to multiply the semi-annual rate to adjust for 2 semi-annual time periods per year. (2.84% x 2 = 5.68%) 5.68% reflects the bond's Yield to Maturity (YTM.)
Bond YTM 5.68% (Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 6% and that is not sufficiently exact and would be marked as incorrect.)
BOND YTM 2) Here is a bond yield to maturity (YTM) practice problem:
A 8.5 percent coupon bond with 11 years remaining to maturity is priced for sale at $1,252.52. What is this bond's yield to maturity. Assume interest payments are paid semiannually. The correct yield to maturity answer is 5.42% … determined below.
semi annual RATE 3% You need to adjust the C137 cell to allow showing additional decimals (at least 4 decimal places) as reflected in cell C138.
semi annual RATE 2.71%
What we have just calculated is a semi-annual rate. Since yield to maturity always means an annual return, we need to multiply the semi-annual rate to adjust for 2 semi-annual time periods per year. (2.71% x 2 = 5.42%) 5.42% reflects the bond's Yield to Maturity (YTM.)
Bond YTM 5.42% (Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 5% and that is not sufficiently exact and would be marked as incorrect.)

Stock Valuation Example

Stock Valuation Examples
Practice Problem Worksheet Using Excel Formulas
It is highly recommended you first read and study Chapter 8, "Valuing Stocks," from your M: Finance text before reviewing the following problems and solutions. One must understand the TVM theory before being able to solve the valuation problems.
Stock Market Indexes
Example:
On March 9, 2009, the Dow Jones Industrial Average reached a new low. The index closed at 6,547.05, which was down 79.89 that day. What was the return (in percent) of the stock market that day?
Today's close = 6547.05
Previous day close = 6626.94
Return = (Today's close – Previous day close)/Previous day close
Return = -1.21%
Common Stock Valuation
Constant Growth Model Example
Where P0 is the price today, D1 is the dividend next year, i is the discount rate and g is the growth rate
Financial analysts forecast Safeco Corp.’s (SAF) growth rate for the future to be 8 percent. Safeco’s recent dividend was $0.88. What is the value of Safeco stock when the required return is 12 percent?
Solution: In this problem following variables are given
D0= $1.00
g= 0.06
i= 0.1
First calculate the D1= D0(1 + g)= 1.06
Now, calculate the price P0 = $26.50
Preferred Stock Valuation
Preferred stock pays constant dividend so the growth rate is zero. Constant growth rate model can be applied to preferred stock with growth rate (g) set to zero.
Price = Constant dividend
interest rate
Example:
A preferred stock from Hecla Mining Co. (HLPRB) pays $2.00 in annual dividends. If the required return on the preferred stock is 5 percent, what is the value of the stock?
Dividend = $2.00
Interest rate = 0.05
Price = $2 $40.00
0.05
Price/Earnings (P/E) Ratio
Example
JPMorgan Chase Co. (JPM) has earnings per share of $3.53 and a P/E ratio of 13.81. What is the price of the stock?
EPS = $3.53
P/E ratio = 13.81
Price = $48.75