# How to Calculate Bond Yield to Maturity Using Excel

by Tom McNulty ; Updated July 27, 2017The “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.

#### Photo Credits

- Yasuhide Fumoto/Photodisc/Getty Images