The Excel YIELD function has the syntax shown below.
YIELD(Settlement, Maturity, Rate, Pr, Redemption, Frequency, Basis)
Settlement = Settlement date
Maturity = Maturity date
Rate = Annual coupon rate
Pr = Security price per 100 face value
Redemption = Security redemption value per 100 face value
Frequency = Number of coupon payments per year
Basis = Day count basis
Use of the Excel YIELD Function
The Excel YIELD function is used to calculate bond yield to maturity.
Excel YIELD Function Example
Suppose for example, the current price of a bond is 952.13. Assuming that the 3 year bond was issued for settlement on 25 October 2018 with a face value of 1,000, and coupon rate of 6% paid every 6 months. The Excel YIELD to maturity function can be used to calculate the yield to maturity on the bond as follows:
Settlement = 25 October 2018 Maturity = 25 October 2021 (3 years) Rate = 6% Pr = 95.213 (per 100 face value) Redemption = 100.000 (per 100 face value) Frequency = 2 (every 6 months) Basis = 0 (US (NASD) 30/360 basis) Yield to maturity = YIELD(Settlement, Maturity, Rate, Pr, Redemption, Frequency, Basis) Yield to maturity = YIELD(DATE(2018,10,25), DATE(2021,10,25), 6%, 95.213, 100, 2, 0) Yield to maturity = 7.82%
The yield to maturity on this bond is 7.82%
Things to note about the Excel Yield Function
Settlement and Maturity Dates
The Excel YIELD function does not like the use of text in dates. It is safer to enter the date argument using the Excel DATE function as shown above. For example, the date 25 October 2018 is entered as DATE(2018,10,25).
Price and Redemption Values
The price (Pr) and the redemption value (Redemption) are for every 100 face value. In this example the bond price was 952.13 for 1,000 face value so the Pr argument becomes 95.213 per 100 face value. The redemption value was 1,000 for 1,000 face value and so the redemption argument is 100 per 100 face value
Frequency of Coupon Payments
The frequency is the number of coupon payments in a year. In the example above this was every 6 months and so the frequency argument is 2. The Excel YIELD function allows the value to be 1 for annual coupon payments, 2 for semi-annual coupon payments or 4 for quarterly coupon payments. No other values are permitted.
Day Count Basis
The basis argument is the day count basis used. The Excel YIELD function allows any of the values 0 – 4 shown below:
- 0 or omitted US (NASD) 30/360
- 1 Actual/actual
- 2 Actual/360
- 3 Actual/365
- 4 European 30/360
Using the Excel YIELD Function with Variable Arguments
Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.
In this example spreadsheet, the variable arguments are entered in cells B4 to B10, and the Excel YIELD function is entered at cell B13 as =YIELD(B4,B5,B6,B7,B8,B9,B10). By changing any of the variables in B4 to B10, the yield to maturity can be recalculated without having to enter the Excel YIELD function each time.
The Excel YIELD to maturity function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.