{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

ch 07 (Bonds)

# ch 07 (Bonds) - 1 2 3 4 5 6 A B 07 Chapter model C D E F G...

This preview shows pages 1–3. Sign up to view the full content.

07 Chapter model 12/10/2008 Chapter 7. Bonds and Their Valuation INPUT DATA Years to Maturity 15 Coupon rate 10% You know that 10% = 0.10, so rates can be shown as percents Annual Payment \$100 or as fractions. Excel works with fractions, so it reads 10% as Par value \$1,000 0.10. 10% The value of any financial asset is the present value of the asset's expected future cash flows. The key inputs are (1) the expected cash flows and (2) the appropriate discount rate, given the bond's risk, maturity, and other characteristics. The model developed here analyzes bonds in various ways. Bond valuation requires keen judgment with regard to assessing the riskiness of the bond, i.e., what is the likelihood that the promised coupon and maturity payments will actually be made at the scheduled times? Also, investing in bonds requires one to make implicit forecasts of future interest rates--you don't want to buy long-term bonds just before a sharp increase in interest rates. We do not deal with these important but subjective issues in this spreadsheet. Rather, we concentrate on the actual calculations used, given the inputs. BOND VALUATION (Section 7-3) A bond has a 15-year maturity, a 10% annual coupon, and a \$1,000 par value. The required rate of return on the bond is 10%, given its risk, maturity, liquidity, and other rates in the economy. What is a fair value for the bond, i.e., its market price? Required rate, r d We find the bond's price using Excel's PV function. First, put the pointer on cell C42, where we want to put the bond's value. Then click fx on the Formula Line, then Financial, and then PV to get the following dialog box. Fill the box in as shown below. A B C D E F G H I 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Value of bond = \$1,000.00 Thus, this bond sells at its par value. That situation always exists if the required rate is equal to the coupon rate. Required Bond Type in the labels as shown, Rate Value (r) 1000 Enter =C42 here to display the value calculate in C42. 0% \$2,500.00 Type in the interest rates as shown in Column A. 5% \$1,518.98 Highlight the area A54:B59. 10% \$1,000.00 Click on Data > Table to get this dialog box. The interest rate is the 15% \$707.63 input variable which enters the 20% \$532.45 model in cell C17. Enter it and then click OK. That causes Excel to calculate the bond's value at the rates shown in Cells A55:A59 and then display those values in B55:B59. Suppose the required rate r d (a) fell from 10% to 5% or (b) rose to 15%. How would those changes affect the value of the bond? First, change cell C17 from 10% to 5% and observe that the bond's value rises to \$1,518.98. Then change C17 to 15% and note that the value declines to \$707.63. You can see that the bond's price falls when the going interest rate (or required rate of return) rises, and the price rises when the interest rate falls. Finish by resetting C17 to 10%.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}