Interest Rate Risk consider two 30-year maturity - Accounting

ChemistryExplain daily providing Q&A content “#454 Interest Rate Risk consider two 30-year maturity" in Economics, 10 principles of economics, Accounting vs economic profit, Aggregate economics, American institute for economic research, Bachelor of economics

Get the Free Online Chemistry Q&A Questions And Answers with explain. To crack any examinations and Interview tests these Chemistry Questions And Answers are very useful. Here we have uploaded the Free Online Chemistry Questions. Here we are also given the all chemistry topic.

ChemistryExplain team has covered all Topics related to inorganic, organic, physical chemistry, and others So, Prepare these Chemistry Questions and Answers with Explanation Pdf.

For More Chegg Questions

Free Chegg Question

Interest Rate Risk. Consider two 30-year maturity bonds. Bond A has a coupon rate of 4%, while bond B has a coupon rate of 12%. Both bonds pay their coupons semiannually.

a. Construct an Excel spreadsheet showing the prices of each of these bonds for yields to maturity ranging from 2% to 15% at intervals of 1%. Column A should show the yield to maturity (ranging from 2% to 15%), and columns B and C should compute the prices of the two bonds (using Excel’s bond price function) at each interest rate.

b. In columns D and E, compute the percentage difference between the bond price and its value when yield to maturity is 8%.

c. Plot the values in columns D and E as a function of the interest rate. Which bond’s price is proportionally more sensitive to interest rate changes?

d. Can you explain the result you found in part (c)? Hint: Is there any sense in which a bond that pays a high coupon rate has a lower “average” or “effective” maturity than a bond that pays a low coupon rate?

Free Chegg AnswerFor More Chemistry Notes and Helpful Content Subscribe Our YouTube Chanel - Chemistry Explain

Free Chegg Answer

Step 1 of 14

Bond A has 30 years to maturity and pays a coupon of 4%. Bond B also has 30 years maturity but pays coupon of 12%. The prices of the bonds are to be calculated in Excel using Price function of Excel for various YTMs.
Step 2 of 14

Price function in Excel calculates the price of the bond if time to maturity, coupon and YTM is known. Price function in Excel can be called by typing =Price in any cell. Syntax of the function is explained below.

=PRICE ( settlement , maturity , rate , yld , redemption , frequency , basis)

Settlement    : This is the settlement date or date of purchase of the bond. Date should be entered using date function. For example, date of 15^th Feb 2012 should be entered as =date(year,month,day) which would be =date(2012,2,15);

Maturity: This is the maturity date of the bond. Date should be entered using date function. For example, date of 15^th Feb 2042 should be entered as =date(year,month,day) which would be =date(2042,2,15);

Rate: This is the annual coupon rate payable on the bond;

Yld:   This is the yield or YTM of the bond;

Redemption: This is the redemption price or face value of the bond;

Frequency: This is the number of times coupon is paid on the bond;

Basis: This is the day count convention of the bond. If day count convention is not given, select 0 or omit the selection. For this problem, day count is not given and value of basis would be 0.

Various input values for Bond A is explained below. Note that Bond A matures in 30 years. Assume that Settlement date for bond is Feb 15, 2012 and Maturity date is 14 Feb 2042 so as to give 30 years of maturity.

Settlement: For Bond A, date of 15^th Feb 2012 is entered as =date(2012,2,15);

Maturity: For Bond A, date of 15^th Feb 2042 is entered as =date(2042,2,15);

Rate: For Bond A, coupon rate is 4% given in C34;

Yld:   YTM values are various values given in column B40 to B53 of the Excel;

Redemption: Assume the redemption price or face value of the bond as $100;

Frequency: For Bond A, coupon is paid semi annually. Hence, frequency is 2;

Basis: For Bond A, day count is not given and value of basis would be 0.
Step 3 of 14

Open the Excel workbook downloaded from the website given in the book. Click on the tab named Problem 29 in the Excel. Note the following.

In cell C36 and D36, today’s date is equivalent to date of settlement. In cell C37 and D37, maturity date is to be applied.

Price of the bond A is to be calculated in cells C40 to C53. Price function needs to be repeatedly applied in each of the cell from cell C40 to Cell 53. YTM values to be used are given between Cell B40 to cell B53.

For Price function formula being applied in Cell C40, YTM value to be used is from Cell B40. For Price function being applied in Cell C41, YTM value to be used is from Cell B41.This is done till cell C53 is reached.
Step 4 of 14

In cell C36, input the following formula for settlement date.

=date(2012,2,15)

In cell C37, input the following formula for maturity date.

=date(2042,2,15)

Price Function in Cell C40 is applied as below:

=PRICE(C36, C37, C34, B40, 100, 2)

This process is repeated in all cells from cell C40 to C53 with only YTM cell address changing in the function. Hence, function in cell C41 would be

=PRICE(C36,C37,C34,B41,100,2)
Step 5 of 14

For Bond B, note the following.
In cell D36, ‘Today’s Date’ is equivalent to date of settlement. In cell D37, maturity date is to be applied.

