Need help calculating these

Students will learn how to price a bond and compute its yield to maturity using the Excel functions. Students then learn how to compute the duration of a bond as a measure interest rate sensitivity. Requirements: Please complete the following requirements. Part I reviews what you learned from the Financial Management course. Part II requires you to compute the duration that you learned from chapter 11. You will use the duration of a bond to predict the percentage change of the bond price. Please follow the steps complete this project. You can use the Excel file (FINA 3121 Project 2 Format.xlsx) posted on Blackboard. The cells highlighted in green are to be filled in, while the yellow highlighted cells require setting formulae for calculations. 1. For Part I, see the attached screenshots for the price quotations of coupon bonds issued by Amazon and Walmart, respectively. Please use the following Excel functions to compute the price and yield to maturity of these two bonds: =Price(settlement, maturity, rate, yld, redemption, frequency, basis) =Yield(settlement, maturity, rate, pr, redemption, frequency, basis) The followings are the definitions of the arguments: Settlement is the date on which money and securities actually change hands. In this project, we assume that transactions are settled on the same day and the settlement date is April 14, 2021. In practice, settlement usually takes 1-2 days. Maturity is the date on which the last coupon payment is made and the principal is returned. Rate is the annual coupon rate, while yld is the annual required rate of return. Rate and yld in Excel should be entered in decimal form, or with a % sign added. Redemption is the amount to be received per $100 of face value when the bond is redeemed. We assume that the face value of a bond is $100. 2 Frequency is the number of coupons paid each year. These two bonds are semiannual coupon bonds. So the value of frequency is 2. Basis describes the assumption regarding the number of days in a month and year. Corporate bonds are priced assuming that there are 30 days in a month and 360 days in a year. The code for 30 days in a month and 360 days in a year is 0 for this argument basis. The argument pr in the yield function is the price of the bond as a percentage of the face value. Just enter the last trade price that you see from the screenshots for this argument. 2. Please compute the price of Amazons coupon bond by entering the data for these arguments: Settlement date (last trade date), maturity date, coupon rate, required return (last trade yield), face value, frequency, and basis. Excel will return you a price very close to the last trade price that you see on the screenshot. 3. Please compute the yield to maturity (required return) of the Walmart coupon bond by entering the data for these arguments: Settlement date (last trade date), maturity date, coupon rate, face value, frequency, basis, pr (last trade price). Excel will return you a yield to maturity very close to the last trade yield (2.849%) that you see on the screenshot. 4. For Part II, lets assume that the annual yield to maturity of the Amazon coupon bond increases by 0.5% (i.e. from 1.415% to 1.915%). What will be its new bond price and the actual percentage change in bond price? To compute the actual percentage change in Amazons bond price, you will take the difference between the new and old bond prices and divide it by the old bond price. 5. Please calculate Macaulays duration for Amazons coupon bond. Excel has a built-in function of computing Macaulays duration: =duration(settlement, maturity, coupon, yld, frequency, basis) 6. Please estimate the percentage change in bond price due to duration for Amazons coupon bond. = [1 + ( /2] The annual yield to maturity in the denominator is 1.415%. and is 0.5%, which is 1.915% - 1.415%. Compare the predicted percentage change in bond price with actual percentage change in bond price, and compute the predicting error. I have also attached a screenshot of Part II for your reference. However, you still have to set the formulae or use the Excel functions in these yellow highlighted cells.

Part 1 AMZN WMT Settlement Date Maturity Date Coupon Rate Required Return Face Value Frequency Basis Value Part II frate goes up by 0.5% Settlement Date Maturity Date Coupon Rate Required Return Face Value Frequency Basis Value (New bond price) Actual % change in bond price Duration Predicted % change in bond price due to duration Predicting error Part 1 AMZN WMT Settlement Date Maturity Date Coupon Rate Required Return Face Value Frequency Basis Value Part II frate goes up by 0.5% Settlement Date Maturity Date Coupon Rate Required Return Face Value Frequency Basis Value (New bond price) Actual % change in bond price Duration Predicted % change in bond price due to duration Predicting error