Question

In: Finance

Use the following filename: yourlastname.FIL242Assignment3; there will be one file submission, an Excel spreadsheet. ALL calculations...

Use the following filename: yourlastname.FIL242Assignment3; there will be one file submission, an Excel spreadsheet. ALL calculations must use Excel functions or Excel formulas. For example, bond prices can be computed using Excel’s PV function. All calculations must use cell references rather than typing in a number into a formula.

For consistency in spreadsheet design—use the template provided in the assignment. NOTE the spreadsheet template has THREE tabs—one tab for each question.

1. (10 points) For each of the bonds described below,

a) compute the bond’s price as the yield to maturity (YTM) varies from 1% to 20% (in increments of 1 percentage point).

b) Graph the price-YTM curves; put the curves of each bond in the same graph and label the graph.

Bond 1

Bond 2

Coupon rate

3%

8%

Annual coupon frequency

1

2

Par

$1,000

$1,000

Time to maturity (years)

10

20

2. (8 points) Compute the bond price and the percent change in price for Bond 1 when the yield to maturity changes from 5% to 6% for the following:

a) the original Bond 1 (compute the bond’s price using a YTM of 5% and a YTM of 6%)

b) if Bond 1’s time to maturity is 20 years rather than 10 (that is, compute the bond’s price using a YTM of 5% and a YTM of 6% given Bond 1’s inputs above but use a time to maturity of 20 years rather than 10 years)

c) if Bond 1’s coupon rate is 0% rather than 3% (compute the bond’s price using a YTM of 5% and a YTM of 6% using the original Bond 1 inputs except for these coupon rates)

d) if Bond 1’s original yield to maturity is 2% and it rose to 3% (compute the bond’s price using the original inputs but now using a YTM of 2% and a YTM of 3%)

NOTE: compute the percent change using the lower YTM price as the “base” or beginning value. For example, part a), the percent change is:

price with 6% YTM-price with 5% YTMprice with 5% YTM. Doing so will result in negative values of the percent change.

3. (7 points) Use Excel to compute prices and modified duration:

a) Compute duration and modified duration for Bond 1 using a discount rate (YTM) of 5%. Use the template to compute this the “long way.”

b) If the bond has a YTM of 5%, what is the estimated price, using modified duration, if the YTM falls to 4.75%?

c) What is the bond’s actual price at a YTM of 4.75%? (use this YTM to compute bond price)

d) What is the percentage difference between the actual and estimated price you computed?

Solutions

Expert Solution


Related Solutions

Use the following filename: yourlastname.FIL242Assignment3; there will be one file submission, an Excel spreadsheet. ALL calculations...
Use the following filename: yourlastname.FIL242Assignment3; there will be one file submission, an Excel spreadsheet. ALL calculations must use Excel functions or Excel formulas. For example, bond prices can be computed using Excel’s PV function. All calculations must use cell references rather than typing in a number into a formula. For consistency in spreadsheet design—use the template provided in the assignment. NOTE the spreadsheet template has THREE tabs—one tab for each question. 1. (10 points) For each of the bonds described...
Please use ONLY one Excel file to complete this case study, and use one spreadsheet for...
Please use ONLY one Excel file to complete this case study, and use one spreadsheet for each problem. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable. Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable. Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable. Which of these three models is the best?  Use R-square values, Significance...
Please use ONLY one Excel file to complete case study one, and use one spreadsheet for...
Please use ONLY one Excel file to complete case study one, and use one spreadsheet for each problem. Finally, upload the Excel file to the submission link (Week 3 Case Study) for grading. No credit will be granted for problems that are not completed using Excel. In 2011, when the Gallup organization polled investors, 26% rated gold the best long-term investment. But in April of 2013 Gallup surveyed a random sample of U.S. adults. Respondents were asked to select the...
Please use ONLY one Excel file to complete case study one, and use one spreadsheet for...
Please use ONLY one Excel file to complete case study one, and use one spreadsheet for each problem. No credit will be granted for problems that are not completed using Excel. In 2011, when the Gallup organization polled investors, 32% rated gold the best long-term investment. But in April of 2013 Gallup surveyed a random sample of U.S. adults. Respondents were asked to select the best long-term investment from a list of possibilities. Only 168 of the 650 respondents chose...
Please upload your calculations (e.g., an Excel spreadsheet, a Word document, a PDF file, or a...
Please upload your calculations (e.g., an Excel spreadsheet, a Word document, a PDF file, or a picture of your work) for Q21. Q21. Consider a 2-year bond. The coupon rate of the bond is 10%, and the bond pays coupons semiannually. The bond is selling at a yield to maturity of 8.0% annually, or 4.0% semi-annually. (a) What is the duration of the bond measured in half-years and in years? (10 points) (b) If the semi-annually yield changes from 4.0%...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
Answer the following problems in an Excel file. Please upload only one Excel file with all...
Answer the following problems in an Excel file. Please upload only one Excel file with all of your answers, including #3 (which requires an explanation rather than a calculation). All problems must be solved using the PV and FV functions in Excel. If I deposit $8,000 in a bank account that pays interest of 1.5%, compounded annually, how much will I have in the account after 10 years? If I deposit $8,000 in a bank account that pays simple interest...
2. Describe how you save a file with a new filename in Excel.
2. Describe how you save a file with a new filename in Excel.
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create a new data set that is a subset of this larger provided data set. The new data set should include the dependent variable (VALUE) and the following independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH, AIRCON and FIREPL). b. For this new data set, use the Correlation option of Data Analysis in Excel and produce the correlation matrix associated with the dependent variable...
PLEASE POST EXCEL SPREADSHEET Please show all computation and underline your final answer in your submission....
PLEASE POST EXCEL SPREADSHEET Please show all computation and underline your final answer in your submission. Income statements and balance sheets follow for Microsoft Corporation. Refer to these financial statements to answer the requirements. (non-operating items are red-marked) MICROSOFT CORPORATION Income Statements For the years ended June 30, (in millions) 2016 2015 Revenue      Product $61,502 $75,956      Service 23,818 17,624 Total revenue 85,320 93,580 Cost of revenue      Product 17,880 21,410      Service and other 14,900 11,628 Total cost...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT