Microsoft Office Tutorials and References
In Depth Information
Examples of Functions for Bond Investors
For the following discussion, let ’ s assume that a city issues a 30-year
municipal bond. The bond is issued on July 1, 2015. The bond ’ s maturity date
is June 30, 2044. The city agrees to pay 3% interest semiannually.
Here is what makes bonds interesting: They can be bought and sold after the
issue date. Suppose that 14 months have gone past. Interest rates have now
risen. The bond is going to keep paying 3% interest for the next 30 years. If
interest rates have moved above 3%, a potential buyer of the bond will not
want to pay $1,000 for the bond. Instead, the buyer might pay $950. Thus, there
is a price paid for the bond, and there is a value of the bond at maturity.
Many bond functions ask for these arguments:
settlement — This is the day that the buyer purchases the bond. It
might be the issue date but is usually after the issue date. In the pre-
ceding example, the settlement date is September 1, 2015.
maturity — This is the day that the issuer will pay the face value
of the bond. In the preceding example, the maturity date is June 30, 2044.
rate — This is the published coupon rate for the bond. In the preceding
example, it is 3%.
• ppr — This is the price that the current buyer paid for the bond. If the
bond was purchased on the issue date, the price matches the face
value of the bond. If it was purchased on a later date, the price is
higher or lower than the face value, depending on whether interest
rates go up or down. For example, if interest rates go up, bond prices go
down. If interest rates go down, bond prices go up. The pris expressed
as the price per $100 of face value. If you buy a $1,000 face-value
bond for $950, the price is $95 per $100, so you enter 95 for the prar-
redemption — This is the value of the bond on the maturity date. It is
the amount the issuer will pay back to the holder of the bond. The
price is expressed as the price per $100 of face value. If you buy a
$1,000 face-value bond that will pay $1,000 at maturity, you enter
100 for the redemptionargument.
frequency — This is the number of interest payments per year. For
semiannual interest payments, enter 2. For quarterly payments, enter
4. For annual payments, enter 1. In Excel, these are the only three fre-