ZuoTi.Pro
Question

PLEASE READ EVERYTHING BEFORE WORKING ON IT. Would appreciate an excel spreadsheet and formulas too. Everything...

PLEASE READ EVERYTHING BEFORE WORKING ON IT. Would appreciate an excel spreadsheet and formulas too. Everything I get on excel is different than what I get on paper and I don't know what I'm doing wrong on excel.


14. Consider a bond that has a coupon of 8 percent paid semiannually and has a maturity of 5 years. The bond is currently selling for $1,047.25. Use Excel to do the following analysis.

a. What is its yield-to-maturity?

b. Compute its duration.

c. If interest rates are expected to increase by 75 basis points, what is the expected dollar change in price? What is the expected percentage change in price?

business   finance   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

Home nert Page Layout Formulas Data Review View dd-Ins Cut Σ AutoSum ー E ゴWrap Text ta copy ▼ в 1 프· ー· 鱼, Δ. :r-ㄧ 逻锂函Merge & Center. $, % , 弼,8 C Paste Conditional Format CeInsert Delete Format Formatting, as Table w styles. ▼ ㆆ ▼ Sort &Find & 2 ClearFe Select Edting Format Painter Clipboard Font Alignment Number Styles Cells AK132 AJ AK AL AM AN AO AP AQ AR AS 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 1 KE CAPM , UTILITY, SH COUPON YEARS PV COUPON PERIODS IN A YEAR PAR VALUE YTM (SEMI ANNUAL) YTM (ANNUAL) 8% 1047.25 1000 3.4338% EXCEL-RATE(AM 123*AM 125,-AM 12 2 *AM 126/AM 125,AM124,-AM126) ANS a 6.87% beta bond future INDEX INTL CAP BUDLEASING PV, FV, ANNUITYDIR cleanYIELD bond tru WACC RESI ex d erences: BB116 130% 02:08 29-01-2019Home nert Page Layout Formulas Data Review View dd-Ins E AutoSum Wrap Text в 1 프· ー· 鱼, Δ. :r-ㄧ 逻锂函Merge & Center. $, % , 弼,8 Conditional Format eCell Insert Delete Formsat Sort &Find & Format Painter Formatting as Table Styles2 Clear Clipboard AR147 Al Edting Formula Bar AJ AO AS 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 4 H KE CAPM UTILITY, SH C-AB PV OF PMT NOTE ON PV FACTOR 1/(1+r)An 1/(1+0.034438)41 1/(1+0.034438)A2 1/(1+0.034438)A3 AND SO ON TIME UNTIL PAYMENTS PAYMENT PV FACTOR AT 3.4338% DURATION WEIGHT YEARS 0.0369 0.0714 0.1035 0.1335 0.1613 0.1871 0.2111 0.2332 0.2537 7.0853 40 0.9668 0.9347 0.9037 0.8737 0.8447 0.8166 0.7895 0.7633 0.7380 0.7135 38.6721 0.0369 0.0357 36.1470 0.0345 0.0334 33.78680.0323 0.0312 31.5808 0.0302 0.0292 29.5187 0.0282 0.7085 37.3882 34.9470 32.6652 30.5323 40 1040 742.0079 PRICE 1047.25 8.4772 ANS b DURATION- 4.2386 [8.4772/2] port future INDEX INTL CAP BUD stru WACCRESI ex d 福 130% beta bond cleanYIELD bond erences: BB116Home nert Page Layout Formulas Data Review View dd-Ins Σ AutoSum Cut Wrap Text General Sort &Find & 2 ClearFe Select Editing ta copy. Formatting, as Table w styles. Styles ㆆ ▼ ▼ B า 프 . Ej-., Δ. : r_一 逻锂函Merge & Center. $, % , 弼,8 conditional Format . Cell Insert Delete Format Paste Format Painter Cells Number Font Alignment Clipboard AJ157 AO AP AQ AR AS AL AM AN Al AK 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 1 INTEREST RATE INCREASES BY 75 BASIS POINTS-0.75% EXPECTED % CHANGE IN PRICE-CHANGE IN YIELD DURATION/(1 + YIELD) EXPECTED % CHANGE IN PRICE =-0.75%* 4.2386/(1 + 6.87%) = 2.97% ANS c -31.15 EXPECTED DOLLAR CHANGE IN PRICE = 1047.25*(-2.97%)- cleanYIELD bond tru WACC RESI ex d GM future INDEX INTL CAP BUDLEASING PV, FV, ANNUITYDIR 13096 KE CAPM , UTILITY, SH erences: P33 beta bond 02:14 29-01-2019

