The “Yield” function in Excel quickly calculates a bond's yield to maturity. To make the function work, it is essential to set up the input cells correctly.
Open a Blank Excel Spreadsheet
In Excel click “File” then “New” using the toolbar at the top of the screen.
Set Up the Assumption Labels
Type the following labels into cells A1 through A8:
Settlement Maturity Rate Price Redemption Frequency Basis Yield
Enter the Dates
Enter the settlement date into cell B2. The settlement date is when an investor buys the bond. For this example, enter “10/15/2010.”
Enter the maturity date into cell B2. The maturity date is the exact date when the bond matures or expires. For this example, enter “10/15/2012.”
Enter the Other Key Assumptions
Enter the annual interest rate that the bond pays into cell B3. In this example, use “.03” or 3 percent.
Enter the bond’s price into cell B4. Bonds most often price at a discount to a par value of $100, so in this example enter a number lower than $100, such as $97.
Enter the face value, or par value, of the bond into cell B5. For example, enter “$100.”
Enter the number of times per year that the bond pays interest into cell B6. For a semi-annual bond, as in this example, enter the number “2.”
Enter the basis into cell B7. Basis refers to the number of days in a calendar year that are used to calculate interest. In this example enter “0” to represent the most common type of basis days.
Use the Yield Function to Calculate the Answer
Type the formula “=Yield(B1,B2,B3,B4,B5,B6,B7)” into cell B8 and hit the “Enter” key. The result should be 0.0459--4.59 percent--which is the annual yield to maturity of this bond.
References
Writer Bio
Tom McNulty is a consultant and a freelance writer based in Houston, Texas. He holds degrees from Yale and Northwestern, and has worked in banking, government, and in the energy industry. McNulty has published several articles for eHow on a variety of finance, accounting, and general business issues.