Instruction: please read carefully

·  The purposes of the excel cases are to get you familiar with financial functions in Excel and to help you understand more about the lectures with practice problems. Therefore, it is expected that you use as many Excel functions as you can, try to get Excel to do all calculations

·  You can either do the case individually or work in a group of no more than 2 people.

·  You should submit a hard copy of the case and send me the excel file via email.

·  Late cases will not be graded.

·  You are required to fill out the yellow blank space in the excel cases.

First, you have to do the following problems using a financial calculator. Once you have done that, you use Excel to do the case and compare the results.

Problem

A 10 year, 12 percent semiannual coupon bond with a par value of $1000 may be called in 4 years at a call price of $1060. The bond sells for $1,100 (assume the bond has just been issued)

a.  What is the bond’s yield to maturity

b.  What is the bond’s current yield

c.  What is the bond’s capital gain or loss yield

d.  What is the bond’s yield to call

Now, go back to the excel case and answer all the questions

Problem a

·  Because the bond is semiannual, so periods to maturity has to equal number of years to maturity times periods per year.

·  Similarly to periods till callable.

·  You have to calculate the yield to maturity (YTM). To do that, use the rate function in Excel

Either you click on function icon in Tool Bar or go to Insert in the top bar, choose function. The function box will come up, choose Financial in Function Category and choose RATE in function name

RATE function has the following form

RATE(Nper, pmt, pv, fv, type, guess). They are all explained in detail in the function box

You don’t have to worry about type and guess, just leave them blank. Also remember pv have to have a negative sign in front. Besides that, the rate you just calculate is the rate for 6 months, not a year, therefore to get YTM, you need to multiply rate of 6 months with 2.

For example, cell b29 = RATE(c20,c23,-c24,c22)*c19

Problem b and c

Using the formulas given, calculate current yield and capital gain/loss

Problem d

Using the same rate function to calculate yield to call. However, remember to change the input corresponding to call data, i.e., Nper in this case is the period till callable, fv in this case is not the par value, it is the call price. Pmt and pv are still the same.

Problem e

First, using PV function in Excel to calculate present value of bond without call and with call.

PV(rate,nper,pmt,fv,type)

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmtis the payment made each period and cannot change over the life of the annuity.

Fvis the future value,

Type is the number 0 or 1 and indicates when payments are due.

Type = 0 or omitted / : At the end of the period
Type = 1 / : At the beginning of the period

Usually, the bond pay interests at the end of period so type = 0 or omitted (i.e., no need to consider type)

For example, to calculate value of bond if not called

Cell d65 = PV(d64/2,c20,-c23,-c22)

Cell c23 and c22 is the par value and payment period, they have the opposite sign with the present value, that is why you have to put negative sign in front of them

Similarly, calculate the value of bond if called.

After that, you do the sensitivity analysis meaning how the bond value changes when interest rate changes.

The column actual value, considering call likelihood ask you to find out the value of bond considering call provision. You call the bond only if the rate < coupon rate (12%).

You have to use the if function

If function has the form If(logical_test, value if true, value if false). Look at the example I did and follow it. You have to do for each value of rate r, from 0% to 20%. Make sure you input the function for each cell separately,

You need to draw a graph, x-axis is the rate r, y-axis is the value of the bond (call, not called, actual value considering the call likelihood). Therefore, you will have 3 lines in the graph.

Problem f

Input the information in the blank yellow sheets. The format of date should be the same format as given in the problem f.

Redemption is the par value if the bond is not called, is the call price if the bond is called

Basis is the type of day count basis to use. In the US, basis is usually = 0, so put 0 in cell D128

After inputting all the information, use the YIELD function to fill out cell C133 and C157