The goal of this project is to explore the topic of bond valuation. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. Make sure you reference cells in Excel where necessary and do not simply type in answers. Also, type up a one-page report in Word with a separate section for the introduction (description, variables, context, etc.), findings (results, plots, variable relationships, etc.), and conclusion (summary, lessons, etc.). Failure to follow all instructions will result in a deduction of points. This project is an individual project, so please submit independent work. Upon completion, please submit both your Word report and Excel file to Blackboard. Each question (A, B, C, D, E, F, G, H, I, and J) is worth 7 points (total of 70 points), and the report is worth 10 points.

Bond Valuation

Assume that a 10-year, 2.5% annual coupon bond with semiannual (two periods per year) payments has a par value of $1,000. Assume the bond can be called at the five-year mark (i.e., in 5 years) at a call price of $1,100. The bond currently sells for $950. Employ the Excel file to answer the following questions:

Part 1: Bond Yield

A)Fill in and calculate the basic bond data. Note that the periods to maturity is equal to the years to maturity times the periods per year; the periods until callable is equal to years until callable times the periods per year; the periodic coupon payment is equal to the annual coupon payment divided by the periods per year. (7 Points)