Question

u are required to produce a table showing bond valuesand the impact of changes in interest rate over the lifeof a bond and a diagram demonstrating the link between the changes in values(due to changes in interest rate) and time to maturity. The bond has a face value of $1,000, pays a coupon rate of 7% p.a paid annually and it is issued with 10 years to maturity. All calculations should be executed in excel.Your table should show the following:The value of the bond, year by year(from dateof issue until its maturity), assuming all other things remain the same. The value of the bond, year by year, from date of issue until its maturity, assuming that market interest rate increasesby 1.5%(hence yield to maturity increases by 1.5%), all other things remain the same.The potential%change in value, year by year, from the date of issue until its maturity.The % change in value demonstratesthe impact of the increasein interest rate on the bond value (or interest rate risk), for each year of maturity.From your table produce a diagram that demonstrates the relationship between% change in value and time to maturity. The initial market interest rate (yield to maturity) to be used is 12%. show your formulas used

Answer 1

The logic used in column B for year calculation is to adjust if the years have passed from the date of issue. In this particular example initially the coupon rate (7%) is less than YTM (12%) and due to this we know that the market value of the bond will be less than face value. Hence, as the year pass the market value will come close to its par value. By using this logic and putting values, a diagram can simply be created.