Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
Excel provides worksheet functions that are used to calculate various aspects of bonds. A bond is a financial instrument in which the buyer loans money to the bond issuer–usually a corporation or a government. Many of the functions that deal with securities (such as bonds) are beyond the scope of this book. However, examples of some of the more common functions are provided in this section.
| On the CD | The examples in this section can be found on the companion CD-ROM in the file named |
Bonds have certain properties that are worth reviewing, mostly because those properties are also arguments in many of the bond related functions:
-
Settlement: The date the security is transferred to the buyer.
-
Maturity: The date the loan (represented by the bond) is repaid to the buyer.
-
Rate: Also known as the coupon: the interest rate the issuer is paying on the bond.
-
Yield: The rate of return the buyer receives, including the interest payments and the discount.
-
Redemption: The amount the buyer receives at maturity, per $100 of face value. In typical cases, the buyer gets the face value, so this argument is 100.
-
Frequency: The number of times per year that interest is paid.
Pricing Bonds
Bond issuers set the properties of the bond before it is issued based on current market conditions. As market conditions change, the value of the bonds change as well.
For example, Company X issues bonds with a $100 face value, 10-year maturity date, and a 6% interest rate paid twice per year:
-
If interest rates rise: Earning 6% isn't so attractive anymore, and buyers will not be willing to pay $100. They will, however, be willing to pay something less.
-
If interest rates fall: The 6% coupon looks like a great deal, and the bonds will be in demand. In that case, buyers will pay more than the face value.
The PRICE function calculates the price an investor should pay for a bond to achieve a specified return on his money. The syntax for PRICE, with required arguments in bold, is
PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)
Given the preceding facts, an investor who requires a 7.5% return on his money would use the following formula to determine what price to pay for a bond that matures in eight years:
=PRICE(TODAY(),TODAY()+DATE(8,1,0),.06,.075,100,2)
The result of $91.10 is what the investor should pay so that his yield is 7.5%. He will get $6.00 in interest per year (6% × $100), plus he will earn an addition $8.90 when the bond matures and he is paid the $100 face value. These two components-the interest and the discount-make up yield.
The actual dates used for settlement and maturity are irrelevant as long as the time between the dates is correct. In this example, Company X issued the bonds two years earlier, but the investor didn't buy them until today. Because they were issued as ten-year bonds, they would mature in eight years from the day the investor bought them.
If instead, interest rates had fallen since the bonds were issued, and the investor required only a 5.2% return on his money, the formula would change slightly:
=PRICE(TODAY(),TODAY()+DATE(8,1,0),.06,.052,100,2)
Under these circumstances, the investor will be willing to pay $105.18 per $100 face value bond.
Figure 11-17 shows these calculations in a worksheet.
Calculating Yield
In the previous section, an investor knew what yield he wanted and calculated the price to pay to get it. If instead, he knows what price he is willing to pay, the YIELD function will tell him what his rate of return on his investment will be. The syntax for YIELD is
YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
The investor is still interested in buying the ten-year bond with a 6% coupon paid twice per year, but this time, he wishes to only pay $93.95 for each $100 face value bond. The following formula will calculate his rate of return over the eight years remaining until the bond matures:
=YIELD(TODAY(),TODAY()+DATE(8,1,0),.06,93.95,100,2)
The investor will make 7% on his investment if he pays $93.95 for these bonds. Had he been willing to pay more than the $100 face value, the resulting yield would be lower than the 6% coupon rate, as shown in Figure 11-18.
Категории