Organization need fund for expansion now and then. There are two ways they can raise funds one is they can issue shares or they can borrow. When an organization borrows money it’s termed as bonds. Bonds are debt. So we can say the bond issuer (organization) is the borrower and the bond investor is the lender.
Figure: - Bonds Concept
The bond issuer pays periodic interest payments to the bond investor and repays the principal at the maturity. The interest payment is also termed as coupon payment. The coupon payment can be yearly, half yearly or quarter yearly. So the investor pays a principal, he gets regular coupons or interest payments and finally when the maturity finishes he gets the principal back. Below figure ‘bond payments’ shows the same in a more pictorial format.
Figure: - Bond payments
So lets take scenario and try to understand how we can do bond calculation using excel. Let’s consider the below scenario for bond calculation.
|
Settlement date |
2/24/2010 |
|
Maturity date |
4/23/2030 |
|
Coupon rate |
8% |
|
Target rate of return |
9% |
|
Redemption Value |
100 |
|
Frequency |
1 |
|
Basis |
0 |
|
What should be the value of the bond? |
|
Table: - Bond example
The bond is for 20 years (from 2/24/2010 to 4/23/2030) and the issuer promises to pay 8% on a yearly basis. We are expecting 9% percent returns so what should be the value of the bond?.
Excel provides a ‘price’ function which can make our life easy for solving bond calculations. ‘Price’ function is not available as a normal formula. We need to install the ‘Analysis toolpak’ for the same. To install analysis toolpak click on tools à Add-IN’s. You will be popped up with a screen as shown below. Check the ‘Analysis toolpak’ and click ok and we are ready to use the ‘price’ function.
Figure: - Add-Ins installation
Click on ‘Fx’ à select ‘Financial’ function and you should be able to use the ‘price’ function.
Figure: - Price function
Now let’s map the above problem with the formula. You can see how the problem can be easily mapped with ‘price’ formula. So let’s understand each attribute step by step.
Settlement: - This date is on which money and security exchange hands.
Maturity: - This is date on which the last coupon payment is done and also the principal amount is returned.
Rate: - This is the coupon rate. In the current scenario it’s annually because the frequency is 1 i.e. annually.
Yld: - This is required rate of return you are looking.
Redemption: - This is the amount per 100 of the face value when the bond is redeemed. For instance if the bond value is 720 INR then that is the 100 of the face value. If the bond issuer says that we will give a redemption of 106 that means you will get 763 INR as the redemption value.
Frequency: - This is the number of coupons paid each year. So its 1, 2 or 4 (annual, semiannual or quarter).
Basis: - This describes the method of counting the number of days between two dates. There are several methods, each of which makes different assumptions about how to count. 30/360 assumes that each month has 30 days and that there are 360 days in a year. Actual/360 counts the actual number of days, but it assumes that there are 360 days in a year. Actual/Actual counts the actual number of days in each month, and the actual number of days in a year. In Excel bond functions, 0 signifies 30/360, 1 specifies actual/actual, 2 is actual/360, 3 is actual/365 (which ignores leap days), and 4 represents the European 30/360 methodology.
Figure: - Price in action
The ‘price’ function always gives the percentage value. To find the actual value we need to multiply the percentage with bond price when it was listed. For the above calculation we will get 91%. So if the price of the bond is 7000 we need to buy the same at 6370 to get 9% return rate. The percentage calculation helps us to the compare the same with return of other bonds.
Note: - One of the most important aspects in any investment is what returns does it give back, same holds true for bonds. There are many methods to calculate the return from bonds YTM and YTC are the most used , we will try to cover the same in the coming questions.