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 2: Relation between Bond Value and Market Interest Rate

F. Assume the current annual market rate is 5% and the periodic market rate is half of the market rate. Use the Excel PV function to calculate the present value of the noncallable bond. Note for the PV function, a negative sign should be placed on both the payment and future value.

G) Assume the current annual market rate is 5% and the periodic market rate is half of the market rate. Use the Excel PV function to calculate the present value of the callable bond. Note for the PV function, a negative sign should be placed on both the payment and future value.

H. Calculate present values under different interest rates for the noncallable and callable bond tables. Use the Excel PV function to calculate bond present values.

H. Copy present values from the previous step into the table and fill in the table. A bond will only be called if it is cheaper for the issuer (i.e., if PV Noncallable PV callable is positive). Actual present value depends on whether the bond is called or not (i.e., actual present value will equal to the PV of a callable bond, only if it will be called).

J. Graph the actual present value (y-axis) against the annual market interest rate (x-axis). Label the y-axis, x-axis, and chart title. Use a scatter plot with straight lines and markers. (How do you create the Graph with steps?)

Part 2) Annual Market Rate: Periodic Market Rate: 5.00% 2.50% F) Noncallable Bond Value PV of noncallable bond: Rate (periodic market rate) Number of Periods (periods to maturity) Payment (periodic coupon paymen FV (par value of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present Value of bond G) Callable Bond Value PV of callable bond: Rate (periodic market rate) Number of Periods (periods untill callabl Payment (periodic coupon paymen FV (call price of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present value of bond H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% 1) Bond Value Table Annual Market Rate PV of Noncallable Bond PV of Callable Bond PV Noncallable - PV Callable Will the Bond be called Actual Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Part 2) Annual Market Rate: Periodic Market Rate: 5.00% 2.50% F) Noncallable Bond Value PV of noncallable bond: Rate (periodic market rate) Number of Periods (periods to maturity) Payment (periodic coupon paymen FV (par value of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present Value of bond G) Callable Bond Value PV of callable bond: Rate (periodic market rate) Number of Periods (periods untill callabl Payment (periodic coupon paymen FV (call price of bond) (See https://support.office.com/en-US/article/pv-function-3d25f140-634f-4974-b13b-5249ff823415) Present value of bond H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% H) Bond Value Table Noncallable Bond: Annual Market Rate Periodic Market Rate Number of Periods to Maturity Periodic Payment Future Value (par value Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% Callable Bond: Annual Market Rate Periodic Market Rate Number of Periods untill Callable Periodic Payment Future Value (call price Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8% 1) Bond Value Table Annual Market Rate PV of Noncallable Bond PV of Callable Bond PV Noncallable - PV Callable Will the Bond be called Actual Present Value 0% 1% 2% 3% 4% 5% 6% 7% 8%