Price of the bond B is to be calculated in cells D40 to D53. Price function needs to be repeatedly applied in each of the cell from cell D40 to Cell D53. YTM values to be used are given between Cell B40 to cell B53.

For Price function formula being applied in cell D40, YTM value to be used is from cell B40. For Price function being applied in cell D41, YTM value to be used is from Cell B41.This is done till cell D53 is reached.
Step 6 of 14

In cell D36, input the following formula for settlement date.

=date(2012,2,15)

In cell D37, input the following formula for maturity date.

=date(2042,2,15)

Price Function in Cell D40 is applied as below:

=PRICE(D36, D37, D34, B40, 100, 2)

This process is repeated in all cells from cell D40 to D53 with only YTM cell address changing in the function.
Step 7 of 14

Constructed Excel sheet would have formulas as in the image below. In the image first row of second column has value of ‘C’ which represents C column in book’s Excel sheet and first row of third has value ‘D’ which represents D column in book’s Excel sheet. First column in image below has values ranging from 36 to 54 which represent row number in book’s Excel sheet.

Using the cell reference of book’s worksheet, exact cell address to be input is shown below.

When function is input like shown above, the various values generated would be as in image below

This will set up the Excel workbook and calculate the prices for various values of YTMs.
Step 8 of 14

Difference in price or price sensitivity around 8% YTM is calculated by using the price of the bond when YTM is 8% and price of the bond when YTM is other than 8%. For example, when YTM is 2%, price sensitivity around 8% is calculated as below

In the Excel worksheet, price sensitivity for Bond A need to be calculated in cell E40 to E53 for each YTM for which price was calculated. For Bond B, it needs to be calculated in cell F40 to F53 for each YTM for which price was calculated.

To calculate price sensitivity for Bond A around 8% YTM when YTM is 2%, note the price of the bond at 2% YTM and 8% YTM. Price of bond at 2% YTM is calculated in cell C40 and price of bond at 8% YTM is calculated in cell C46. Hence, Excel formula to calculate price sensitivity for Bond A around 8% YTM when YTM is 2% is given as

=(C40-$C$46)/$C$46

Note the absolute cell referencing for cell C46. Since all sensitivities are calculated around 8%, it would be easier if cell C46 is provided absolute referencing.
Step 9 of 14

Similarly, to calculate price sensitivity for Bond B around 8% YTM when YTM is 2%, note the price of the bond at 2% YTM and 8% YTM. Price of bond at 2% YTM is calculated in cell D40 and price of bond at 8% YTM is calculated in cell D46. Hence, excel formula to calculate price sensitivity for Bond B around 8% YTM when YTM is 2% is given as

=(D40-$D$46)/$D$46

Note the absolute cell referencing for cell D46. Since all sensitivities are calculated around 8%, it would be easier if cell D46 is provided absolute referencing.
Step 10 of 14

Proceeding in the manner described above, input the formula to all other cells in Excel sheet. In the image first row of second column has value of ‘C’ which represents C column in book’s Excel sheet and first row of third has value ‘D’ which represents D column in book’s Excel sheet.

First column in image below has values ranging from 36 to 54 which represent row number in book’s Excel sheet. Exact formulas to be input in book’s Excel workbook are shown in image below.

Values generated by the formulas are given in image below.

These values are the percentage difference of bond prices at various YTM around 8% YTM.
Step 11 of 14

Copy the YTMs and differences around 8% for Bond A into a separate excel sheet. Block the copied area and draw a graph with YTM as horizontal axis and Difference as Vertical Axis. This is done in Excel by selecting

Insert>Charts>Line and by selecting first chart in 2-D Line as shown below.

Chart will appear like image below. Note that YTM is on horizontal axis and is also plotted as a blue line in the chart. This blue line needs to be removed as only Difference is to be plotted in the chart.
Step 12 of 14

This is done by doing a right clicking on the chart. A right click on the chart will show Chart Tools on the top of the Excel as in image above. From the chart tool select ‘Select Data’. A box like below would appear like in image below.

Since YTM as a series plotted in the chart is to be removed, select YTM and click on remove. Press OK. Final chart would look like below. Legends can be changed by clicking on legend or by selecting Chart Tools>Layouts.
Step 13 of 14

As illustrated above and explained for Chart A, Chart for Bond B is drawn as below.

Note in charts that slope of the line that represents the difference is steeper for Bond A than for Bond B. Also in the table, note that when YTM changes from 8% to 7%, change in bond price for A is $0.14 whereas for Bond B it is $0.12. Hence, Bond A is more sensitive to the change in YTM than Bond B.
Step 14 of 14

The reason for Bond A being more sensitive than Bond B lies in the coupon rate and time value of money. Note that Bond A pays less coupon than Bond B. Since Bond B pays more coupons, higher coupon in earlier years of Bond B pays off more of “effective maturity” than lower coupon of bond A. This is because impact of time value on earlier coupon payments is less in dollars terms for Bond B because of higher coupon.

This has effect of reducing the effective maturity or duration of the bond. Bond with lower duration is less sensitive to interest rate and YTM movements. This explains the difference and steeper slope of Bond A.