0 0
Add a comment
Know the answer?
Add Answer to:
PLEASE READ EVERYTHING BEFORE WORKING ON IT. Would appreciate an excel spreadsheet and formulas too. Everything...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Please read before doing this. I have everything down but I'm stuck on b and c....

    Please read before doing this. I have everything down but I'm stuck on b and c. By stuck I mean what would the formula and final answer be in this scenario since it's SEMIANNUAL. I'm only familiar with annual payments regarding durations and price changes, and I'm seeing a lot of different results, too. Please do this on Excel as well. 14. Consider a bond that has a coupon of 8 percent paid semiannually and has a maturity of 5...

  • 14. Consider a bond that has a coupon of 8 percent paid semiannually and has a...

    14. Consider a bond that has a coupon of 8 percent paid semiannually and has a maturity of 5 years. The bond is currently selling for $1,047.25. Use Excel to do the following analysis. a. What is its yield-to-maturity? b. Compute its duration. c. If interest rates are expected to increase by 75 basis points, what is the expected dollar change in price? What is the expected percentage change in price? I only need b. I know how to do...

  • MUST BE DONE ON EXCEL USING EXCEL FORMULAS 2) You are looking at a bond with...

    MUST BE DONE ON EXCEL USING EXCEL FORMULAS 2) You are looking at a bond with 13 years to maturity, has a coupon rate of 4% paid semi- annually, and a price of 105. What is the Yield to Maturity of the bond?

  • This was all the information that I was given. QUESTION 1 What is the duration of...

    This was all the information that I was given. QUESTION 1 What is the duration of a five year, $1,000 Treasury bond with a 10 percent coupon (paid semiannually) if its yield to maturity is 12 percent? SUGGESTION: use the duration calculation spreadsheet provided for this problem. QUESTION 2 What is the modified duration of the bond in question one? QUESTION 3 Using the modified duration value from question two, what is the predicted price change for the bond if...

  • please answer question in excel and show the formula to get the answer ( I must...

    please answer question in excel and show the formula to get the answer ( I must have the formula) Yan Yan Corp. has a $2,000 par value bond outstanding with a coupon rate of 4.9 percent paid semiannually and 13 years to maturity. The yield to maturity of the bond is 3.8 percent. What is the dollar price of the bond? 1/1/2000 1/1/2013 4.90% Settlement date Maturity date Coupon rate Coupons per year Redemption value (% of par) Yield to...

  • Could i get the solution without using excel? by hand please. A coupon bond that pays...

    Could i get the solution without using excel? by hand please. A coupon bond that pays interest semiannually has a par value of $1,000, matures in 8 years, and has a yield to maturity of 6%. If the coupon rate is 8%, the intrinsic value of the bond today will be $1,125.61 $1,000 $1,062.81 $1,081.82

  • PLEASE SHOW YORK WORK!! STEP BY STEP! Posting just the answers or excel spreadsheet doesnt help...

    PLEASE SHOW YORK WORK!! STEP BY STEP! Posting just the answers or excel spreadsheet doesnt help me Question 2 A firm issues a bond today with a $1,000 face value, an 8% coupon interest rate, and a 25-year maturity. An investor purchases the bond for $1,000. (2.1) What is the yield to maturity (YTM)? Explain. (2.2) Suppose the investor bought the bond described previously for $900. What is the YTM? (2.3) Suppose the bond described previously has a price of...

  • Excel Online Structured Activity: Bond valuation You are considering a 20-year, $1,000 par value bond. Its...

    Excel Online Structured Activity: Bond valuation You are considering a 20-year, $1,000 par value bond. Its coupon rate is 11% , and interest is paid semiannually. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. Open spreadsheet If you require an "effective" arinual interest rate (not a nominal rate) of 8.03 % , how much should you be willing to pay for the bond?...

  • Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use...

    Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. 1) a. What is the price of a semiannual $1,000 par value bond with four...

  • Please do not use excel and use financial formulas 1. Consider the following five bonds, all...

    Please do not use excel and use financial formulas 1. Consider the following five bonds, all with notional amounts of $100.00, that are trading in a liquid market on September 30th 2018 i. T-bill 1: 1 year maturity, no annual coupon, market price = $99.01 ii. T-bill 1: 3 year maturity, no annual coupon, market price = $92.86 iii. Bond 1: 4 year maturity, 4% annual coupon, market price = $103.92 iv. Bond 2: 5 year maturity, 2% annual coupon,...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT