Corporate Finance Online Exam Due Tomorrow SEE ATTACHED

Main Model

Worksheet for Chapter 4 BOC Questions							1/29/15
We use this model to illustrate some points about the bond valuation.
Illustrative Data: Given the following data, find the bond's coupon rate, current yield, expected capital
gains yield for coming year, YTM, and YTC.
Today's date:			1/29/15	Typically, we show inputs in blue.
Maturity:			1/6/2033	Enter dates with quote mark first.
First call:			1/6/2023
Coupon rate:			8.5%	We often talk about $1,000 par bonds par value, but
Current price:			93	they can be sold in any denomimation.Therefore, in
Call price:			108	practice they are quoted as a % of par. Thus, 93
Maturity value:			100	means the bonds sell at 93% of par, and 108 means
Payments per year:			2	they can be called with an 8% call premium.
Current yield:			9.14% Gene Brigham: This is a discount bond. If interest rates remain constant, then its price will rise over time and be 100 just before it matures. The interest payment will remain fixed. Therefore, the current yield will decline over time, and the expected capital gains yield will increase.	= Annual coupon / Current price
Expected capital gains yield:			0.17%	This is just the YTM - Current yield.
YTM:			9.31%	These are more complicated. They are calculated
YTC:			10.49%	below.
YTM:	9.31%	Click fx > Financial > Yield > OK to bring up the Yield menu. Then
		point and click to fill in the menu cells. You must scroll down to
		complete the menu, and you can leave "basis" blank. We assume
		settlement is today, though it is normally 4 days after the trade date.
		The completed dialog box is shown to the right. Note that you must scroll
		down to get to "frequency" to complete the dialog box. Also, leave the
		"basis" box blank and Excel will use as the default a standard 360 day year.
YTC:	10.49%	We use the Yield function again, but use the call date for the maturity and
		call price for the redemption price. The completed dialog box is to the right.
If interest rates remain at the current level, then the bonds will not be called. New bonds would cost
the company about 9.3%, so it would not call 8.5% bonds to replace them with 9.3% bonds.
Of course, we do not know that interest rates will remain at current levels. Indeed, there is always a
chance that interest rates will fall from whatever level they are at, and if rates fall enough, then the
bonds will be called.
If you owned the bonds, you should not want to have them called. True, you would then earn the YTC,
which is higher than the YTM, but you would get your money back and then have to reinvest at a
lower rate. Your average earned rate of return out to the maturity date would be less than the YTM.
Remember, companies only call bonds if it is advantageous to them, which means disadvantageous to
the bondholder.
Interest rate risk and reinvestment rate risk
The following analysis can be used to illustrate interest rate and reinvestment rate risk.
Assume three bonds, all non-callable, selling at approximately par, and all having an 8.5% coupon.
The bonds mature in 1, 10, and 50 years. Note that bonds never mature in exactly 1 year etc. except
on their issue date and once per year thereafter.We used the datevalue function (fx > Dates&Times >
Datevalue) to find the years as shown below. Set up a price formula, then use a data table to analyze
the sensitivities, and then graph the results.
Coupon (Rate)		8.5%		Par (Redemption)		100
Market rate (Yield)		8.5%		Frequency		2
Settlement (Today)		1/29/15
	Yrs to Mat* Gene Brigham: Excel's date function feature uses 1900 as time 0 and then adds one day (365 or 366 per year) going forward. If you specify a given date, Excel will give you a number. You can subtract that number from the number for some other date to get the number of days between two dates. You can divide by 365 to get the number of years (or fraction of a year). We show the dialog boxes for the maturity and settlement dates below.			Price			42,398
