# Relationship between time to maturity and yield excel

### Duration - Full Explanation & Example | InvestingAnswers The formula assumes a linear relationship between bond prices and yields even though the relationship is Over time, it shortens as the bond nears maturity. The Yield to maturity (YTM) of a bond is the discount rate that equates the The " 3" tells Excel to use an actual day count with days per year. Note: Excel. current yield, yield to maturity (YTM), and yield to call (YTC) on and between If you are comfortable using the built-in time value functions, then this will be a.

For example, consider the Company XYZ bonds with a duration of 5. If for whatever reason market yields increased by 20 basis points 0. The formula assumes a linear relationship between bond prices and yields even though the relationship is actually convex.

## YIELD function

Thus, the formula is less reliable when there is a large change in yield. In general, six things affect a bond's duration: The higher a bond's coupon, the more income it produces early on and thus the shorter its duration. The lower the coupon, the longer the duration and volatility. Zero-coupon bonds, which have only one cash flowhave durations equal to their maturities. The longer a bond's maturity, the greater its duration and volatility. Duration changes every time a bond makes a coupon payment. Over time, it shortens as the bond nears maturity. Change your formula in B14 to: The Yield to Call on a Payment Date Many bonds but certainly not allwhether Treasury bonds, corporate bonds, or municipal bonds are callable. That is, the issuer has the right to force the redemption of the bonds before they mature.

This is similar to the way that a homeowner might choose to refinance call a mortgage when interest rates decline. If you wish, you can jump ahead to see how to use the Yield function to calculate the YTC on any date. Given a choice of callable or otherwise equivalent non-callable bonds, investors would choose the non-callable bonds because they offer more certainty and potentially higher returns if interest rates decline. Therefore, bond issuers usually offer a sweetener, in the form of a call premiumto make callable bonds more attractive to investors. A call premium is an extra amount in excess of the face value that must be paid in the event that the bond is called before maturity. Notice that the call schedule shows that the bond is callable once per year, and that the call premium declines as each call date passes without a call.

• What Are Bonds and Bond Investing?
• What it is:
• Bond Yield Calculation Using Microsoft Excel

It should be obvious that if the bond is called then the investor's rate of return will be different than the promised YTM. That is why we calculate the yield to call YTC for callable bonds. The yield to call is identical, in concept, to the yield to maturity, except that we assume that the bond will be called at the next call date, and we add the call premium to the face value. Let's return to our example: What is the YTC for the bond? I have already entered this additional information into the spreadsheet pictured above. Note that the yield to call on this bond is Now, ask yourself which is more advantageous to the issuer: Obviously, it doesn't make sense to expect that the bond will be called as of now since it is cheaper for the company to pay the current interest rate.

However, bonds only pay interest twice a year, so there are only 2 days per year that the Rate function will give the correct answer. On any other date, you need to use the Yield function. Note that this function as was the case with the Price function in the bond valuation tutorial is built into Excel The Yield function is defined as: YIELD settlement,maturity,rate,pr,redemption,frequency,basis where settlement is the date that you take ownership typically 3 business days after the trade datematurity is the maturity daterate is the annual coupon ratepr is the current market price as a percentage of the face value, redemption is the amount that will be paid by the issuer at maturity as a percentage of the face value, frequency is the number of coupon payments per year, and basis is the day count basis to use.

### Bond Investment, Coupon, Yield, Maturity, Ratings Explained

Note that the dates must be valid Excel dates, but they can be formatted any way you wish. Also, both pr and redemption are percentages entered in decimal form. Our worksheet needs a little more information to use the Yield function, so set up a new worksheet that looks like the one in the picture below: Note that I've had to add exact dates for the settlement date and the maturity daterather than just entering a number of years as we did before.

Also, since industry practice which the Yield function uses is to quote prices as a percentage of the face value, I have added for the redemption value in B3. Finally, I have added a row B11 to specify the day count basis. With that additional information, using the Yield function to calculate the yield to maturity on any date is simple. Insert the following function into B Notice that we didn't need to make any adjustments to account for the semiannual payments.

The Yield function takes annual arguments, and uses the Frequency argument to adjust them automatically.

Calculating the Yield of a Coupon Bond using Excel

It also returns an annualized answer. Calculating the yield to call is done in the same way, except that we need to add the call premium to the redemption value, and use the next call date in place of the maturity date.

Enter the following function into B As noted, the nice thing about the Yield function is that it works correctly on any day of the year. You should find that the YTM is still 9. Make-Whole Call Provisions The above discussion of callable bonds assumes the old-fashioned type of call. However, for the last 15 years or so, corporations have typically used a "make-whole" type of call. To learn about those, please see my tutorial for make-whole call provisions.

I hope that you have found this tutorial to be helpful.