In assessing the profitability of bonds, analysts use the concept of yield to determine the amount of income that a given investment can be expected to generate each year. The yield is promising and should not be confused with rate of return that relates to income that has already been implemented.
(see what is the difference between yield and return?)
To calculate the current bond yield in Excel, enter the value of Bonds, coupon rate and bond price in the neighboring cells, say A1 to A3. In cell A4 enter the formula “= A1 * A2 / A3” to display the current bond yield. However, as the change of the bonds during its current yield varies. Analysts often use more complex calculation called yield to maturity (yield) to determine the total expected yield of the bonds, including any income from capital gains or losses due to price fluctuations.
(see also the guide to Excel for Finance)
To calculate bond yield in Excel, you will need the following information:
- Settlement date: the date you bought security. All dates should be entered using date functions in Excel and not as text.
- Maturity date: the date when expires.
- Coupon rate: a fixed rate payment every year guaranteed.
- Price: is the price of the securities per $100 of face value.
- Redemption value: this is the cost of buying bonds at $100 par value.
- Frequency: is the number of coupon payments per year. Generally, payments are made annually, semiannually or quarterly.
- Basis: this annual day-count basis used for the calculation. This Element is optional; if it is omitted, it will return to the Nasdaq 360 days a standard account.
Enter information in cells A1 through A7. If the day-base is missing, there will be data only in cells A1 through A6. In the next available cell, enter the formula =return (A1, A2, A3, A4, A5, A6, A7) to display the bond yield. Formula passing day-the database would not contain A7 record.