How to Make Treasury Bond Calculator in Excel (2 Easy Ways)

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.


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.

treasury bond calculator excel

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.

Using YIELDDISC function to make treasury bond calculator in Excel

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.

Using PRICEDISC function to make treasury bond calculator in Excel

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.

Utilizing Mathematical Formula to make treasury bond calculator in Excel

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.

Final output of method 2 to make treasury bond calculator in Excel

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.

Dataset to Create 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.

Final output of method 3 to create zero coupon bond calculator in Excel

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.

Dataset to Make Bond Price Calculator in Excel

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.

Using PV function to Make Bond Price Calculator in Excel

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.

Dataset to Calculate Bond Present Value in Excel

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.

Final output of method 5 to Calculate Bond Present Value in Excel

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.

Practice section to make treasury bond calculator in Excel


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!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo