How to Calculate Yield to Maturity Using Excel
Measuring the return on an investment in bonds requires more than just calculating the capital gain or loss. Many bonds make regular interest payments that must be accounted for when calculating the overall return. The yield to maturity, or YTM, encompasses all of these factors. However, it is not a simple calculation. Using software like Microsoft Excel makes calculating yield to maturity easier, faster, and more accurate.
Other People Are Reading
-
History
-
Prior to the development of computer software and financial calculators capable of calculating bond yields, investors relied on published tables. These bond tables were used to estimate the yield to maturity on bond investments.
Identification
-
Yield to maturity is one of the ways to calculate the yield of a bond investment. Yield to maturity, or YTM, is the yield if the bond is held until it matures and the full face value of the bond is repaid. This is in contrast to yield to call or YTC. Yield to call measures the yield of the investment if it is called, or repaid early, on the first day that the issuer is able to call the bond.
-
Function
-
Microsoft Excel offers several financial functions. To calculate the yield to maturity, use the "YIELD" function. The YIELD function requires several parameters to be entered in order to solve for the yield.
Using the YIELD Function
-
The syntax for the function is: YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) where all but basis are required input. Settlement is the date the bond transaction was completed. Maturity is date the bond matures and rate is the coupon rate of the bond. PR is the price of the bond. Redemption is how much the bond is sold or redeemed for, and frequency is how many times per year the bond pays interest. Basis is used to input the type of day count to use.
Considerations
-
In versions of Microsoft Excel before Excel 2003, the financial tools add-in needs to be installed to get access to all of the available financial formulas available. The dates required by the function must be input as serial dates. Use Excel's "DATE" function to return the proper date formats.
-
References
- Photo Credit savings bonds image by Stephen VanHorn from Fotolia.com
