Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One straightforward way to calculate the value of a bond is to estimate the bond's yield given the investments condition such as price, coupon rate, and time to maturity. In Excel, you can calculate a bond's yield perhaps not surprisingly by using the YIELD function. The YIELD function has seven arguments, Settlement Date, Maturity Date, Percent Coupon which is listed here as its rate, Price, Redemption Value, Frequency, and Basis. The Settlement Date is the date that you gain ownership of the security.
That date will probably be different from the bond's issuance date, which is the date the bond is made available for sale. The Maturity Date is the date the bond will be paid off. Percent Coupon is the bond's interest rate. Price is the amount you paid for $100 of face value. Redemption is the bond's redemption value for $100 of face value that's most commonly $100. Frequency is the number of interest or coupon payments per year. Finally, Basis reflects the way the investment counts the days of year. The western calendar can have days of 28, 29, 30, or 31 days and years of either 365 or 366 days.
So many investments base their calculations on a year that is presumed to have 12 months of 30 days each for a 360 day year. The Excel help system describes the other options available including the European 3360 systems, which is slightly different from the US system. So to complete the formula using the values that we find in the Yield worksheet. The Settlement Date is in cell B3, Maturity Date is in cell B4, Percent Coupon is in cell B5, the Price is in cell B6.
The Redemption Value is in cell B7, Frequency is in cell B8 and the Basis, which will add but we don't have to, it's an optional argument and we are using the default of 0, but we'll just complete the formula, is in B9. Close the parenthesis and you see that this bond has an annual Yield of 4.88%. The YIELD function provides a simplified look at bonds. The function assumes the bond's interest rate stays constant, which almost never happens, that you reinvest your bond interest and that there are no delays in getting paid when the bond matures.
Even so the YIELD function provides a good first look at a bond's potential value.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64708 Viewers
80 Video lessons · 124322 Viewers
52 Video lessons · 60258 Viewers
59 Video lessons · 46091 Viewers