Hello,

I'm looking for some help with the attached questions. I'd like to be able to do these questions in Excel exclusively. Can you please help me figure out the appropriate Excel functions to use for each problem?

Thank you!See Bond Pricing Questions.doc

2015 Spring2 Financial Concepts Homework Assignment 7 Bond Pricing Student Name: Instructions Leave all answers in TWO decimal places. For the boxes in Question 1 through 5, put calculator input numbers in the second row and your computed answer in the third row. Question 1 thru 5 cash flow sign conventions: negative PV [assuming we are buying bonds]. Therefore PMT and FV are positive numbers [receiving periodic coupons and receiving face value at maturity]. All bonds have a face value of $1,000. Assume that all coupon payments are made annually unless the problem specifically states otherwise. Some additional notes and guidance at the end of the document. 1) What is the current value of a 10-year bond with a 5% coupon rate if the current market interest rate is 6.00%? N ? i ? PV PMT ? FV ? =? 2) A zero coupon bond is sold at a discount and pays no cash interest during its lifetime. At maturity it pays the face value of the bond. What is the current value of a 20-year zero coupon bond if the current market interest rate is 5.00%? N ? i ? PV PMT ? FV ? =? 3) What is the current value of a 15-year bond with a coupon rate of 4% (annual) with semiannual coupon payments if the current market interest rate is 3.0% (annual)? [The coupon frequency is explicitly stated here because we want to compare this PV(bond) to the next question. Everything else being equal, the frequency of coupons DO affect the price of the bond.] N ? i ? PV PMT ? FV ? =? MET AD-632OL -1- Financial Concepts 2015 Spring2 4) What is the current value of a 15-year bond with a coupon rate of 4% with quarterly coupon payments if the current market interest rate is 3.0%? N ? i ? PV PMT ? FV ? =? 5) What is the Yield to Maturity (YTM) and Yield to Call (YTC) for a bond which is currently priced at $1,025 if the bond has a coupon of 6%, matures in 10 years but could be called at a price of $1,050 in 6 years? Spring2 Fall2 YTM: N ? YTM PV ? PMT ? FV ? PV ? PMT ? FV ? =? YTC: N ? YTC =? MET AD-632OL -2- Financial Concepts 2015 Spring2 6a) Calculate the Value of the Bonds under the following assumptions: [assume annual coupons; $1,000 face value] [you can leave the following bond prices as positive numbers; easier to calculate % change] Market Interest Rate = 4% Maturity 10-Yr Period 1 2015 Spring2 1% coupon 4% coupon 7% coupon 30-Yr What value? What value? 1,000.00 1,000.00 What value? Market Interest Rate = 2% What value? Maturity 10-Yr 1% coupon What value? What value? 4% coupon What value? What value? 7% coupon Period 2 30-yr What value? What value? % Change in Bond Value from Period 1 to 2 Maturity: 10-Yr 30-Yr 1% coupon 4% coupon 7% coupon 6b) What do you conclude about the relative sensitivity of bond prices to changes in interest rate? [Hint: use the above calculated numbers to make comments on the following comparisons with regard to how sensitive are bond price changes when market interest rate changes] 1) Longer maturities relative to shorter maturities? Put your response here .... 2) Higher coupon bonds relative to lower coupon bonds? Put your response here .... MET AD-632OL -3- Financial Concepts 2015 Spring2 Some notes on bond calculations using the financial calculator 1) As usual, make sure your P/Y is set to 1. This makes the calculator calculate in \"period mode\" since not all Time Value problems are on an annual basis. PS: if you are using a HP 12C, this is always set as such; no need to fiddle with this. PS: some other brands of calculators (e.g TI), fresh out of the box, has a factory setting of \"12\"; this is bad!!! Set it to 1. 2) Having done (1), \"N\" or \"n\" is the number of periods (e.g. years, months, semi-annual, etc.) depending on the question, and \"I/Y\" or \"i\" is the periodic rate, and \"PMT\" is the periodic payment. PS: different calculators label their buttons differently as in above. 3) ... where number of periods = number of years x number of coupon payments per year and the periodic rate = the bond's yield number of coupon payments per year and each coupon payment = coupon rate x face value number of coupon payments per year 4) For example, if a 15-year bond, 4% coupon rate [Question #3] makes semi-annual payments, then N = 15x2 or 30 periods (i.e. 6-month semi-annual periods) and PMT = 4% x $1,000 2 or $20 periodic payment (i.e. each coupon payment is $20 and there are 2 coupon payments per year). PMT in this case is the periodic repetition of the same amount of coupon payment, i.e. an annuity. 5) In a similar fashion, if you are asked to calculate a bond's YTM or YTC, you still continue to enter the number of periods in \"N\" and the periodic coupon payments in PMT. Once you calculated the \"i\" or the \"I/Y\