I have attempted to calculate the duration and modified duration in Excel for this problem for hours today. Can anyone help me figure out what I am doing wrong in figuring out the duration and know the formula to use in Excel? The MDuration formula in Excel asks me for specific dates based on this question and I am clueless how to overcome it. Thanks. I do not need the whole answer just an example of the formula and how it would look in Excel to make sure I am calculating it correctly.

Elliot Karlin is a 35-year-old bank executive who has just inherited a large sum of money.Having spent several years in the bank's investments department, he's well aware of the concept of duration and decides to apply it to his bond portfolio.In particular, Elliot intends to use $ 1 million of his inheritance to purchase 4 U.S. Treasury bonds:

1. An 8.55 %, 13-year bond that's priced at $ 1087.98 to yield 7.48 %.

2. A 7.801 %, 15-year bond that's priced at $ 1020.50 to yield 7.57 %.

3. A 20-year stripped Treasury (zero coupon) that's priced at $ 200.05 to yield 8.21 %.

4. A 24-year, 7.42 % bond that's priced at $ 950.76 to yield 7.88 %.

Note that these bonds are semiannual compounding bonds.

a. Find the duration and the modified duration of each bond.

b. Find the duration of the whole bond portfolio if Elliot puts $ 250000 into each of the 4 U.S. Treasury bonds.

c. Find the duration of the portfolio if Elliot puts $ 330000 each into bonds 1 and 3 and $ 170000 each into bonds 2 and 4.

d. Which portfolio b or c should Elliot select if he thinks rates are about to head up and he wants to avoid as much price volatility as possible? Explain. From which portfolio does he stand to make more in annual interest income? Which portfolio would you recommend, and why?

a. The duration and modified duration can be calculated using a spreadsheet, such as Excel.It gives the precise duration measure because it avoids the rounding-off errors, which are inevitable with manual calculations.