## What Is Zero Coupon Bond?

When a bond does not pay** coupon **payments or** interest** and** trades** but rather pays a bulk amount of money at the **time of maturity**, it is called a **Zero Coupon **bond. A **Zero Coupon** bond is also known as a “**deep discount bond**” or “**discount bond**”. The sum of money paid at **maturity** is called the face value. Since a **Zero Coupon **bond provides no **coupons** or **interest** and **trades**, its transaction occurs at a discount to its **face value**.

## Zero Coupon Bond Price Calculator Excel: 5 Examples

The following table has **Bond Terms** and **Value **columns. We will use this table for the **zero coupon bond price calculator in Excel**.

### Example 1 – Applying a Generic Formula to Create a Zero Coupon Bond Price Calculator in Excel

The **generic formula **for** Zero Coupon Price Calculation** = **(Face Value)/****〖****(1+r)****〗****^t**

**Steps:**

- Use the following formula in cell
**C8**.

`=C5/(1+C6)^C7`

**Formula Breakdown**

**(1+C6) →**adds**1**with cell**C6**.**(1+8%)**→ Therefore, this becomes**Output: 1.08**

**(1+C6)^C7 →**is**(1.08)^10****(1.08)^10 →**As a result, it becomes**Output: 2.158924997279**

**C5/(1+C6)^C7 →**divides**20000**by**2.158924997279****20000/2.158924997279**→ Hence, it becomes**Output: $9263.87**

- Press
**Enter**.

**Read More: **How to Create Convertible Bond Pricing Model in Excel

### Example 2 – Zero **Coupon **Bond Price Calculator for Compounding Periods

The **generic formula **including **compounding periods per year**= `(Face Value)/`

`〖`

`(1+r/n)`

`〗`

`^t*n`

We can see the** Value **for **Compounding Periods Per Year (n)** is** 3**. We will use the** above formula **for** Zero Coupon Price Calculation**.

**Steps:**

- Use the following formula in cell
**C9**.

`=C5/(1+(C6/C8))^(C7*C8)`

**Formula Breakdown**

**(C7*C8)**→ It multiplies cell**C7**with cell**C8****(10*3) →**Therefore, it becomes**Output: 30**

**(C6/C8) →**divides cell**C6**by cell**C8****(8%/3)**→ Then, it becomes**Output: 0.026666666667**

**(1+(C6/C8)) →**is adding 1 with**0.026666666667****(1+0.026666666667) →**As a result, this becomes**Output: 1.026666666667**

**(1+(C6/C8))^(C7*C8) →**is**(1.026666666667)^30**- (1.026666666667)^30
**→**Then, it becomes**Output: 2.2033739695385**

**C5/(1+(C6/C8))^(C7*C8)**→ is dividing**C5**by**2.2033739695385**.**20000/2.2033739695385 →**becomes**Output: $9081.26**

- Press
**Enter**.

**Read More: **How to Make Treasury Bond Calculator in Excel

### Example 3 – Using the PV Function to Create a Zero Coupon Bond Price Calculator in Excel

**Steps:**

- Use the following formula in cell
**C8**.

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

**Formula Breakdown**

**PV(C6,C7,0,C5) →**The**PV**function calculates the**present value**of a**loan or investment**based on a**constant interest rate**.**C6**is the**rate**, which is referred to as**Yield to Maturity (YTM)****C7**is the**nper**, which is the**total number of payment periods****0**is the**pmt,**that is the**payment made on each period**. For**zero coupon bond**, as there is**no periodic payment**,**pmt**is**0****C5**is the**fv**, which is the**Future Value****PV(8%,10,0,20000) →**Therefore, this becomes**Output: -$9263.87**, here the**negative sign**means**outgoing cash flow**.

- Press
**Enter.**

### Example 4 – Using the PV Function to Make Zero **Coupon Bond Price Calculator for **Compounding Periods

We can see the **Value** of **Compounding Periods Per Year (n)** is **3**.

**Steps:**

- Use the following formula in cell
**C9**.

`=PV(C6/C8,C7*C8,0,C5)`

**Formula Breakdown**

**PV(C6/C8,C7*C8,0,C5) →**The**PV**function calculates the**present value**of a**loan or investment**based on a**constant interest rate**.**C6/C8**is the**rate**, which is referred to as**Yield to Maturity (YTM)****8%/3 →**Therefore, it becomes**Output: 0.026666666667**

**C7*****C8**is the**nper**, which is the**total number of payment periods****10*3 →**As a result, becomes**Output: 30**

**0**is the**pmt,**that is the**payment made on each period**. For**zero coupon bond**, as there is**no periodic payment**,**pmt**is**0****C5**is the**fv**, which is the**Future Value****PV(0.026666666667,30,0,20000) →**becomes**Output: -$9081.26**, here the**negative sign**means**outgoing cash flow**.

- Press
**Enter**.

### Example 5 – Using the RATE Function to Calculate the Interest Rate for a Zero Coupon Bond

We will use the RATE function to calculate the **Yield to Maturity-YTM (r)**, which is the** interest rate (r) **for a** zero coupon bond**.

**Steps:**

- Use the following function in cell
**C8**.

`=RATE(C7,0,C6,C5)`

**Formula Breakdown**

**RATE(C7,0,C6,C5) →**the**RATE**function returns the**interest rate per period of an annuity**.**C7**is the**npr**, which is the**total number of payment periods**

**0**is the**pmt,**that is the**payment made on each period**. For**zero coupon bond**, as there is**no periodic payment**,**pmt**is**0****C6**is**pv**, which is the**Present Value****C5**is**fv**, that is the**Future Value****RATE(10,0,-12000,20000) →**Therefore, it becomes**Output: 5%**

- Press
**Enter**.

## Practice Section

You can download the **Excel **file to practice the explained methods.

**Download the Practice Workbook**

**<< Go Back to Bond Price Calculator | Finance Template | Excel Templates**