Investors often need to do bond valuations to analyze and understand the worth of a bond. In Excel, there are a lot of formulas and functions that you can use to do bond valuations. Although calculating bond price is a complex calculation, Excel makes it easy, and you can automate it with some formulas. In this article, we will learn all the bond valuation formulas in Excel and show you how to calculate the prices of your bonds.
➤ To calculate the value of a bond, use the following formula:
=-PV(B5,B4,B2*B3,B2)
➤ Replace B5 with the discount rate, B4 with the payment period, B2 with the face value, and B3 with the coupon rate.
That was one way to calculate the bond price, but bond valuation includes a lot of other calculations that you would want to know to learn about bonds. This article provides a comprehensive overview of bond valuations, so grab the provided Excel file and follow along.
Calculating Coupon Bond Price
These bonds provide interest based on annuities, and there are several ways to calculate it. We will go through them one by one. We have a dataset here with the face value, coupon rate, years to maturity, and the discount rate. We will use these data to calculate the bond price.
Method 1: Using the Manual Calculation for Annual Annuity
First, let’s learn the manual calculation so that the concept is clear, then you can use the functions provided by Excel itself.
➤ First, add another row to calculate the Coupon Payment.
➤ Enter the following formula in the B6 cell:
=B2*B3
By applying the coupon rate to the face value, we get the coupon payment amount.
➤ Calculate the bond price in the B7 cell using the following formula:
=B6*(1-(1+B5)^-B4)/B5+B2/(1+B5)^B4
The next part (B2/(1+B5)^B4) calculates the PV of the face value. Here, B2 is the face value, and we already know B4 and B5. By adding those values, we get the bond price.
Method 2: Making Use of the PV Function to Calculate the Bond Price
Now that you know how to calculate the bond price, let’s simplify it using the PV function provided by Excel.
➤ Since we already have the coupon payment calculated, we will be using those for our calculation here.
➤ Insert the following formula in the B7 cell:
=-PV(B5,B4,B6,B2)
The final parameter is the future value, which is the face value in this case, from B2. We put a subtraction sign at the beginning because Excel outputs the PV result in a negative value; we need to make it positive first.
Method 3: Calculating the Biannual/Quarterly Annuity in Excel
When you know how to calculate the annual annuity, it is fairly easy to calculate the biannual/quarterly annuity. We just need to edit the formula a bit, and we will be good to go.
➤ In the case of biannual, there will be 2 payments per year. For quarterly payments, the payment count will be 4. Add another row, and write that payment count there. We are writing 2 in the B7 cell for the biannual count.
➤ Now, calculate the bond price in the B8 cell using the following formula:
=-PV(B5/B7,B4*B7,B6/B7,B2)
Note:
The formula is almost the same as before. However, we had to divide the discount rate and the coupon payment by the payment count, as those will be divided into similar portions for the whole year. The years to maturity were used as the number of payments before, so we multiplied them by the payment count per year to get the new number of payments.
Method 4: Computing the Bond Price by Continuous Compounding
Investors like to reinvest. If you are thinking of reinvesting your bond again and again, you need to find out the bond price to understand the limit at which you stop making a profit. Here is how to calculate the bond price with continuous compounding:
➤ Insert the following formula in the B7 cell:
=B6*(1-EXP(-B5*B4))/B5 + B2*EXP(-B5*B4)
Finding the Bond Price of Zero-Coupon Bonds in Excel
Calculating the zero-coupon bonds is easier than calculating regular bonds. As there are no coupons here, the calculation is very limited. In the table, we have the face value, interest rate, and the years to maturity. This table will be used to find the bond price of zero-coupon bonds in Excel.
Method 1: Using the PV Function
The simplest method is to use the PV function to calculate the bond price. Follow the instructions below:
➤ Insert the following formula in the B5 cell to calculate the bond price:
=PV(B3,B4,0,-B2)
Method 2: The Continuous Zero-Coupon Bond Calculation
This time, we don’t need the PV function to calculate the price. Instead, a simple mathematical formula will suffice.
➤ Insert the formula in the B5 cell:
=B2*EXP(-B3*B4)
Figuring Out the Bond Price from Specific Dates
Sometimes, instead of the year count, we have dates of settlements and maturity. We can find the bond price using those as well. Follow the methods below:
Method 1: Applying the PRICE Function
Excel provides us with a function to calculate the price at ease. In the following table, we have the settlement date, maturity data, coupon rate, yield to maturity, redemption value, and coupon frequency. We will use these values to calculate the bond price.
➤ Insert the following formula in the B8 cell:
=PRICE(B2,B3,B4,B5,B6,B7)
Method 2: Calculating the Dirty Price of the Bond
The dirty price of a bond includes the accrued interest since the last coupon payment. To calculate this, we have a table with the settlement and maturity date, coupon rate, yield to maturity, redemption value, and the bond price. We will calculate the annual coupon, accrued interest, and the dirty price.
➤ Calculate the annual coupon in the B8 cell using the following formula:
=B6*B4
By multiplying the redemption value by the coupon rate, we get the annual coupon price.
➤ To calculate the accrued interest, use the following formula:
=ABS(TODAY()-DATE(YEAR(TODAY())- 1, MONTH(B2),DAY(B2)))
Then, we subtract that date from today’s date to count the days passed. Finally, the ABS function is used to obtain the absolute value, ensuring that even if you open the workbook after the maturity date, you don’t receive a negative value.
➤ Finally, calculate the dirty price using the following formula:
=B7+B9
Note:
This is nothing complex; we are adding the accrued interest to the clean bond price.
Determining the Discount Rate/YTM with the RATE Function
So far, we have only calculated the bond price. However, there are more parts to bond valuation, such as calculating the YTM Rate. To calculate that, we have a table with face value, coupon rate, years to maturity, coupon payment, and bond price. Here’s how to calculate the YTM:
➤ Use the following formula in the B7 cell:
=RATE(B4,B5,-B6,B2)
Projecting the Payment Period Using the NPER and ROUNDUP Functions
This time, we have the face value, coupon rate, coupon payment, bond price, and the discount rate. We have to calculate the payment periods, also known as the years to maturity.
➤ Insert the formula in the B7 cell:
=ROUND(NPER(B6,B4,-B5,B2),0)
Frequently Asked Questions
What is the FV of a bond in Excel?
Although FV is usually called the future value, and the function is the same in Excel as well, that is not what it’s called for bonds. For bonds, FV is the face value, the price the bond will be worth after a certain period of time.
What is nper in Excel?
The NPER function in Excel calculates the number of periods required to pay a loan or get an investment back. The minimum parameters for this function are the interest rate, payment per period, and the present value/amount of the loan.
What is a valuation calculator?
A valuation calculator helps investors understand the value of their investment using different financial metrics. Investment websites usually include valuation calculators for new investors. Using Excel, you can create your own valuation calculator customized for your investments.
Is the FV of a bond always 1000?
Most corporate bonds have a face value of $1000. However, it can differ depending on the market and other variables. Therefore, the FV of a bond is not always $1000.
What is the 7% rule in stocks?
The rule indicates that if the price of your stock drops by 7% below the price you paid for it, you need to sell the stock immediately. If you don’t, you are carrying a big risk on your stocks, and you should reconsider the investment choices.
Wrapping Up
In this article, we have learned the bond valuation formulas in Excel. No matter what type of bond valuation you want to do, if you have read this article, you know how to calculate it. Leave your questions regarding bond valuation in the comment section below, and we will see you in another article.
