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.

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.

**Table of Contents**Expand

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

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

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

** **

âž¤ Use the **ENTER** key to display the Coupon Bond 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**.

**ðŸ”„ 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)`

** **

âž¤ Hit the **ENTER** key to display the Bond Price as depicted in the below image.

**Read More: **Determine Yield to Maturity in Excel

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

- Calculate Duration of a Bond in Excel
- How to Calculate Present Value of a Bond in Excel
- How to Calculate Clean Price of a Bond in Excel
- Calculate Face Value of a Bond in Excel
- How to Calculate Coupon Rate in Excel
- Calculate Bond Price with Negative Yield in Excel
- Compute Floating Rate Bond Valuation in Excel

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