Question: a) Consider two bonds A and B with payments , where , , , . Bond A has just been issued. Its face value is

a) Consider two bonds A and B with payments , where , , , . Bond A has just been issued. Its face value is $1,000, it bears coupon rate of 7%, and it will mature in 10 years. Bond B was issued 5 years ago, when interest rates were higher.This bond has $1,000 face value and bears a 13% coupon rate. When issued, this bond had a 15-year maturity, so its remaining maturity is 10 years. The yield to maturity is 7% (see Cell B2).

i) Using the Excel spreadsheet below write down and explain the Excel formula on how we estimated the bond A and bond B price in Cells B17 and E17, respectively:

a) Consider two bonds A and B with payments , where ,

ii) Columns C and F in the Excel spreadsheet below report the time-weighted average maturity of the payments received from the Bond A and B, respectively. Write down and explain the Excel formula on how we estimated the values in Cells C5:C6 and F5:F6, respectively. Interpret those numbers.

, , . Bond A has just been issued. Its face value

iii) Using the Excel spreadsheet below, estimate the duration of each of the two bonds A (Cell B20) and B (Cell E20), using the Macaulay duration measure. Which bond has the longest duration? Show your calculations and interpret your results.

is $1,000, it bears coupon rate of 7%, and it will mature

iv) Using the Excel spreadsheet below, write and explain the Excel formula on how we estimated the duration value of bond A and bond B in Cells B22 and B24, respectively, by assuming that the two bonds' settlement date is 3 December 1996, and the maturity date is 3 December 2006.

in 10 years. Bond B was issued 5 years ago, when interest

B E G H D BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 2 3 7% 4 Year CEA 5 1 Time-Weighted Average Maturity of the Payments Received From the Bond A 70 0.0654 70 0.1223 70 0.1714 70 0.2136 70 0.2495 70 0.2799 70 0.3051 Time-Weighted Average Maturity of the Payments Received From the CLB Bond B 130 0.0855 130 0.1598 130 0.2240 130 0.2791 130 0.3260 130 0.3657 130 0.3987 2 6 7 8 9 5 6 10 11 12 130 0.4258 13 14 15 9 10 70 70 1,070 0.3259 0.3427 5.4393 130 1,130 0.4477 4.0413 16 17 Bond price 1,000.00 ? 1,421.41 ? Question i 18 A B F G H DE BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 Time-Weighted Average Maturity of the Payments Received From the Bond A Time-Weighted Average Maturity of the Payments Received From the Bond B 4 Year CA 5 1 130 ? Question ii 6 130 ? N 7 3 130 4 8 9 0.0654 0.1223 0.1714 0.2136 0.2495 0.2799 0.3051 130 0.0855 0.1598 0.2240 0.2791 0.3260 0.3657 0.3987 5 10 6 130 130 130 11 7 12 8 70 130 13 9 70 0.3259 0.3427 5.4393 130 0.4258 0.4477 4.0413 14 10 1,070 1,130 15 16 17 Bond price 1,000.00 1,421.41 A B E F D BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 Time-Weighted Average Maturity of the Payments Received From the Bond A 70 0.0654 Time-Weighted Average Maturity of the Payments Received From the Bond B Year CLA CLB 4 5 5 1 130 6 3 4 7 8 9 10 11 70 70 70 70 70 70 0.1223 0.1714 0.2136 0.2495 0.2799 0.3051 130 130 130 130 130 130 0.0855 0.1598 0.2240 0.2791 0.3260 0.3657 0.3987 5 6 7 70 70 1,070 0.3259 0.3427 5.4393 130 130 1,130 0.4258 0.4477 4.0413 12 8 13 9 14 10 15 16 17 Bond price 18 1,000.00 1,421.41 19 Estimation of the Macaulay duration using the mathematical formula 20 Duration ? ? Question iii B G I E F BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 4 Year 5 1 Time-Weighted Average Maturity of the Payments Received From the CA Bond A 70 0.0654 70 0.1223 70 0.1714 70 0.2136 70 0.2495 70 0.2799 70 0.3051 Time-Weighted Average Maturity of the Payments Received From the CB Bond B 130 0.0855 130 0.1598 130 0.2240 130 0.2791 130 0.3260 130 0.3657 130 0.3987 6 7 8 9 10 2 3 4 5 11 12 13 14 15 16 8 9 10 70 70 1,070 0.3259 0.3427 5.4393 130 130 1,130 0.4258 0.4477 4.0413 17 Bond price 1,000.00 1,421.41 18 19 Estimating the Duration of Bond A and Bond B using the Excel function and by assuming that the two bonds settlement date is 3 December 1996 and the maturity date is 3 December 2006. 20 21 22 Bond A 7.5152 ? Question iv 23 24 Bond B 25 6.7535 ? B E G H D BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 2 3 7% 4 Year CEA 5 1 Time-Weighted Average Maturity of the Payments Received From the Bond A 70 0.0654 70 0.1223 70 0.1714 70 0.2136 70 0.2495 70 0.2799 70 0.3051 Time-Weighted Average Maturity of the Payments Received From the CLB Bond B 130 0.0855 130 0.1598 130 0.2240 130 0.2791 130 0.3260 130 0.3657 130 0.3987 2 6 7 8 9 5 6 10 11 12 130 0.4258 13 14 15 9 10 70 70 1,070 0.3259 0.3427 5.4393 130 1,130 0.4477 4.0413 16 17 Bond price 1,000.00 ? 1,421.41 ? Question i 18 A B F G H DE BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 Time-Weighted Average Maturity of the Payments Received From the Bond A Time-Weighted Average Maturity of the Payments Received From the Bond B 4 Year CA 5 1 130 ? Question ii 6 130 ? N 7 3 130 4 8 9 0.0654 0.1223 0.1714 0.2136 0.2495 0.2799 0.3051 130 0.0855 0.1598 0.2240 0.2791 0.3260 0.3657 0.3987 5 10 6 130 130 130 11 7 12 8 70 130 13 9 70 0.3259 0.3427 5.4393 130 0.4258 0.4477 4.0413 14 10 1,070 1,130 15 16 17 Bond price 1,000.00 1,421.41 A B E F D BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 Time-Weighted Average Maturity of the Payments Received From the Bond A 70 0.0654 Time-Weighted Average Maturity of the Payments Received From the Bond B Year CLA CLB 4 5 5 1 130 6 3 4 7 8 9 10 11 70 70 70 70 70 70 0.1223 0.1714 0.2136 0.2495 0.2799 0.3051 130 130 130 130 130 130 0.0855 0.1598 0.2240 0.2791 0.3260 0.3657 0.3987 5 6 7 70 70 1,070 0.3259 0.3427 5.4393 130 130 1,130 0.4258 0.4477 4.0413 12 8 13 9 14 10 15 16 17 Bond price 18 1,000.00 1,421.41 19 Estimation of the Macaulay duration using the mathematical formula 20 Duration ? ? Question iii B G I E F BOND DURATION CALCULATION 1 Yield to Maturity (YTM) 7% 2 3 4 Year 5 1 Time-Weighted Average Maturity of the Payments Received From the CA Bond A 70 0.0654 70 0.1223 70 0.1714 70 0.2136 70 0.2495 70 0.2799 70 0.3051 Time-Weighted Average Maturity of the Payments Received From the CB Bond B 130 0.0855 130 0.1598 130 0.2240 130 0.2791 130 0.3260 130 0.3657 130 0.3987 6 7 8 9 10 2 3 4 5 11 12 13 14 15 16 8 9 10 70 70 1,070 0.3259 0.3427 5.4393 130 130 1,130 0.4258 0.4477 4.0413 17 Bond price 1,000.00 1,421.41 18 19 Estimating the Duration of Bond A and Bond B using the Excel function and by assuming that the two bonds settlement date is 3 December 1996 and the maturity date is 3 December 2006. 20 21 22 Bond A 7.5152 ? Question iv 23 24 Bond B 25 6.7535

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Consider two bonds A and B with payments , where . Bond A has just been issued. Its face value is $ 1 , 0 0 0 , it bears coupon rate of 7 % , and it will mature in 1 0 years. Bond B was issued 5...

