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 bond values.

## 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

#### 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%.

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)`

- Press
**Enter**to apply the formula.

**Read More:** How to Calculate Face Value of a Bond in Excel

#### 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.

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.

**Read More: **Calculate Bond Price from Yield in Excel

### 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.

**Read More: **How to Calculate Bond Payments in Excel

#### 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)`

- After that, press
**Enter**to apply the formula.

**Read More: **How to Calculate Price of a Semi Annual Coupon 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.

**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:** Calculate Bond Price with Negative Yield in Excel

### 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.

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.

## 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,

## Download Practice Workbook

Download the practice workbook below.

## 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.

## Related Articles

- Calculate Clean Price of a Bond in Excel
- Calculate the Issue Price of a Bond in Excel
- Calculate Duration of a Bond in Excel
- Calculate Floating Rate Bond Valuation in Excel
- How to Calculate Coupon Rate in Excel

**<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel**