General Electric has an outstanding bond that paid quarterly interest with an annual coupon of 4.25%, with a yield to maturity (required rate of return) of 3.85% and a maturity date of September 1^st, 2032.

Build a spreadsheet that calculates the correct price for this bond using a combination of the present value of a dollar and the present value of an annuity formulas, ensuring that the bond-pricing model is flexible enough to automatically calculate the bond price for any changes to yield, coupon, or maturity.

1. What is the price (as a percentage of principal) of the bond with a settlement date of September 1^st, 2022? If the yield (required rate of return) of this bond goes up 75 basis points what would the new price be?

Tip: Usually, coupons or interest on most bonds are paid twice a year (every six months), but sometimes they pay quarterly (every three months). In the case of a quarterly pay bond, because one-fourth of the coupon rate is paid every three months, a bond with a coupon rate of 5.00% would receive 1.25% of par every period. In order to take the compounding into effect when using this method in present value calculations, the time period is four times as long and the payment is one-fourth as much as the annual coupon payment.

Although bonds typically have a par value of $1,000, they are priced based on a par value of 100 (i.e. 100% of the value of the outstanding principal, or 100 cents on the dollar).

The formula to calculate the present value of a bond is given below. Adjustments sometimes need to be made for partial periods. The right portion of the Formula should to be recreated in Excel in order to complete part B of the homework. You are solving for two individual values, the present value of an annuity and the present value of the terminal repayment of the principal amount.

Where n = number of periods

C = semi-annual coupon payment

r = semi-annual discount rate (yield divided by 2)

M = maturity value (Par value)

t = time period when payment is to be received.

Note that this formula assumes payments are received at the end of the period.

To complete the calculation given in the above formula you will need to determine the quarterly coupon amount, the number of three-month periods until bond maturity (assume that we are holding the bond for exactly 10 years), the discount rate (taken from the yield to maturity), and the terminal payment for the bond.

JUST TELL ME THE FUNCTION IN EXCEL, TY!