How to Calculate Bond Yield to Maturity Using Excel

Save

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

  • Photo Credit Yasuhide Fumoto/Photodisc/Getty Images
Promoted By Zergnet

Comments

You May Also Like

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!