For example there is 10-years bond, its face value is $1000, and the interest rate is 5.00%. Before the maturity date, the bondholder cannot get any coupon as below screenshot shown. You can calculate the price of this zero coupon bond as follows:

Select the cell you will place the calculated result at, type the formula =PV(B4,B3,0,B2) into it, and press the Enter key. See screenshot:

Note: In above formula, B4 is the interest rate, B3 is the maturity year, 0 means no coupon, B2 is the face value, and you can change them as you need.

Let’s say there is a annul coupon bond, by which bondholders can get a coupon every year as below screenshot shown. You can calculate the price of this annual coupon bond as follows:

Select the cell you will place the calculated result at, type the formula =PV(B11,B12,(B10*B13),B10), and press the Enter key. See screenshot:
 

Note: In above formula, B11 is the interest rate, B12 is the maturity year, B10 is the face value, B10*B13 is the coupon you will get every year, and you can change them as you need.

Sometimes, bondholders can get coupons twice in a year from a bond. In this condition, you can calculate the price of the semi-annual coupon bond as follows:

Select the cell you will place the calculated price at, type the formula =PV(B20/2,B22,B19*B23/2,B19), and press the Enter key.

Note: In above formula, B20 is the annual interest rate, B22 is the number of actual periods, B19*B23/2 gets the coupon, B19 is the face value, and you can change them as you need.

How to create loan amortization interest calculator of Excel template?

How to calculate average/compound annual growth rate in Excel?