Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

In Microsoft Excel, you can easily calculate the present value of a bond. Basically, Excel provides you with several ways to calculate the present value of a bond including the PV function, PRICE function, or any conventional formula. The present value of a bond can be defined as the value after including all types of interest. This article will basically show you how to calculate the present value of a bond in Excel. I hope you find this article very informative and gain lots of knowledge regarding the bond values.


Download Practice Workbook

Download the practice workbook below.


What Is Bond?

A bond is a type of security under which the issuer owes the holder a debt. It is a security that obliges the issue to make specific interest and principal payments to the holders on the maturity date. This is an interest-only loan where the issuer can pay interest every period but the holder can’t take the principal until maturity. Government, companies, and business entities use bonds to raise funds from the capital market.


3 Easy Methods to Calculate Present Value of a Bond in Excel

To calculate the present value of a bond in Excel, we have found three different methods through which we can efficiently perform this calculation. We try to solve this problem using the conventional formula, PV function, and PRICE function. In general, the PV function is the widely used function to calculate the present value of a bond in Excel.


1. Applying Conventional Formula

Our first method is basically by using the conventional formula to calculate the present value of a bond. The present value can be calculated for a coupon bond or a zero-coupon bond. The conventional formula to calculate the present value for a coupon bond is

Then, the conventional formula to calculate the present value for a zero-coupon bond is

Here,

F= Face Value

C= Annual Coupon Rate

r = Yield to Maturity

n = Number of Compounding Per Year

t = Number of Years Until Maturity

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


1.1 For Zero Coupon Bond

The zero-coupon bond can be defined as a bond that doesn’t pay interest during the life of that bond. The investor buys the zero-coupon bond at a deep interest from its face value whereas, after maturity, the investor will sell it at its face value. To calculate the present value of a zero-coupon bond, we take a dataset that includes face value, Number of Years Until Maturity, Yield to Maturity, and Number of Compounding Per Year. Now, set the coupon rate to 0%.

How to Calculate Present Value of a Bond in Excel

Follow the steps carefully to calculate the present value of the zero-coupon bond.

 Steps

  • At first, select cell C10.

  • Then, write down the following formula in the formula box.
=C5/(1+(C8/C7))^(C7*C6)

How to Calculate Present Value of a Bond in Excel

  • Press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel

Read More: Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)


1.2 For Coupon Bond

In terms of a coupon bond, you must have a coupon rate. The investor will give the buyers a specific rate of interest over a period of time. The bondholder will receive interest until the date of maturity.  To calculate the present value of a zero-coupon bond, we take a dataset that includes face value, Number of Years Until Maturity, Yield to Maturity, Number of Compounding Per Year, and coupon rate.

How to Calculate Present Value of a Bond in Excel

Follow the steps carefully to calculate the present value for the coupon bond.

Steps

  • At first, select cell C11.

  • Then, write down the following formula in the formula box.
=C10*(1-(1+(C8 /C7))^(-C7*C6 ))/(C8/C7)+(C5/(1 + (C8/C7))^(C7*C6))

  • After that, press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel

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


2. Using PV Function

Our most effective method is to use the PV function to calculate the present value of a bond. The PV function returns the present value of the investment.

We use the PV function to calculate the present value of a bond for three different cases. One is basically for zero-coupon bonds and the other two are annual and semi-annual coupon bonds.


2.1 For Annual Coupon Bond

At first, we use the PV function for an annual coupon bond.  In terms of an annual coupon bond, bondholders will receive yearly interest. Here, the payment incurred in each payment is the product of face value and coupon rate. To calculate the present value of the annual coupon bond, you need to follow the steps carefully.

Steps

  • At first, select cell C9.

  • Then, write down the following formula
=PV(C7,C6,C5*C8,C5)

  • After that, press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel

Read More: How to Calculate Bond Payments in Excel (2 Easy Methods)


2.2 For Semi-Annual Coupon Bond

When it comes to a semi-annual coupon bond, we need to do some minimal adjustments to calculate the present value of a semi-annual coupon bond. As it is semi-annual, so the interest rate would be half of the annual interest rate. The payment incurred in each payment would also be half of the annual interest rate. Otherwise, all of the others remain the same.

Steps

  • At first, select cell C9.

  • Then, write down the following formula.
=PV(C7/2,C6*2,C5*C8/2,C5)

How to Calculate Present Value of a Bond in Excel

  • After that, press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel


2.3 For Zero Coupon Bond

When we use the PV function to calculate the present value for a zero-coupon bond, we have to set the coupon rate as zero. For that reason, the payment incurred in each payment will become zero. Because we know the payment incurred in each payment is the product of face value and coupon rate. To calculate the present value for a zero-coupon bond, you need to follow the steps

Steps

  • At first, select cell C10.

  • Then, write down the following formula.
=PV(C8,C7,0,C5)

  • After that, press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel

Note

In every case of the PV function, you will get a negative value of the bond value. This actually indicates the present cash flow or expenditure.

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


3. Using PRICE Function

Our last method is by using the PRICE function to calculate the present value of a bond. The PRICE function returns the price of a par value (i.e. $200) of investment which pays successive interest. We take a dataset that includes settlement, maturity date, coupon rate, yield, redemption amount per $200 face value, and the number of coupon payments per year which means frequency.

How to Calculate Present Value of a Bond in Excel

Follow the following steps to calculate the present value of a bond using the PRICE function.

Steps

  • At first, select cell C12.

  • Write down the following formula in the formula box.
=PRICE(C5,C6,C7,C8,C9,C10,C11)

  • Then, press Enter to apply the formula.

How to Calculate Present Value of a Bond in Excel


Things to Remember

  • While using the conventional method, make sure all the parenthesis are applied accurately. Otherwise, it produces a different result.
  • In terms of a zero-coupon bond, set the coupon rate as zero.
  • The present value of a bond using the PV function will be negative. Don’t bother about that because it shows the present cash flow,

Conclusion

We have shown three different methods through which you can easily calculate the present value of a bond in Excel. All of the methods are very effective and user-friendly. The PV function is the best of the three methods. This is a widely used function in terms of calculating present value. I hope you find this article very interesting. If you have questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page to gain further Excel-related knowledge.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo