Complete the steps below using cell references to given data or previous calculations. In some cases,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Consider the following bonds: b. Bond A B с D Par value Old YTM New YTM F a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? b. Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? $1,000 6% 5% a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? Annual Coupon Bond A B с D Coupon Rate (annual payments) 0% 0% 4% 8% Bond Bond Coupon Rate 0% 0% 4% 8% Maturity 15 Maturity (years) 15 10 15 10 10 15 10 Old Price New Price Change Rank Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? Iis the most sensitive to changes in bond yields. is the least sensitive to changes in bond yields. Requirements 1. Start Excel - completed. 2. In cell F23, by using cell references, calculate the annual coupon payment of bond A. 3. To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26. 4. In cell G23, by using cell references and the function PV, calculate the price of bond A before the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 5. To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells G24:G26. 6. In cell H23, by using cell references and the function PV, calculate the price of bond A after the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 7. To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells H24:H26. 8. In cell 123, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond A. Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 4 and 6 in your calculations. 9. To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell 123 and paste it onto cells 124:126 Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 5 and 7 in your calculations. 10. In J23, by using cell references and the function RANK.EQ, rank the change in price due to the drop in the yield to maturity of bond A. Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. 11. To rank the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell J23 and paste it onto cell J24:J26. 12. In cell D30, find the bond that is most sensitive to changes in the bond yields. Select A, B, C, or D.. 13. In cell D31, find the bond that is least sensitive to changes in the bond yields. Select A, B, C, or D.. 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Consider the following bonds: b. Bond A B с D Par value Old YTM New YTM F a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? b. Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? $1,000 6% 5% a. What is the percentage change in the price of each bond if its yield to maturity falls from 6% to 5%? Annual Coupon Bond A B с D Coupon Rate (annual payments) 0% 0% 4% 8% Bond Bond Coupon Rate 0% 0% 4% 8% Maturity 15 Maturity (years) 15 10 15 10 10 15 10 Old Price New Price Change Rank Which of the bonds A-D is most sensitive to a 1% drop in interest rates from 6% to 5%? Which bond is the least sensitive? Iis the most sensitive to changes in bond yields. is the least sensitive to changes in bond yields. Requirements 1. Start Excel - completed. 2. In cell F23, by using cell references, calculate the annual coupon payment of bond A. 3. To calculate the annual coupon payment of bonds B thru D, copy cell F23 and paste it onto cells F24:F26. 4. In cell G23, by using cell references and the function PV, calculate the price of bond A before the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 5. To calculate the price of bonds B thru D before the fall in the yield to maturity, copy cell G23 and paste it onto cells G24:G26. 6. In cell H23, by using cell references and the function PV, calculate the price of bond A after the fall in the yield to maturity. Note: The output of the expression or function you typed in this cell is expected as a positive number. Use cell reference to the annual coupon payment from Step 2 in your calculations. 7. To calculate the price of bonds B thru D after the fall in the yield to maturity, copy cell H23 and paste it onto cells H24:H26. 8. In cell 123, by using cell references, calculate the percentage change in price due to the drop in the yield to maturity of bond A. Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 4 and 6 in your calculations. 9. To calculate the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell 123 and paste it onto cells 124:126 Note: Use cell references to the price of bonds before and after the fall in the yield to maturity from Steps 5 and 7 in your calculations. 10. In J23, by using cell references and the function RANK.EQ, rank the change in price due to the drop in the yield to maturity of bond A. Note: The function RANK.EQ returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. 11. To rank the percentage change in price due to the drop in the yield to maturity of bonds B thru D, copy cell J23 and paste it onto cell J24:J26. 12. In cell D30, find the bond that is most sensitive to changes in the bond yields. Select A, B, C, or D.. 13. In cell D31, find the bond that is least sensitive to changes in the bond yields. Select A, B, C, or D.. 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
Expert Answer:
Answer rating: 100% (QA)
Step 1 Start Excelcompleted Step 2 In cell F23 by using cell references calculate the annual coupon ... View the full answer
Related Book For
Fundamentals of Financial Accounting
ISBN: 978-0078025914
5th edition
Authors: Fred Phillips, Robert Libby, Patricia Libby
Posted Date:
Students also viewed these finance questions
-
Derby Chips is an international producer of corn chips. At the end of 2006, Mary Hahn, president of Derby, appointed a task force to focus on the packaging and product use segments of its products...
-
List three specific parts of the Case Guide, Objectives and Strategy Section (See below) that you had the most difficulty understanding. Describe your current understanding of these parts. Provide...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
What two accounting equalities must be maintained in transaction analysis?
-
A product is manufactured in a continuous process carried on successively in two departments, Assembly and Finishing. In the production process, materials are added to the product in each department...
-
What do you like best about working at this company?
-
On January 1, 2014, Ven Corporation had the following stockholders equity accounts. Common Stock (no par value, 90,000 shares issued and outstanding) ...$1,600,000 Retained Earnings...
-
3. A region has the peak-period electricity demand in MWh given by D(r) = { 40,008 rif r 64 104,000 1,000r if r64 6 Exercises for Markets Module by Metin akanyldrm where r is the wholesale price in...
-
On January 1, 2020, Trigson Ltd. had 580,000 common shares outstanding. During 2020, it had the following transactions that affected the common share account: Feb. 1 Issued 180,000 shares. Mar. 1...
-
What is the Three-Dimensional Model of Leadership Effectiveness adapted from William Reddin's managerial effectiveness? Explain.
-
Calculate the straight-line and sum-of-years-digits depreciation schedules for a $1,000 computer purchased by a college athletic department with a useful life of five years and no salvage value.
-
Daisy Farm, a NSW based dairy company exports fresh milk to China. Chinese Yuan (CNY) has been trading at AUD/CNY5.00. Exports to China are currently 100,000 litres per year at the equivalent price...
-
What is the leader's response and leadership effectiveness based on the Hersey and Blanchard model?
-
Find the magnitude of vector and the angle 6c it makes with respect to the positive x-axis, measured counterclockwise, when Cx = 0.00 and Cy = -2.00.
-
3. How strategic alliances differs from joint ventures. What alternatives do entrepreneurs have regarding these in the maturity stage of their companies? Give examples. 8:46 F
-
(a) With not more than 300 words, examine why Malaysia missed its 2020 targets to divert 40% of waste from landfills and increase recycling rates to 22%. (10 marks) b) Why arent Malaysian recycling?...
-
Okay Optical, Inc. (OOI), began operations in January, selling inexpensive sunglasses to large retailers like Walgreens and other smaller stores. Assume the following transactions occurred during its...
-
Rowland & Sons Air Transport Service, Inc., has been in operation for three years. The following transactions occurred in February: Feb. 1 Paid $ 200 for rent of hangar space in February. Feb. 4...
-
This case is available online in the Connect library. By completeing this case, you will learn to quantify cash embezzlement, identify missing cash controls, and recommend control improvements.
-
An entity denominated a sale of goods in a currency other than its functional currency. The sale resulted in a receivable fixed in terms of the amount of foreign currency to be received. The exchange...
-
If 1 Canadian dollar can be exchanged for 90 cents of U.S. currency, what fraction should be used to compute the indirect quotation of the exchange rate expressed in Canadian dollars? a. 1.10/1 b....
-
An entity denominated a December 15, 20X6, purchase of goods in a currency other than its functional currency. The transaction resulted in a payable fixed in terms of the amount of foreign currency...
Study smarter with the SolutionInn App