In Excel, we often need to **make Treasury Bond Calculator** to calculate the** Yield to Maturity** and the **Bond Price** of the treasury bond. By definition, a **Treasury Bond** is a debt security of the U.S. government with a fixed rate and a maturity ranging from **10 **years to **30 **years. Now, let’s start with this article and explore **2 **simple ways to **make treasury bond calculator in Excel**.

**Table of Contents**hide

## Download Practice Workbook

## 2 Simple Methods to Create a Treasury Bond Calculator in Excel

In this section of the article, we will learn **2 **simple methods to **make treasury bond calculator in Excel**. Here, we will use both Excel functions and mathematical formulas to make a treasury bond calculator.

Not to mention that we have used the *Microsoft Excel 365 *version for this article, you can use any other version according to your convenience.

### 1. Using Excel Functions

In the first method, we will use the **YIELDDISC** and **PRICEDISC** functions of Excel to create a **treasury bond calculator**. The **YIELDDISC **function returns the annual yield for a discounted security like a **treasury bond**. On the other hand, the **PRICEDISC **function gives us the price per **$100** face value of a discounted security like a** treasury bond**.

For instance, here, we have the** Details of Bond Information **as our dataset. Our aim is to calculate the **Yield to Maturity (YTM) and the Price **of the bond. Let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, enter the following formula in cell
**C8**.

`=YIELDDISC(C5,C6,C7,100,3)`

Here, cell **C5 **represents the **Trade Date** of the bond, cell **C6 **refers to the** Maturity Date**, cell **C7 **indicates the **Price **of the bond, **100 **indicates the **Redemption Value** per **$100** face value, and **3 **refers to the **365 days a year** option.

- Following that, press
**ENTER**.

Consequently, you will have the **Yield to Maturity** of the bond as marked in the following image.

- Now, enter the formula given below in cell
**C8**.

`=PRICEDISC(C5,C6,C7,100,3)`

Here, cell **C7 **refers to the** Yield to Maturity** of the bond.

- Then, hit
**ENTER**.

As a result, the **Price **of the bond will be available in cell **C8 **as shown in the picture below.

**Read More: ****How to Make a Yield to Maturity Calculator in Excel**

### 2. Utilizing Mathematical Formula

Now, we will use some mathematical formulas to **create treasury bond calculator in Excel**. Let’s say, we have the **Data of Bond Informations** as our dataset. Our goal is to calculate the **Yield to Maturity** and the **Bond Price**. Let’s use the steps outlined below to do this.

__Steps:__

- Firstly, create
**2**new rows as shown in the following picture.

- After that, enter the following formula in cell
**C11**.

`=(C7+((C5-C10)/C8))/((C5+C10)/2)`

Here, cell **C7 **represents the **Annual Interest**, cell **C5 ** refers to the **Face Value** of the bond, cell **C10 **is the **Current Market Value **of the bond, and cell **C8 **indicates the **Maturity Time** of the bond in years.

- Now, press
**ENTER**.

Subsequently, you will have the **Yield to Maturity** of the bond as shown in the image below.

- Following that, use the following formula in cell
**C12**.

`=((C9/(1+C11)^1)+(C9/(1+C11)^2)+(C9/(1+C11)^3)+(C9/(1+C11)^4)+(C9/(1+C11)^5)+(C9/(1+C11)^6)+(C9/(1+C11)^7)+(C9/(1+C11)^8)+(C9/(1+C11)^9)+(C9/(1+C11)^10))`

Here, cell **C9 **refers to the **Coupon Rate**, and cell **C11 **indicates the **Yield to Maturity **of the bond.

- Now, hit
**ENTER**.

Finally, you will have the **Bond Price** as demonstrated in the following picture.

**Read More: ****How to Calculate Face Value of a Bond in Excel (3 Easy Ways)**

## How to Create Zero Coupon Bond Calculator in Excel

While working in Excel, we often need to **calculate zero coupon bond in Excel**. In the zero coupon bond, the **Annual Coupon Rate** becomes **0%**. Let’s say, we have the **Data of Zero Coupon Bond Informations** as our dataset. In this section of the article, we will learn the steps to **create a zero coupon bond calculator in Excel**.

- Firstly, enter the following formula in cell
**C10**.

`=(C7+((C5-C9)/C8))/((C5+C9)/2)`

Here, cell **C7 **represents the **Annual Interest**, cell **C5 **refers to the **Face Value** of the bond, cell **C9 **indicates the **Current Market Value** of the bond, and cell **C8 **is the **Maturity Time** of the bond in years.

- Following that, press
**ENTER**.

Subsequently, you will have the **Yield to Maturity** of the bond as marked in the following picture.

**Read More: ****Calculate Bond Price from Yield in Excel (3 Easy Ways)**

## How to Make Bond Price Calculator in Excel

In this section of the article, we will learn the steps to **make bond price calculator in Excel**. Here, we will use the **PV function** to calculate the **Bond Price**. The **PV **function returns the present value of an investment. Let’s say, we have the **Coupon Bond Price **as our dataset. Our aim is to calculate the **Bond Price** using this data. Let’s follow the steps discussed in the following section.

__Steps:__

- Firstly, create a new row as marked in the image given below.

- After that, enter the following formula in cell
**C10**.

`=-PV(C6,C9,C5*C7,C5)`

Here, cell **C6 **refers to the **Interest Rate**, cell **C9 **indicates the **Number of Periods**, cell **C5 **is the **Face Value** of the bond, and cell **C7 **refers to the **Coupon Rate**.

- Now, hit
**ENTER**.

*Note:**Here, we have used a negative sign before the PV function so that the output of the PV functions becomes a positive number.*

Consequently, you will have the **Bond Price** as demonstrated in the following image.

Additionally, there are **various techniques to determine the Bond Price in Excel**.

**Read More: ****How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)**

## How to Calculate Bond Present Value in Excel

While working in Excel, we often need to **calculate the present value of the bond**. Let’s say we have the **Data for Bond Present Value** as our dataset. Our aim is to calculate the **present value of the bond**. Let’s use the steps outlined below to do this.

__Steps:__

- Firstly, create a new row as marked in the image below.

- Following that, enter the formula given below in cell
**C12**.

`=C10*(1-(1+(C11 /C9))^(-C9*C8))/(C11/C9)+(C5/(1 + (C11/C9))^(C9*C8))`

Here, cell **C10 **refers to the **Coupon Rate** of the bond, cell **C11 **indicates the **Yield to Maturity** for the bond, cell **C9 **is the **Number of Periods per Year**, and cell **C8 **represents the **Maturity Time** of the bond in years.

- Then, hit
**ENTER**.

Finally, you will have the **Present Value** of the bond as shown in cell **C12**.

Furthermore, there are **other techniques you can employ to determine the Bond Present Value in Excel**.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to **make Treasury Bond Calculator in Excel**. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, **ExcelDemy**, a one-stop Excel solution provider. Happy learning!