Understanding the True Value of a Bond
When you purchase a corporate or government bond, you are essentially lending money in exchange for regular interest payments (coupon payments) over a set period, followed by the return of your initial principal when the bond matures.
If you buy a bond brand new at its exact face value and hold it to maturity, calculating your return is simple math. However, the bond market is highly active. Most investors buy bonds “secondary”—meaning the bond has already been issued, and its price fluctuates based on current market interest rates.
If you buy a $1,000 bond at a discount for $950, your overall return is going to be higher than the stated coupon rate because you paid less for it. This true, comprehensive measure of profitability is called the Yield to Maturity (YTM).
Instead of performing complex financial calculus by hand, you can use Excel’s YIELD function to calculate exactly what percentage return a bond will generate based on its current market price.
Step 1: Understanding the YIELD Syntax
The YIELD function requires several specific data points regarding the bond’s lifespan and pricing.
The Syntax:
=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
- settlement: The exact date you purchased the bond.
- maturity: The exact date the bond expires and pays out the principal.
- rate: The bond’s stated annual coupon interest rate.
- pr: The current market price of the bond per $100 of face value.
- redemption: The amount paid out at maturity per $100 of face value (almost always 100).
- frequency: How often the coupon payments are made (1 for annual, 2 for semi-annual, 4 for quarterly).
Step 2: Preparing the Data in Excel
Because the YIELD function relies heavily on exact dates, typing them directly into the formula can cause errors if Excel misunderstands your regional date format. It is much safer to reference specific cells.
Set up a spreadsheet with the following data for a hypothetical bond:
- B1 (Settlement Date): 11/15/2023 (The day you bought it)
- B2 (Maturity Date): 11/15/2033 (A 10-year lifespan)
- B3 (Coupon Rate): 5% (The stated interest rate)
- B4 (Price): 92.50 (You bought it at a discount; $925 for a $1,000 bond)
- B5 (Redemption): 100 (The bond will pay out its full face value at the end)
- B6 (Frequency): 2 (The bond pays interest twice a year)
Step 3: Calculating the Yield
Now that the data is neatly organized, you can execute the formula to determine the true Yield to Maturity.
In an empty cell, enter the following formula:
=YIELD(B1, B2, B3, B4, B5, B6)
The Result: Excel will likely output a decimal, such as 0.0601.
Highlight the cell and click the % (Percent Style) button on the Home ribbon to format it properly. The result is 6.01%.
Notice that even though the bond’s stated coupon rate is only 5%, your true Yield to Maturity is 6.01% because you bought the bond at a steep discount (for $92.50 instead of $100).
Step 4: What if You Buy at a Premium?
If market interest rates fall, older bonds paying higher rates become more valuable, and their price goes up. You might have to pay a premium to buy the bond.
Change cell B4 (Price) from 92.50 to 105.00 (meaning you paid $1,050 for a $1,000 bond).
The YIELD formula will automatically recalculate. The result will drop to 4.37%. Because you overpaid for the bond upfront, your true long-term profitability is lower than the stated 5% coupon rate.
Step 5: The Basis Argument (Advanced Accuracy)
You may notice the YIELD syntax has a final, optional argument called [basis]. This determines exactly how Excel counts the days in a year, which varies depending on the type of bond (e.g., US Treasuries versus corporate bonds).
- 0 (or omitted): US (NASD) 30/360 – Assumes 30 days a month, 360 days a year. Standard for most corporate and municipal bonds.
- 1: Actual/Actual – Counts the exact number of days. Standard for US Treasury bonds.
If you are calculating the yield of a US Treasury Bond, your formula should include a 1 at the very end to ensure penny-accurate regulatory compliance:
=YIELD(B1, B2, B3, B4, B5, B6, 1)