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 澳洲幸运5开奖号码历史查询:rate of return, which refers to 澳洲幸运5开奖号码历史查询: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 澳洲幸运5开奖号码历史查询:capital gains or losses due to price fluctuation.

Calculating Yield in Excel

To calculate the YTM of a bond in Ex💙cel, you n༺eed 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 澳洲幸运5开奖号码历史查询: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.