What is the Excel YIELD Function?
The YIELD Function in Excel calculates the return earned on a security that issues periodic interest payments, such as a bond.
How to Use YIELD Function (Step-by-Step)
The Excel “YIELD” function is most often used in practice to determine the yield on a bond or a related debt instrument.
As part of a financing arrangement, the borrower is obligated to pay periodic interest payments to the lender until the date of maturity, in which the remaining balance of the principal must be repaid in full.
Before the terms of the lending agreement are formalized, i.e. prior to the settlement date, the yield earned on a bond by the lender can be estimated. However, there is substantial room for variance between the expected and the actual return.
Therefore, lenders must rely on multiple bond yield metrics, rather than merely one, to measure their potential returns under different scenarios.
Bond Terminology Review
Before delving further into the Excel “YIELD” function, it is necessary to preface the section with a review of common bond terminology to not only understand the calculation itself, but also to reduce the chance for misinterpretations.
|Par Value (or Face Value)||
|Par Bond vs. Discount Bond vs. Premium Bond||
|Coupon Rate (or Interest Rate)||
|Yield to Maturity (YTM)||
|Yield to Call (YTC)||
|Yield to Worst (YTW)||
|Callable Bond vs. Non-Callable Bond||
Excel YIELD Function Formula
The formula for using the YIELD function in Excel is as follows.
- “settlement” = Settlement Date
- “maturity” = Original Maturity Date or Early Redemption Date
- “rate” = Annual Coupon Rate (%)
- “pr” = Bond Quote (% of Par)
- “redemption” = Par Value or Call Price
- “frequency” = Number of Compounding Periods (e.g. Annual = 1x, Semi-Annual = 2x, Quarterly = 4x)
- “basis” = Day Count Basis
The brackets around the final input, “basis”, denotes that it can be omitted, i.e. left blank.
In order for the implied yield to be accurate, the units of time used in the equation must remain consistent all throughout the calculation.
For instance, if a borrower has taken out a ten-year bond with an annual interest rate of 10% paid on a semi-annual basis, the time-adjusted (i.e. semi-annual) interest rate on the bond is 5.0%.
- Semi-Annual Interest Rate (rate) = 10% ÷ 2 = 5.0%
YIELD Function Excel Syntax
The table below describes the syntax of the Excel YIELD function in more detail.
YIELD Function Calculator – Excel Model Template
We’ll now move on to a modeling exercise, which you can access by filling out the form below.
Step 1. Bond Yield Calculation Exercise Assumptions
Suppose we’re tasked with calculating the yield on a ten-year semi-annual corporate bond given the following set of assumptions:
- Settlement Date = 12/31/21
- Term to Maturity = 10 Years
- Maturity Date = 12/31/31
The structure of the corporate bond issuance has a stated maturity of ten years and is not callable for the first five years of the borrowing term (i.e. NC/5).
Step 2. Call Provision Assumptions
The first call date is therefore five years from the date of original issuance, with a call premium of 4.0%.
- Call Provision = NC/5
- First Call Date = 12/31/26
- Call Premium (%) = 4%
- Call Price = 104
The bond was issued at par, i.e. “100”, and is trading in the markets at the same price as of the present date.
Step 3. Frequency of Payment and Coupon Rate Assumptions
The final two assumptions left are the frequency of payment and the coupon rate (%).
Earlier we stated the issuance was a semi-annual bond, so the frequency of payment is 2 times per annum.
The annual coupon rate that we’ll assume is 6.5%, meaning that the annual coupon is $65
- Frequency of Payment = 2x
Annual Interest Rate = 6.5%
The primary factors that determine the yield on bonds are the par value, coupon rate (%), the coupon ($), and the borrowing term.
Furthermore, the price of a bond and the yield are inversely related, wherein if the price of a bond were to rise, its yield would decline (and vice versa).
Step 4. Bond Yield Calculation in Excel
The inputs entered in our YIELD function formula to compute the yield to maturity (YTM) are as follows.
The implied yield to maturity (YTM), i.e. the return if held until the date of maturity, is 6.5% on the bond.
In contrast, the formula to calculate the yield to call (YTC) is the following.
The distinction of YTC from the prior YTM calculation, which has been formatted in italics, is the maturity date and the call price:
- Maturity Date → The bondholder is assumed to have called the bond earlier than anticipated on the first call date and paid a premium.
- Call Price → The call price becomes relevant only for callable bonds, in which a call premium (i.e. fee) is attached to compensate the lender for the loss of the future interest payments and the risk of needing to reinvest the capital.
Using the YIELD function in Excel, we arrived at an implied yield to maturity (YTM) is 6.5%, whereas the yield to call (YTC) was 7.2% in comparison.
The bondholder in our hypothetical scenario—assuming that earning a higher return is the priority—is likely to call the bond early because of the higher yield.