The assignment is located in the attachment!!!!!!

Due date: End of the class period on April 27, 20162. Any project that is submitted after the due date will not be accepted and a zero (0) will beassigned to theThe

project.3. This is individual work; it is NOT group work. Each student is individually responsiblefor completing the project. Each student is expected to do his/her own work without helpfrom any other person, except, possibly, the instructor.4. Use Microsoft Excel to perform the tasks described below. Once you have accomplishedthe tasks, answer the questions on pages 3-5 of this document. Record your answers onthe Answer sheet on page 6.5. Things to submit: Answer sheet plus Excel printouts showing your work. Refer to page 3. Please check the attachment

Project #2 General Instructions: 1. Due date: End of the class period on April 27, 2016 2. Any project that is submitted after the due date will not be accepted and a zero (0) will be assigned to the project. 3. This is individual work; it is NOT group work. Each student is individually responsible for completing the project. Each student is expected to do his/her own work without help from any other person, except, possibly, the instructor. 4. Use Microsoft Excel to perform the tasks described below. Once you have accomplished the tasks, answer the questions on pages 3-5 of this document. Record your answers on the Answer sheet on page 6. 5. Things to submit: Answer sheet plus Excel printouts showing your work. Refer to Page 3 for details. Primary objective: Financial modeling using spreadsheets is a standard tool in the investment analyst's tool kit. The overall aim of the Finance projects is to get students started on using spreadsheets for investment analysis. Specific learning objectives 1. Learn to embed formulas into Excel worksheets. 2. Compute the number of days since last coupon payment using the COUPDAYBS function. 3. Compute the number of days in the coupon period using the COUPDAYS function. 4. Compute the flat price of a coupon bond using the PRICE function. 5. Compute the invoice price of a coupon bond when the settlement date is between coupon payment dates. 6. Compute a bond's Macaulay duration using the DURATION function. 7. Compute a bond's modified duration using the MDURATION function. 8. Compute a bond's convexity. 9. Compute the change in a bond's price using modified duration and convexity. Task 1: Bond pricing between coupon dates When you buy a bond, the date of purchase (the settlement date) is often between two coupon payment dates. In this situation, the price you pay (the invoice price) is the sum of the flat price and the accrued interest. Invoice price = Flat price + Accrued Interest For a semi-annual payment coupon bond, the accrued interest = (Annual coupon payment/2) x (days since last payment/days separating payments) 1 In this exercise, you compute the invoice price of a $1000 par value, 5% semi-annual payment coupon bond maturing on December 31, 2022 for various settlement dates. Use a yield to maturity of 6.2%. a) Calculate the invoice price (as a percentage of par value and in dollars) when the settlement date is December 31, 2015. b) Calculate the invoice price (as a percentage of par value and in dollars) when the settlement date is April 20, 2016. Refer to BKM, p.303-304 for further information about bond pricing in Excel. Presentation of your work: Provide a one-page printout. Task 2: Bond Duration You just purchased a $1000 par value bond maturing on December 31, 2020. Suppose today's date (settlement date) is December 31, 2015 and the yield to maturity is 6%. Given all these inputs, do the following: a) Assume the bond is a zero coupon bond (with annual compounding). Compute the bond's Macaulay duration (using the DURATION function) and modified duration (using the MDURATION function). b) Holding everything else constant, now assume the bond pays coupons semi-annually. Compute the bond's Macaulay and modified durations for the following annual coupon rates: 4%, 5%, 6%, 7%, and 8%. c) Continue with the bond in part ( b ), but now assume (i) the annual coupon rate is 7% and (ii) the maturity date is now December 31, 2025. Compute the bond's Macaulay and modified durations. d) Continue with the bond in part ( b ), but now assume (i) the annual coupon rate is 7%, (ii) the maturity date is again December 31, 2020 and (iii) the yield to maturity is 5%. Compute the bond's Macaulay and modified durations. Refer to BKM, p.333-336 for further information about computing duration and modified duration in Excel. Presentation of your work: Provide a one-page printout. Task 3: Bond Convexity Convexity captures the curvature of a bond's price-yield relationship. For a semi-annual coupon payment bond, the semi-annual convexity is computed as, 2 The annualized convexity is: Annual Convexity = Semi-annual Convexity/4 You just purchased a $1000 par value bond maturing on December 31, 2020. Suppose today's date (settlement date) is December 31, 2015 and the yield to maturity is 6%. The bond pays coupons semi-annually and the annual coupon rate is 7%.1 Given all these inputs, do the following. a) Compute the bond's annualized convexity (assume semiannual yield to maturity of 3%). b) Suppose the yield to maturity increases to 10%. Compute the approximate percentage price change using only the modified duration (from Task 2 (b)). Approx. percentage price change = Dy100 c) Continue with the situation in part ( b ), but now calculate the approximate percentage price change using both modified duration and annualized convexity (from part ( a )). Approximate percentage price change is found by multiplying the result in below formula by 100. Refer to BKM p.356 to see how you can use Excel to compute a bond's convexity Presentation of your work: Provide a one-page printout. Multiple Choice Questions Instructions: 1. There are 10 questions. Choose the best answer to each question and write your answer choice (A, B, C, D or E) on the answer sheet (page 7). Each question is worth 10 points. 2. The questions will be graded according to the Course Syllabus. Your Excel printouts will serve as evidence of your work. 3. Submit the answer sheet together with your Excel printouts (stapled together). Arrange your work in the following order: (i) answer sheet, (ii) printout for Task 1, (iii) printout for Task 2, and (iv) printout for Task 3. 4. Make sure you write CLEARLY and NEATLY. Untidy work (such as illegible hand writing) will result in a 1 point penalty per question. 1 Note that in this case, the settlement date coincides exactly with the coupon payment date. Therefore, the flat and invoice prices are the same. 3 Questions: Questions 1 and 2 are directed at Task 1 1. What is the invoice price when the settlement date is December 31, 2015? a. 918.17 b. 932.68 c. 942.83 d. 950.62 e. 981.55 2. What is the invoice price when the settlement date is April 20, 2016? a. 918.17 b. 932.68 c. 942.83 d. 950.21 e. 981.55 Questions 3 to 8 are directed at Task 2 3. What is the Macaulay duration and modified duration of the zero-coupon bond maturing on December 31, 2020? a. Macaulay duration = 5.0000, modified duration = 4.7170 b. Macaulay duration = 4.5576, modified duration = 4.4249 c. Macaulay duration = 4.4717, modified duration = 4.3414 d. Macaulay duration = 4.3931, modified duration = 4.2651 e. Macaulay duration = 4.3209, modified duration = 4.1950 4. What is the Macaulay duration and modified duration of the 4% semi-annual coupon payment bond maturing on December 31, 2020? a. Macaulay duration = 5.0000, modified duration = 4.7170 b. Macaulay duration = 4.5576, modified duration = 4.4249 c. Macaulay duration = 4.4717, modified duration = 4.3414 d. Macaulay duration = 4.3931, modified duration = 4.2651 e. Macaulay duration = 4.3209, modified duration = 4.1950 5. What is the Macaulay duration and modified duration of the 7% semi-annual coupon payment bond maturing on December 31, 2020? a. Macaulay duration = 5.0000, modified duration = 4.7170 b. Macaulay duration = 4.5576, modified duration = 4.4249 c. Macaulay duration = 4.4717, modified duration = 4.3414 d. Macaulay duration = 4.3931, modified duration = 4.2651 e. Macaulay duration = 4.3209, modified duration = 4.1950 4 6. Holding time to maturity and yield to maturity constant, what can you infer about a bond's Macaulay duration and modified duration as the coupon rate decreases from 8% to 4%? a. Macaulay and modified durations remain unchanged. b. Macaulay and modified durations increase. c. Macaulay and modified durations decrease. d. Macaulay duration increases while modified duration decreases. e. Macaulay duration decreases while modified duration increases 7. Suppose the bond's annual coupon rate is 7%. What can you infer about the Macaulay duration as the maturity increases from December 31, 2020 to December 31, 2025? a. Macaulay duration stays the same. b. Macaulay duration increases. c. Macaulay duration decreases. d. None of the above. 8. Suppose the bond's annual coupon rate is 7% and the maturity date is December 31, 2020. What can you infer about the Macaulay duration as the yield to maturity decreases from 6% to 5%? a. Macaulay duration stays the same. b. Macaulay duration decreases. c. Macaulay duration increases. d. None of the above. Questions 9 and 10 are directed at Task 3 9. The current yield to maturity is 6%. What is the annualized convexity of the 7% semiannual coupon payment bond maturing on December 31, 2020? a. 85.0892 b. 67.9615 c. 49.7945 d. 21.2723 e. 12.3112 10. The yield to maturity rises from 6% to 10%. What is the approximate percentage price change of the bond if you consider both modified duration and convexity? a. -15.08% b. -16.78% c. 0.00% d. 16.78% e. 15.08% 5 Name_______________________ Date________________________ Project #2 - Answer Sheet Question 1 2 3 4 5 6 7 8 9 10 6