1-Year	1.00	1/29/16		100.00	Click fx > Financial > Price > OK.		42,033
10-Year	10.01	1/28/25		100.00	then fill in the menu items.		365
50-Year	50.03	1/28/65		100.00			1.00
The bonds' prices are found with Excel's Price function. Click fx > Financial > Price > OK and
Then fill in the dialog box as shown below for the bond maturing in 1 year.
The bonds' prices are not exactly 100 because they all have less than an even number of years to maturity.
Now that we have the bond price formulas, we can find the values of the three bonds at various interest
rates. Note that when the going interest rate is equal to the coupon rate, the bonds all sell at
approximately their par values.
Row:C45		Maturity
Market	1-Year	10-Year	30-Year
Rate	100.00	100.00	100.00
5.0%	103.37	127.27	164.07
8.5%	100.00	100.00	100.00
20.0%	90.02	51.05	42.50
Note on making data tables:
1. Type in the labels (words) as shown above.
2. Enter the market rates as shown.
3. Put the pointer on B92, type =, then click on E61, where the 1-year bond's price is calculated.
Then fill in cells C92 and D92 similarly.
4. Market rate is the variable that will change. The values we use are shown in a column.
This variable enters the calculations in C58, which is the "Input Cell."
5. Now highlight the "active" cells in the data table, which is the range A92:D95.
6. Click Data > What if Analysis > Data Table to get a menu. Sometimes data tables have 2 inputs, one shown
on a row and one in a column. However, this data table has just one input, and it is in a column. So, leave "Row
input cell" blank, go down to "Column input cell," and type C58. A picture shown to the right.
7. When you click OK, the data table will be filled out, and it will show the price of each of the bonds at the different market interest rates.
8. You can make a graph to show the sensitivity of the bonds to changes in rates. The graph above makes
it clear that the longer the maturity, the greater the price sensitivity to interest rate changes.
Data tables can deal with one input and one or more outputs. We just created a 1-input, 3-output
table. We can also construct a 2-input, 1-output table, using maturity and coupon rate as the inputs
and % change from beginning price as the output. We will analyze bonds with maturities ranging
from 1 ot 50 years and coupon rates of 0%, 8.5%, and 15%.
Coupon (Rate)		8.5%		Par (Redemption)		100
Market rate (Yield)		8.5%		Frequency		2
Today (Settlement)		4203300.0%		Maturity		60295
Price:		100.000	Click fx > Financial > Price > OK. Then fill in the menu items.
		Bond Prices at Different Market Yields and Maturities
			Maturity (converted to Datevalues)
	Market Yld	1-Year	10-Year	50-Year
	100.00	1/29/16	1/28/25	1/28/65
	5.0%	103.373	127.3	164.1
	8.5%	100.000	100.0	100.0
	15.0%	94.164	66.9	56.7
	Note: These are for non-callable bonds.
		Percentage Change from r = 8.5% Price
	Market		Coupon Rate
	Yield	0.0%	8.5%	20.0%
	5.0%	3.4%	27.3%	64.1%
	8.5%	0.0%	0.0%	0.0%
	20.0%	-5.8%	-33.1%	-43.3%
REINVESTMENT RATE RISK
This is the risk that investment income from a bond portfolio will decline due to a decrease
in interest rates. If we assume that an investor has a $1 million portfolio when rates are
8.5%, we could see how income would change with rates under different scenarios. It
would be easy to analyze the situation if we just dealt with a 1-Year bond and a 50-Year
bond. However, it would require more programming.than it's worth todo a through
analysis.
DURATION
We generally do not go into duration in the financial management course because
it is studied in detail in the investments course. Still, here is an Excel analysis of duration.
Coupon (Rate)		8.5%		Par (Redemption)		100
Market rate (Yield)		8.5%		Frequency		2
Today (Settlement)		4203300.0%		Maturity		60295	50 year bond
Zero Coupon bond, same data except coupon =				0.0%
Duration, 50-year Zero:		50.00	Initial price, Zero:			$1.56
Duration, 50-year coupon		12.07	Initial price, Coupon bond:			$100.00
Duration is a function of the coupon rate, the current market rate, and the time to maturity. Here
is a data table for the 8.5% and the zero coupon bonds, changing the market rate:
	Duration
	Coupon Rates
Market	Zero	Coupon
Rate	50.00	12.07	Duration
5.0%	50.00	17.63	The zero coupon bond's duration is constant, but
8.5%	50.00	12.07	the 8.5% coupon bond's duration falls as rates rise.
20.0%	50.00	5.50
At a current market rate of 8.5%, the 8.5% bond's duration is 11.97 years vs. 49.89 years for the
zero, so the zero's duration is about 4.2 times larger. This indicates that the zero is about 4 times
more sensitive to interest rate change as the 8.5% coupon bond for changes close to 8.5%. As the market
rate gets further and further away from the current rate, then the duration changes, and so does the
relative sensitivity. Still, duration is a better indicator of price sensitivity, hence interest rate
risk, than is maturity. Both the zero and the coupon bond have a 50 year maturity, but the zero is far more
sensitive to interest rate changes. The duration picks up this sensitivity, but maturity does not.
							End of model

&P of &N

1-Year 0.05 8.5000000000000006E-2 0.2 103.37299226650804 100 90.02066115702479 10-Year 0.05 8.5000000000000006E-2 0.2 127.27488464902638 99.999514715101938 51.050434099649863 50-Year 0.05 8.5000000000000006E-2 0.2 164.07358260459449 99.999514715101981 42.503073378892132

Market Rate

Bond Prices

Price Sensitivity

1-year 0.05 8.5000000000000006E-2 0.2 3.3729922665080458E-2 0 -5.8355868036776615E-2 10-year 0.05 8.5000000000000006E-2 0.2 0.27275502297817966 0 -0.33128511830067198 50-year 0.05 8.5000000000000006E-2 0.2 0.64074378832776491 0 -0.43302553417274481

Market Yield

Percent Change