Microsoft Office Tutorials and References

In Depth Information

**Bond Calculations**

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

calculates his rate of return over the eight years remaining until the bond matures:

=YIELD(TODAY(),TODAY()+DATE(8,1,0), 6%,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.

Figure 11-18:
When the price is higher than face value, the yield is lower than the coupon.