When assessing the profitability of bonds, analysts use a concept called yield to determine the amount of income an investment expects to generate each year. Yield is prospective and should not be confused with the rate of return, which refers to realized gains.
To calculate the current yield of a bond in Microsoft Excel, enter the bond value, the coupon rate, and the bond price into adjacent cells (e.g., A1 through A3). In cell A4, enter the formula "= A1 * A2 / A3" to render the current yield of the bond. However, as a bond's price changes over time, its current yield varies. Analysts often use a much more complex calculation called yield to maturity (YTM) to determine the bonds' total anticipated yield, including any capital gains or losses due to price fluctuation.
Calculating Yield in Excel
To calculate the YTM of a bond in Excel, you need the following information:
- Settlement Date: The date when you purchased the security. All dates should be entered using the DATE function in Excel rather than as text.
- Maturity Date: This is the date when the security will expire.
- Coupon Rate: This is the fixed rate of payment guaranteed annually.
- Price: This is the security's price per $100 of face value.
- Redemption Value: This is the redemption value of the bond per $100 of face value.
- Frequency: This is the number of coupon payments per year. Generally, payments are made annually, semi-annually, or quarterly.
- Basis: This is the annual day-count basis to use for calculation. This entry is optional; if omitted, it will revert to the Nasdaq 360-day standard count.
Enter all of the information into cells A1 through A7. If the day-count basis is omitted, there will be data only in cells A1 through A6. In the next available cell, enter the formula =YIELD (A1, A2, A3, A4, A5, A6, A7)to render the YTM of the bond. A formula omitting the day-count basis will not include the A7 entry.
