In this guide, you’ll see how to calculate the bond price using **Excel**.

Here are the steps:

## Steps to Calculate the Bond Price using Excel

Let’s explore the process of calculating the following bond price using **Excel**:

- Number of years to maturity is
**9** - Yield is
**8%** - Bond face value is
**1000** - Annual coupon rate is
**6%** - Payments are
**semiannually**

What is the bond price?

Here is the short version of the calculation to get the bond price:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | =B1*B5 |

7 | Periodic Yield: | =B2/B5 |

8 | Periodic Coupon Rate: | =B4/B5 |

9 | Periodic Coupon Payment: | =B3*B8 |

10 | Bond Price: | =PV(B7,B6,B9,B3) |

The resulted bond price (cell B10) is ($873.41):

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | 0.04 |

8 | Periodic Coupon Rate: | 0.03 |

9 | Periodic Coupon Payment: | 30 |

10 | Bond Price: | ($873.41) |

### Detailed Step-by-Step Guide

To start, here we have a simple Excel template to calculate the bond price:

A | B | |

1 | Number of Years to Maturity: | |

2 | Annual Yield: | |

3 | Bond Face Value: | |

4 | Annual Coupon Rate: | |

5 | Payments Per Year: | |

6 | Total Number of Periods: | |

7 | Periodic Yield: | |

8 | Periodic Coupon Rate: | |

9 | Periodic Coupon Payment: | |

10 | Bond Price: |

To calculate the bond price:

(1) In cell **B1**, enter the number of years until the bond matures – for example, **9 years**:

A | B | |

1 | Number of Years to Maturity: | 9 |

(2) In cell **B2**, input the annual yield as **0.08**, representing 8% in percentage terms:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

(3) Cell **B3** is for the face value of the bond, set at $1,000:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

(4) In cell **B4**, representing the annual coupon rate, input **0.06**, equivalent to 6%.

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

(5) In cell **B5**, for semiannual payments, input **2**:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

(6) Moving to cell **B6**, calculate the total number of periods by *multiplying* the years in **B1** by the payments per year in **B5**, resulting in **18** total periods:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | =B1*B5 |

(7) In cell **B7**, calculate the periodic semiannual yield by *dividing* the annual yield in **B2** by the number of payments per year in **B5**, resulting in **0.04**:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | =B2/B5 |

(8) Next, in cell **B8**, compute the periodic semiannual coupon rate by *dividing* the annual coupon rate in **B4** by the number of payments per year in **B5**, yielding **0.03**:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | 0.04 |

8 | Periodic Coupon Rate: | =B4/B5 |

(9) Proceed to cell **B9**, where the periodic coupon payment is calculated by *multiplying* the face value of the bond, $1,000 in **B3**, by the semiannual coupon rate, 0.03 in **B8**, resulting in **$30**:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | 0.04 |

8 | Periodic Coupon Rate: | 0.03 |

9 | Periodic Coupon Payment: | =B3*B8 |

(10) In cell **B10**, utilize Excel’s present value function (‘**=PV**‘) to find the bond price. This function takes the periodic semiannual yield in **B7**, the total number of periods in **B6**, the periodic coupon payment in **B9**, and the face value in **B3**:

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | 0.04 |

8 | Periodic Coupon Rate: | 0.03 |

9 | Periodic Coupon Payment: | 30 |

10 | Bond Price: | =PV(B7,B6,B9,B3) |

Upon hitting **enter**, view the calculated bond price in cell **B10**. This value represents the present value of future cash flows, providing the estimated bond price, which is ($873.41):

A | B | |

1 | Number of Years to Maturity: | 9 |

2 | Annual Yield: | 0.08 |

3 | Bond Face Value: | 1000 |

4 | Annual Coupon Rate: | 0.06 |

5 | Payments Per Year: | 2 |

6 | Total Number of Periods: | 18 |

7 | Periodic Yield: | 0.04 |

8 | Periodic Coupon Rate: | 0.03 |

9 | Periodic Coupon Payment: | 30 |

10 | Bond Price: | ($873.41) |