How to Calculate Face Value of a Bond in Excel (3 Easy Ways)

Looking for ways to calculate the face value of a bond in Excel? Then this is the right article for you. We will show you 3 different formulas to calculate the face value of a bond in Excel.


Download Practice Workbook


Bond and Face Value

A fixed-income tool used by investors to borrow money from the Capital Market is called a Bond. Companies, governments, and business entities use bonds to raise funds from the Capital Market. The owners of bonds are the debtholders, creditors, or bond issuers. Therefore, the bond price is the present discounted value of the future cash stream generated by a bond. It refers to the accumulation of all likely Coupon payments and the present value of the par value at maturity.

The principal amount of the bond is called the face value of the bond. This reflects how much a bond is worth when it matures. This is also known as the par value.


3 Handy Approaches to Calculate Face Value of Bond in Excel

To demonstrate our methods, we have picked a dataset with 2 columns: “Bond Particulars” and “Value”. For the first 2 methods, we will find the face value of a Coupon Bond and for the last method, we will find the face value of a Zero Coupon Bond. Moreover, we have these values given to us beforehand:

  • Coupon Bond Price.
  • Number of Year Until Maturity (t).
  • The number of the Compounding Per Year (n).
  • Yield to Maturity-YTM (r).
  • Annual Coupon Rate. For Zero Coupon Bond, this value will be zero (0%).
  • Coupon (c).

Using these values, we will find the face value of a Bond in Excel.

How to Calculate Face Value of a Bond in Excel 1


1. Using Coupon to Calculate Face Value of a Bond in Excel

For the first method, we will use the multiplication of the coupon (c) by the number of compounding per year (n), and then divide it by the Annual Coupon Rate to calculate the face value of a bond.

Our formula will look like this.

How to Calculate Face Value of a Bond in Excel Formula 1

Steps:

  • To begin with, type the following formula in cell C11.

=C10*C7/C9

How to Calculate Face Value of a Bond in Excel 3

  • Lastly, press ENTER and we will get the face value of the bond.

How to Calculate Face Value of a Bond in Excel

We have calculated that the face value of a bond with a coupon price of $25, the coupon rate of 5% compounded semi-annually is $1000.

Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel (2 Ways)


2. Finding Face Value from Bond Price

For the second method, we will derive our formula from the coupon bond price formula, and using that we will calculate the face value. Our formula looks like this. This time, the coupon price is not directly provided in the example.

How to Calculate Face Value of a Bond in Excel Formula 2

Steps:

  • First, type the following formula in cell C10.

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

How to Calculate Face Value of a Bond in Excel 6

  • Then, press ENTER.

Sample Dataset 1

We have calculated that the face value of a bond with a price of $1081.76, t = 10 years, n = 2, r = 4%, and an annual coupon rate = 5% is $1000.

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


3. Calculating Face Value for Zero Coupon Bond in Excel

For the last method, we will find the face value for a Zero Coupon Bond in Excel. We will use the following formula. Remember, the Annual Coupon Rate is 0% for a Zero Coupon Bond.

How to Calculate Face Value of a Bond in Excel Formula 3

Steps:

  • Firstly, type this formula in cell C10.

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

Sample Dataset 2

  • Then, press ENTER.

Sample Dataset 3

So, with a Zero Coupon Bond price of $1345.94, t = 10 years, n = 2, r = 4%, the face value will be  $2000.

Read More: How to Calculate the Issue Price of a Bond in Excel


Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

How to Calculate Face Value of a Bond in Excel Practice Dataset


Conclusion

We have shown you 3 formulas to calculate the face value of a bond in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo