Bonds essentially refer to a contract between an investor and a borrower. It is a type of loan to be repaid at the end of the maturity period. Some bonds have annual or semi-annual coupons which are basically predetermined interest payments. These coupons are incurred as additional cost of borrowing, and are paid to investors on a regular basis until the bond’s maturity.

This article will show us how to calculate the bond value or bond price for a zero coupon bond, an annual coupon bond and a semi-annual coupon bond.

*Figure 1. Final result: **Bond price formula*

**Calculate bond price**

Bond price is the current discounted value of a future cash flow. In simple terms, a bond price is the sum of the present value of the principal payment and the interest payments. In order to calculate the bond price, we can use the PV function.

**Syntax of PV function**

`=PV(rate, nper, pmt, [fv], [type])`

**Rate**is the interest rate per period**Nper**is the total number of payment periods**Pmt**is the payment made each period and is fixed until bond maturity**Fv**is the future value at bond maturity**Type**is either 0 ((if payment is at the end of the period) or 1 (payment at the beginning of the period)

* Figure 2. Sample data to **calculate bond price*

**Price of zero coupon bond**

Our example is a ten-year bond with a face value of 10,000 and an interest rate of 5%. In order to calculate the bond price with no coupons, we enter this formula in C8:

`=PV(C6,C5,0,C3)`

- Interest rate = 5%
- Number of periods = 10
- Payment made each period = 0 (zero coupons)
- Face value = 10,000

* Figure 3. **Bond price formula** for zero coupons*

The resulting bond price is -6,139.13. In accounting, the negative sign means it is an outgoing cash flow.

**Price of annual coupon bond**

There are bonds wherein the investors get a coupon each year. In order to calculate the price of an annual coupon bond, we enter this formula in D8:

`=PV(D6,D5,D3*D7,D3)`

- Discount rate = 4%
- Number of periods = 10
- Payment made each period = 10,000 x 2.5%
- Face value = 10,000

* Figure 4. **Bond price formula** for annual coupon bond*

As a result, the bond price is 8783.37.

Price of semi-annual coupon bond

There are also bonds that give out coupons twice in a year. The formula for bond price is:

`=PV(E6/2,E5,E3*E7/2,E3)`

- Discount rate = 4%/2 = 2%
- Number of periods = 10×2 = 20
- Payment made each period = 10,000 x 2.5%/2
- Face value = 10,000

* Figure 5. **Bond price formula** for semi-annual coupon bond*

Note that for semi-annual coupons, we divide the discount rate and coupon rate by 2, and the number of periods is twice the number of years. The resulting bond price is 8773.64.

**Instant Connection to an Excel Expert**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment