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

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

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

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-

gument.

•
redemption

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

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-

quencyvalues allowed.