excel question and need the explanation and answer to help me learn.
Only bid if you are good in excel!!
Requirements: correct answer
1. Calculate the yield-to-maturity, the Macaulay duration, and the modified duration for a
5% annual pay coupon bond with exactly five years to maturity that is currently selling
2. Your company is about to issue five year bonds that will be rated BBB by Standard and
Poors. Your investment bankers tell you that you will have to pay 160 basis points over
Treasuries. Look up the yield-to-maturity on five year Treasuries in the Wall Street
Journal or online at wsjmarkets.com under Bonds and then Treasury Quotes and
calculate the yield-to-maturity on the bonds you are about to issue.
3. Look up the price of the February 2037 4.75% US Treasury Bond (CUSIP: 912810PT9).
You can find Treasury Price quotes at https://www.wsj.com/market-
a. Using the spreadsheet features in Excel, calculate the yield to maturity, the
duration, and the modified duration of the bond.
b. Using Excel, calculate the accrued interest on the bond.
c. Using Excel, construct a graph of bond price versus yield to maturity for the
d. Calculate the effective duration and convexity of the bond using the formulas in
the book or the class notes.
e. Using only your effective duration estimate, what would be the price of the bond
if the YTM increased by 100 basis points?
f. Using your effective duration and your effective convexity estimate, what would
be the price of the bond if the YTM increased by 100 basis points.
g. Using Excel, calculate the exact price of the bond if the YTM increased by 100
basis points. How close is it to your answer in part g?
4. Your job is to construct a $100 million long-only diversified portfolio of U.S. Treasury
Bonds with a modified duration of no more than 7.5 years but with the highest yield to
a. Construct a portfolio with at least 10 separate issues that meets this objective.
What is the yield to maturity and modified duration of this portfolio?
b. What will the modified duration of your portfolio look like one year from now?
c. What trades will you have to make over the next year in order to maintain the
target modified duration?
The spreadsheet Unit 4 Treasury Quotes September 20 2021 from WSJ for Bond
Exercises.xlsx has Treasury Quotes that you can use for the exercise. You will
have to calculate the modified duration of the bonds, and then calculate the
modified duration and yield of your chosen portfolio. Use Solver to find the
Use SUMPRODUCT function to calculate yield and modified duration of the
Count the total number of bonds in the portfolio with a weight greater than zero
using COUNTIF function. Make the number of bonds one of the constraints in