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

In this article we will demonstrate 3 different formulas to calculate the face value of a bond in Excel.

Bond and Face Value

A bond is a fixed-income tool used by investors, companies, governments, and business entities to borrow money from the Capital Market. The owners of bonds are called the debtholders, creditors, or bond issuers. The bond price is the present discounted value of the future cash stream generated by a bond, and 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, and reflects how much a bond is worth when it matures, also known as the par value.

3 Handy Approaches to Calculate the Face Value of a Bond in Excel

To demonstrate our methods, we’ll use 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. The following values are provided in order to perform the calculations:

Coupon Bond Price.
Number of Years 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.

Method 1 – Using the Coupon to Calculate the Face Value of a Bond in Excel

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

Our formula looks like this:

Steps:

Enter the following formula in cell C11:

=C10*C7/C9

Press ENTER to return the face value of the bond.

The face value of a bond with a coupon price of $25 and a coupon rate of 5% compounded semi-annually is $1000.

Method 2 – Finding the Face Value from the Bond Price

Now we will derive our formula from the coupon bond price formula, then use that formula to calculate the face value. This time, the coupon price is not directly provided in the example, so our formula looks like this:

Steps:

Enter the following formula in cell C10:

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

Press ENTER.

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.

Method 3 – Calculating the Face Value for a Zero Coupon Bond in Excel

Finally, we will find the face value for a Zero Coupon Bond in Excel. The Annual Coupon Rate is 0% for a Zero Coupon Bond, so our formula is as follows: