Bond Yield Calculator
One of the key variables in choosing any investment is the expected rate of return. We try to find assets that have the best combination of risk and return. In this section we will see how to calculate the rate of return on a bond investment. If you are comfortable using the built-in time value functions, then this will be a simple task. If not, then you should first work through my Microsoft Excel as a Financial Calculator tutorials.
Please note that this tutorial works for all versions of Excel. Furthermore, the functions presented here should also work in other spreadsheets (such as Open Office Calc).
You can download a spreadsheet that accompanies this tutorial, or create your own as you work through it. Since we will use the same example as in my tutorial on calculating bond values using Microsoft Excel, the spreadsheet is the same.
The expected rate of return on a bond can be described using any (or all) of three measures:
- Current Yield
- Yield to Maturity (also known as the redemption yield)
- Yield to Call
We will discuss each of these in turn below. In the bond valuation tutorial, we used an example bond that we will use again here. The bond has a face value of $1,000, a coupon rate of 8% per year paid semiannually, and three years to maturity. We found that the current value of the bond is $961.63. For the sake of simplicity, we will assume that the current market price of the bond is the same as the value. (You should be aware that intrinsic value and market price are different, though related, concepts.)
If you haven’t downloaded the example spreadsheet, create a new workbook and enter the data as shown in the picture below: