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) |