How could we Construct an Excel spreadsheet that uses the input variables below (you may assume realistic values of your choice),

Trade date

Spot rate curve

Coupon rate

Coupon frequency (semi-annual or annual)

Maturity (up to 10 years)

Current price

to compute the following?

(i) the Yield to Maturity (YtM),

(ii) the current yield, and

(iii) the accrued interest as of any date between the trade date and the next coupon date.

We may use the built-in excel function to calculate the YtM.

Verify the accuracy of the yield to maturity computed in (i) by recalculating the bond price as a sum of discounted cash-flows.

We would use a curve with data points every 6 months, i.e., 6m, 1y, 1.5y, 2y, ..., 10y.

Spot Rate Curves-Quarterly data points

0#USDEDMZ=R

USD

Native Bid

Cash 0.092

7D 0.142

1M 0.15

2M 0.136

3M 0.189

4M 0.203

5M 0.193

6M 0.207

7M 0.22

8M 0.229

9M 0.238

10M 0.249

11M 0.261

1Y 0.28

1Y 3M 0.334

1Y 6M 0.398

1Y 9M 0.47

2Y 0.552

2Y 3M 0.638

2Y 6M 0.726

2Y 9M 0.815

3Y 0.906

3Y 3M 0.989

3Y 6M 1.067

3Y 9M 1.143

4Y 1.219

4Y 3M 1.288

4Y 6M 1.352

4Y 9M 1.414

5Y 1.477

5Y 3M 1.534

5Y 6M 1.589

5Y 9M 1.64

6Y 1.694

6Y 3M 1.741

6Y 6M 1.786

6Y 9M 1.829

7Y 1.874

7Y 3M 1.913

7Y 6M 1.945

7Y 9M 1.999

8Y 2.075

8Y 3M 2.12

8Y 6M 2.178

8Y 9M 2.25

9Y 2.331

9Y 3M 2.402

9Y 6M 2.4749Y

9M 2.528

10Y 2.547