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: HERE IS THE DATA TO HELP SOLVE THE PROBLEMS BELOW:

Can you please help with the questions BELOW, Above is the information you will need for the questions: Relation between Bond Value and Market Interest Rate

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.

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.

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

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).

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 DO THIS IN EXCEL WITH STEPS?

Basic Bond Data 10 Years to maturity: Periods per year: Periods to maturity: Annual coupon rate: Par value: Annual coupon payment: Periodic coupon payment: Current price: Call price: Years until callable: Periods until callable: 2 $20.00 2.50% $1,000 $25.000 $12.50 $950 $1,100 5 10 B) Yield to Maturity Periodic YTM Number of Periods (periods to maturity) Payment (periodic coupon payment) PV (current price of bond) $20.00 $12.50 FV (par value of bond) $950.00 $1,000.00 Peridodic YTM = 1.54% Annualualized YTM Number of Years (years to maturity) Payment (annual coupon payment) PV (current price of bond) 101 $25.00 FV (par value of bond) $950.00 $1,000.00 Annualized YTM = 3.09% C) Current Yield Annual Coupon Payment = Current Price = $25 $950 Current yield = 2.63% D) Capital Gain or Loss Yield Annualized YTM = Current Yield = 3.09% 2.63% Capital Gain or Loss yield 0.46% E) Yield to Call Periodic YTC Number of Periods (periods untill callable Payment (periodic coupon payment) PV (current price of bond) 10 $12.50 FV (call price of bond) $950.00 $1,100.00 Peridodic YTC = 2.71% Annualized YTC Number of Years (years untill callable) Payment (annual coupon payment) PV (current price of bond) $25.00 FV (call price of bond) $1,100.00 $5 $950.00 Annualized YTC = 5.46% 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 callabi 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% 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% J) Bond Value Graph Basic Bond Data 10 Years to maturity: Periods per year: Periods to maturity: Annual coupon rate: Par value: Annual coupon payment: Periodic coupon payment: Current price: Call price: Years until callable: Periods until callable: 2 $20.00 2.50% $1,000 $25.000 $12.50 $950 $1,100 5 10 B) Yield to Maturity Periodic YTM Number of Periods (periods to maturity) Payment (periodic coupon payment) PV (current price of bond) $20.00 $12.50 FV (par value of bond) $950.00 $1,000.00 Peridodic YTM = 1.54% Annualualized YTM Number of Years (years to maturity) Payment (annual coupon payment) PV (current price of bond) 101 $25.00 FV (par value of bond) $950.00 $1,000.00 Annualized YTM = 3.09% C) Current Yield Annual Coupon Payment = Current Price = $25 $950 Current yield = 2.63% D) Capital Gain or Loss Yield Annualized YTM = Current Yield = 3.09% 2.63% Capital Gain or Loss yield 0.46% E) Yield to Call Periodic YTC Number of Periods (periods untill callable Payment (periodic coupon payment) PV (current price of bond) 10 $12.50 FV (call price of bond) $950.00 $1,100.00 Peridodic YTC = 2.71% Annualized YTC Number of Years (years untill callable) Payment (annual coupon payment) PV (current price of bond) $25.00 FV (call price of bond) $1,100.00 $5 $950.00 Annualized YTC = 5.46% 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 callabi 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% 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% J) Bond Value Graph