EXCEL FORMULAS PLEASE

$($j$)$ Cell H$2$ will have an alternate yield to maturity.

$($k$)$ Create formulas for Cells H$3$:H$4$ that use the bond base

information, the current date $($Cell E$8),$ and this yield to maturity $($H$2)$

to compute the clean dollar price and modified duration.

$($l$)$ Cell H$5$ displays a change in interest rates.

$($m$)$ Create formulas for Cells H$6$:H$9$ that use the bond base

information, the current date $($Cell E$8),$ and the information in Cells

H$2$:H$5$ to find the new yield to maturity, the new clean dollar price,

the actual percentage change in the prices, and the expected

percentage change in the price based on the modified duration $($Cell

H$4).$

$($n$)$ Your formulas will automatically populate in Cells B$7$:B$12$ for my

use in grading.

Assignment #$5$A: Performance Evaluation

Page $5$

$($o$)$ Change the number formats for your entries to best display the

information.

Step $4$: Solver Example

$($p$)$ The current and maturity dates and yield to maturity from your

original work will populate in Cells K$2$:K$4.$

$($q$)$ Use the Solver tool to find the coupon rate $($Cell K$6)$ for this bond

to have the same interest rate risk $($Modified Duration$)$ as your

original work.

$($r$)$ Hint: This will mean that Cell K$5$ will be equal to$,$ or very close to$,$

Cell E$17.$

$($s$)$ Your formula for the modified duration will appear in Cell B$13$ for

my use in grading.

$($t$)$ Change the number formats for Cells K$2$:K$6$ to best display the

information