To calculate a bond’s price in Excel, use the PV function with the market yield per period, the number of periods, the coupon payment per period, and the face value: for example, =-PV(3%,20,25,1000) returns $925.61 for a $1,000 bond with a 5% coupon paid semiannually, 10 years to maturity, and a 6% market yield. If you have exact settlement and maturity dates, the PRICE function does the same job and handles partial periods.
The Inputs You Need
- Face (par) value: the amount repaid at maturity, usually $1,000.
- Coupon rate: the annual interest rate the bond pays on its face value.
- Market yield (yield to maturity): the annual return investors currently require on similar bonds.
- Years to maturity.
- Payments per year: 2 for most U.S. corporate and Treasury bonds, 1 for many others.
A bond’s price is the present value of all its coupon payments plus the present value of the face value, discounted at the market yield.
Method 1: Calculate Bond Price With the PV Function
- Enter the inputs in a column: B1 face value 1000, B2 coupon rate 5%, B3 market yield 6%, B4 years 10, B5 payments per year 2.
- In B7, enter:
=-PV(B3/B5, B4*B5, B1*B2/B5, B1) - Press Enter. Excel returns 925.61.
Here is what each argument does:
- rate = B3/B5, the yield per period (6% ÷ 2 = 3%).
- nper = B4*B5, the number of periods (10 × 2 = 20).
- pmt = B1*B2/B5, the coupon per period ($1,000 × 5% ÷ 2 = $25).
- fv = B1, the face value repaid at maturity.
PV returns a negative number because Excel treats money you pay (the price) as the opposite sign of money you receive (coupons and face value). The minus sign in front of PV turns the result positive.
For a bond that pays annually, set B5 to 1. The same bond would then be priced at $926.40.
Method 2: Calculate Bond Price With the PRICE Function
PRICE is built for bonds and uses actual dates, so it handles bonds bought between coupon dates. Its syntax is =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]), and it returns the price per $100 of face value.
- Enter the settlement date (when you buy) in B9 and the maturity date in B10, for example 1/1/2026 and 1/1/2036. Use real dates, or the DATE function, such as
=DATE(2026,1,1). - In B11, enter:
=PRICE(B9, B10, B2, B3, 100, B5) - Excel returns 92.56, meaning $92.56 per $100 of face value.
- Multiply by face value ÷ 100 to get the dollar price:
=B11*B1/100returns $925.61.
The optional basis argument sets the day-count convention: 0 or omitted for US 30/360, 1 for actual/actual (used for Treasuries), 2 for actual/360, 3 for actual/365, and 4 for European 30/360.
Method 3: Build the Price From Cash Flows
To see exactly where the price comes from, list each payment and discount it. This is useful for bonds with irregular cash flows or for teaching.
- In A14:A33, number the periods 1 to 20.
- In B14, enter the cash flow:
=IF(A14=20, 25+1000, 25), and fill it down. Every period pays the $25 coupon, and the last also repays the $1,000 face value. - In C14, enter the present value of that payment:
=B14/(1+3%)^A14, and fill it down. - Add the column with
=SUM(C14:C33). The total is $925.61, matching PV and PRICE.
You can also discount the whole column at once with =NPV(3%, B14:B33), which returns the same $925.61, because NPV assumes the first cash flow arrives at the end of the first period.
How Price Changes With Yield
For the same 10-year, 5% semiannual bond, here is the price at different market yields:
- 4% yield: $1,081.76 (premium)
- 5% yield: $1,000.00 (par)
- 6% yield: $925.61 (discount)
- 7% yield: $857.88 (discount)
Prices move in the opposite direction to yields, and longer-maturity bonds move more for the same change in yield.
Premium, Discount, and Par
Compare the coupon rate with the market yield to check that your answer makes sense:
- Coupon below the yield (5% vs 6%): the bond trades at a discount, below $1,000, like the $925.61 above.
- Coupon equal to the yield: the bond trades at par. With a 5% yield, the formula returns exactly $1,000.
- Coupon above the yield: the bond trades at a premium, above $1,000.
To see how price responds to different yields, build a one-variable data table with yields down a column; see how to create a sensitivity table in Excel.
Zero-Coupon Bonds
A zero-coupon bond pays no interest, so set pmt to 0: =-PV(6%,10,0,1000) returns $558.39 for a 10-year zero with a 6% annual yield. This is the same as =1000/(1+6%)^10. The discounting idea is the same one behind compound interest in Excel.
Going the Other Way: Yield From Price
If you know the price and want the yield, use =YIELD(settlement, maturity, rate, pr, redemption, frequency) with the price per $100, or =RATE(nper, pmt, -price, fv)*frequency for a period-based estimate. For example, =RATE(20,25,-925.61,1000)*2 returns about 6%.
Troubleshooting
The result is negative
That is normal for PV. Put a minus sign in front of the function, or enter the price-related arguments as negative values.
#NUM! from PRICE
The settlement date is on or after maturity, the frequency is not 1, 2, or 4, or the rate or yield is negative. Check each argument.
#VALUE! from PRICE
The dates are stored as text. Re-enter them as dates or use the DATE function. Our guide on calculating months between two dates explains how Excel stores dates.
The price looks wrong by a lot
The most common mistake is mixing annual and per-period values. With semiannual payments, divide the yield and coupon by 2 and double the number of years.
Frequently Asked Questions
What is the PV function in Excel?
PV returns the present value of a series of equal payments plus an optional lump sum, discounted at a constant rate. Its syntax is =PV(rate, nper, pmt, [fv], [type]).
How do I adjust for semiannual coupon payments?
Divide the annual yield and the annual coupon by 2, and multiply the years by 2. In PRICE, set frequency to 2.
Should I use PV or PRICE?
Use PV for quick estimates and homework-style problems with whole periods. Use PRICE when you have actual dates, because it accounts for accrued interest timing and day-count conventions.
Does PRICE include accrued interest?
No. PRICE returns the clean price. Add accrued interest (calculated with ACCRINT) to get the dirty or invoice price.
Can I automate bond pricing for many bonds?
Yes. Put each bond in its own row with the inputs in columns, write the formula once, and fill it down.
Related: How to Run Descriptive Statistics in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.