The Complexity of Trading Bonds
If you buy a share of a company’s stock, understanding its value is incredibly simple: you look at the stock market ticker, and it tells you exactly what one share is worth right now. Bonds, however, do not work like stocks. When you buy a corporate or government bond, you are buying a complex financial contract that guarantees future cash payments (coupon payments) and a final lump-sum return (the face value) on a specific date in the future.
Because the bond guarantees these future payouts, its actual value today is constantly fluctuating based on current market interest rates. If you want to sell your bond to another investor before it matures, how do you mathematically calculate exactly what they should pay you for it today?
You cannot just guess a number. You must calculate the exact present value of all those future guaranteed payments, discounted by the current market interest rate. This requires advanced calculus. To instantly perform this massive mathematical valuation and find the exact, fair market price of any bond, you must use the PRICE function in Microsoft Excel.
Understanding the Syntax
The PRICE function requires you to feed it six mandatory pieces of data directly from the bond’s official contract, plus one variable regarding current market conditions.
=PRICE(settlement, maturity, rate, yld, redemption, frequency)
- settlement: The exact date the buyer will physically hand you the money to purchase the bond from you.
- maturity: The exact date the bond officially expires and the government/corporation pays out the final lump sum.
- rate (Coupon Rate): The annual interest rate the bond guarantees to pay out on its official contract (e.g., a 5% bond).
- yld (Yield): The current, actual interest rate happening in the market today (this is what drives the price up or down).
- redemption: The final payout amount per $100 of face value (almost always
100). - frequency: How many times a year the bond pays interest (
1for annual,2for semi-annual,4for quarterly).
Example 1: Calculating a Premium Bond
Assume you own a highly lucrative corporate bond. The bond guarantees a massive 8% annual payout (the Coupon Rate). It expires on December 31, 2030.
You want to sell it to a friend today (let’s assume today’s settlement date is January 15, 2024). The bond pays interest twice a year (semi-annual). The current market interest rate (the Yield) for similar investments has crashed down to a miserable 3%.
Because your bond is paying a massive 8% while the rest of the market is only paying 3%, your bond is incredibly valuable. Your friend must pay you a “premium” (a higher price) to acquire it.
Let’s map out the variables in your spreadsheet. (Always use the DATE function to prevent formatting errors).
- Cell A1 (Settlement):
=DATE(2024, 1, 15) - Cell A2 (Maturity):
=DATE(2030, 12, 31) - Cell A3 (Coupon Rate):
8% - Cell A4 (Market Yield):
3% - Cell A5 (Redemption):
100 - Cell A6 (Frequency):
2
To calculate the exact fair market price, click on cell B1 and type:
=PRICE(A1, A2, A3, A4, A5, A6)
How this works:
- Excel looks at the massive gap between your 8% bond and the weak 3% market.
- It calculates exactly how many semi-annual payments are left between 2024 and 2030.
- It instantly outputs 131.54.
Interpreting the Output
The number 131.54 is not the total price. Like all professional bond pricing, the output is quoted per $100 of face value.
If the bond you are selling has a massive total face value of $10,000, you must mathematically convert Excel’s output to find the final cash price. You simply take the 131.54, divide it by 100, and multiply it by $10,000.
The result is $13,154. Your friend must hand you exactly $13,154 in cash today to buy your $10,000 bond, because the guaranteed future payouts are mathematically worth the premium. By mastering the PRICE function, you gain the ability to instantly and fairly value any fixed-income asset in a rapidly changing economy.