Question $1$

Create an excel sheet that takes as input Face Value $($one number in dollars$),$ Coupon Rate $($one number as a percentage$)$ and Years to Maturity $($two numbers, one each for two bonds$)$ as inputs.

Using this input calculate the price of a bond with a yield to maturity of between $0\%$ and $15\%$ in $1\%$ increments. Do this for both bonds $($both maturities$).$ I recommend you use the PV formula for this.

Plot the two sequences of $16$ bond prices in a line chart with markers to denote the calculated prices. Bond price should be on the vertical axis, yield to maturity should be on the horizontal axis. They should be on the same chart.

In the excel document, answer the following question

Which bond price changes more when the yield to maturity changes? Why?

Question $2$

Create an excel sheet that takes as input Face Value $($as one number in dollars$),$ Coupon Rate $($as one number as a percentage$),$ Years to maturity $($as one number$),$ and Yield to Maturity for three bonds $($three different numbers as a percentage$).$

Using these inputs, calculate the bond prices for all three bonds $($again$,$ I recommend using the PV formula to calculate the price$),$ for years to maturity from the number you input, all the way down to zero. Plot these three price series on a line chart with markers. They should be on the same chart. In the excel sheet answer the following question.

Are the bond prices further apart from each other when you are close to or far away from maturity? Why?

Extra credit will be awarded if you get the table column headings and the legend headings to update automatically when the user enters new data for both Q$1$ and Q$2.$ For example, when the user enters bond $1$ as $8$ years for question $1,$ then F$5$ should update, and the chart legend should now read $"8$ year bond". Please make this in excel and show me the steps