Kentucky Hardware Company (KHC) is considering an investment project that requires a new machine for producing special tools. This new machine costs $1,000,000 and will be depreciated over 10 years...

e. Consider two bonds A and B with payments Ct, where t = 1,2, ...,10. Both bonds have $1,000 face value. Bond A has just been issued, it bears coupon rate of 7%, and it will mature in 10 years. Bond...

. Consider two bonds A and B with payments , where = 1,2, ,10. Both bonds have $1,000 face value. Bond A has just been issued, it bears coupon rate of 7%, and it will mature in 10 years. Bond B was...

Consider two bonds A and B with payments , where . Both bonds have $1,000 face value. Bond A has just been issued, it bears coupon rate of 7%, and it will mature in 10 years. Bond B was issued 5...

1. The yield to maturity of a bond with $1,000 par value, 7% coupon rate, semiannual coupons, and two years to maturity is 7.6% APR. Find its price. 2. A bond with an 8 year maturity, $5,000 face...

Question 9 Which statement about behavioural effect is True? Not yet saved a. Investors are reluctant to sell stocks with "paper" losses-Mental Accounting Marked out of 1.00 b. Investors are slow to...

Question 1 In the second quarter of 2013, Tesla Motors, Inc. (symbol: TSLA) raised $1 billion in funds by issuingcommon stock (about 3.4 million shares) and senior convertible bonds. Of this $1...

Back in 2017, Austria sold a 100 year bond with annual coupons, face value of 100 and coupon rate of 2.1%. The YTM of this bond today is 1.014%, what is its current price? (Note: right now this is a...

Calculate the speed of a 6-MeV electron, one with a kinetic energy of 6 MeV

Summarized the following essay. Thermodynamic in daily life Thermodynamic is very important in daily life or real life. Thermodynamic is mainly used in engineering or even medical. Thermodynamic is a...

When the cost - of - goods - solid method is used adjust cout to "net realicable value in welower - ur uns ( LCNRV ) approach what account is debited? Alowance to Redoce Imentory to Market Value....

A trade promotion that compensates retailers that transport orders from the manufacturer's warehouse is referred to as a . llank allowance. carry haulage finance case freight

Explain how Jack might utilize employee teams to increase employee empowerment. Jack Deppster arrived for his first day of work as store manager at Buzz, one of the largest and most popular retail...

How could Vox International enhance its HR planning so that recruitment and selec- tion are not always done in response to changing or new customer demands?

Should Jack allow some cashiers to determine how they cash out customers? Is this a good idea given that new technology will soon be implemented? What are the consequences of allowing, or not...