How do bonds work?
Bonds are secuties issued by governments or companies to fund their operations. In return, they pay an interest and the amount borrowed at specific dates. To understand bonds you must be familiar with the following terms:
- Bond issuer: the company or government borrowing money;
- Bond holder: the investor lending the money;
- Maturity date: the date at which the issuer must return the borrowed amount back;
- Coupon: the interest paid during the life of the bond.
It's quite simple to understand how typical bonds work: issuer A borrows money from bond holder B for, say, 20 years. In return he pays periodic coupons (for example twice a year) and at the end of the period he pays the amount he received initially. There are many different types of bonds that will be discussed later. For now, let's concentrate on how we can perform some fundamental calculations in Excel for evaluating bonds.
Calculating Bond's Yield
The Excel function to perform this calculation is the YIELD function. The syntax of this function is as shown below.
As the name suggests, this function returns the totally yield a bond will give you at the time of its expiration. The various parameters in this formula are explained below
- Settlement: this parameter decides the date on which the bond security has been given to the person who bought the bond. Since this is a date, it has to be entered in a proper date format, for example "3/11/2011".
- Maturity: this is the expiry date of the bond, and is entered in the same manner as shown above.
- Rate: This is the annual interest rate of the bond.
- Pr: This is the price of the security per 100 units.
- Redemption: This is the redemption value per 100 units.
- Frequency: this defines how often the buyer is supposed to receive the interest payments in one year. For example, input 5 if the buy receives 5 payments per year.
- Basis: it specifies the type of day count.
Back into A Bond Price
Bond price can be calculated per $100 face value by using the inbuilt excel function PRICE(). It requires 7 arguments, and the syntax is as shown below:
Here settlement, maturity, rate, frequency and basis have the usual meaning as mentioned in the above example. Yld represents the annual yield and redemption is the redemption value of the security per $100. Please note that settlement and maturity are dates, and have to be entered in the proper format, otherwise the formula will encounter an error.
Total Cash Generated From a Bond Investment
The RECEIVED() function comes in handy when you wish to calculate how much total cash your bond is going to generate. Here we'll learn about how to use that function through an example. The syntax goes like this:
Here all the arguments have their usual meanings as mentioned in the above examples. Discount is the rate of discount of your security. Things will become clearer after you look at the following example.
As shown above, the amount received would be $1240.31.
Understanding Price Volatility
Suppose you have need to calculate the time duration it would take for your bond to get repaid via the money it generates. This might not be an easy task since that duration keeps changing with time. Excel employs the method of multiplying the present cash flow value by time duration elapsed before receiving it. Excel has an inbuilt function that can calculate this time for you and in this section we're going to learn how to use it. The name of the function is DURATION() and the syntax is as follows:
In this function, as the name suggests, settlement_date and maturity_date have to be in the date format as previously mentioned. The example below demonstrates how to use this function.
As we find out in the above example, the duration is 1.95 years.
T-bills, or treasury bills are bonds that are issued by the government. Although they offer lower interest rates, they are preferred by people because they are considered to be very safe. T-bills are calculated though a slightly different method from normal, and if you are not aware of what it is, excel has cool inbuilt functions for all calculations pertaining to T-Bills. The three functions are TBILLEQ, TBILLPRICE and TBILLYIELD. The first one calculates the percentage rate that is the bond equivalent of the bill. TBILLPRICE calculates the price of the T-Bill per 100 currency units of the face value, and the TBILLYIELD returns the amount your T-Bill will generate over the years.
The syntax of all three functions is as follows:
Price and Yield for Zero Coupon Bonds
In this section we'll learn how to calculate price and yield values for coupon bonds. Coupon bonds are bonds that do not pay any interest rates on recurring dates. On the contrary, they pay all the interest at the time of maturity of the bond.
The syntax and examples are given below.
With this, we conclude our discussion of functions for bond investors.