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

Get FREE Advanced Excel Exercises with Solutions!

Bond yield is the rate of return of an investment made to purchase a bond. The ratio between the Coupon/Interest Payment and the Bond Price is typically referred to as Yield. Users mostly deal with bonds and occasionally need to calculate bond prices from yield in Excel.

Let’s say, we have the following bond particulars and want to calculate bond price from yield in Excel.

Dataset-Calculate Bond Price from Yield in Excel

In this article, we use the Coupon Bond Price Formula, the FV function, and the PRICE functions to calculate bond price from yield in Excel.


Relation between Bond Price and Yield

The relation between bond price and yield is inversely proportional. If the bond price increases, bond yield (the rate of return) will fall and vice versa. The simplified depiction of their relationship is

Bond Yield Formula-Calculate Bond Price from Yield in Excel


3 Easy Methods to Calculate Bond Price from Yield in Excel

Method 1: Calculating Bond Price from Yield Using Formula

The coupon bond price formula can be used to find the bond price. The typical Coupon Bond Price formula is

Coupon Bond Price Formula-Calculate Bond Price from Yield in Excel

In the formula, the r is the Yield to Maturity.


🔄 Coupon Bond Price Calculation from Yield

As mentioned earlier, you can calculate the bond price using the conventional formula. Use the below formula in the C10 cell to find the Coupon Bond Price.

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

Coupon Bond Price-Calculate Bond Price from Yield in Excel

Use the ENTER key to display the Coupon Bond Price.

Price

Read More: Calculate the Issue Price of a Bond in Excel


Method 2: Using FV Function to Calculate Bond Price from Yield in Excel

Alternative to the conventional formula, users can calculate different bond prices using the PV function. The PV function returns the present value of the investment. The syntax of the PV function is:

PV (rate, nper, pmt, [fv], [type])

In the syntax, the arguments are:

rate; the discount/interest rate.

nper; the number of payments during the period.

pmt; payment incurred in each payment.

fv; the future value. Default value is zero (0). [Optional]

type; the payment type, For, end of period = 0, beginning of period = 1. Default is zero (0). [Optional]

The Coupon Rate can be referred to as Nominal Yield. So, in the calculation, we take the Coupon Rate as the Nominal Yield.


🔄 Annual Coupon Bond Price

Type the following formula in cell C9.

=PV(C7,C6,C4*C8,C4)

In the formula, rate = C7, nper= C6, pmt = C4*C8, [fv] =C4.

PV Formula-Calculate Bond Price from Yield in Excel


🔄 Semi-Annual Coupon Bond Price

In cell C9 insert the following formula.

=PV(C7/2,C6,C4*C8/2,C4)

In the formula, rate = C7/2 (as it’s a calculation of semi-annual coupon bond price), nper= C6, pmt = C4*C8/2, [fv] =C4.

Read More: How to Calculate Bond Payments in Excel


Method 3: Calculating Bond Price Using the PRICE Function

The PRICE function returns the price of a par value (i.e., $100 or others) of investment that pays a successive interest. The syntax of the PRICE function is:


PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

 ➤ Use the following formula in any blank cell (i.e., C11).

 =PRICE(C4,C5,C6,C7,C8,C9,C10)

PRICE function-Calculate Bond Price from Yield in Excel

➤ Hit the ENTER key to display the Bond Price as depicted in the below image.

PRICE function Outcome

Read More: Determine Yield to Maturity in Excel


Download Excel Workbook


Conclusion

In this article, we discuss the relationship between bond price and its yield and ways to calculate the bond price from yield in Excel. Users can use the attached practice workbook as a Bond Price Calculator. Hope this article clarifies your understanding of bond and bond yield. Comment if there are any further inquiries needed or have anything to add.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo