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
Get FREE Advanced Excel Exercises with Solutions!
