Excel YIELD Function

The Excel YIELD function has the syntax shown below.

YIELD(Settlement, Maturity, Rate, Pr, Redemption, Frequency, Basis)
Arguments used in the Excel YIELD function
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 2013 with a face value of 1,000, and coupon rate of 6% paid every 6 months. The Excel YIELD function can be used to calculate the yield to maturity on the bond as follows:

Settlement = 25 October 2013
Maturity = 25 October 2016 (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(2013,10,25), DATE(2016,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 2013 is entered as DATE(2013,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.

excel yield function v 1.0
Excel YIELD Function

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 function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

Excel YIELD Function November 6th, 2016Team

You May Also Like

Related pages

journalizing examplestatement of changes in equity templatecalculate retained earnings balance sheetflexible budget formatwhat is bank reconciliation statement definitioncvp formulainstallment loan examplereceivables turnover ratesofp accountingexpenses template uksample spreadsheet for small businessimprest system of petty cashpetty cash formulamarkup and profitcalculate pmt in exceldefine monetary unit assumptionhow to prepare an income statement in excelpurchase order format xlslcm valuationexcel bookkeepingdetailed accounting equationinvestment turnover formulareplenish petty cash fundpetty cash claim formcash flow statement layoutsale of asset accountingunearned subscription revenuepetty cash normal balancenonprofit chart of accounts examplezero coupon bond price calculatorcontra account listvalue of annuity calculator futurestandard rate per direct labor hourdeferred revenue meaningformula for pvaretained earning on balance sheetformula for compounding continuouslyperpetual system of inventorydiscounted cash flow formula in excelhow do you calculate total asset turnovergeneral ledger accounts payablepayback period calculationshipped fobwip balance sheetamortise definitionpresent value interest factor annuity tabledaily timesheet templateintercompany payables and receivablesloan amortization ukcalculate stockholders equitymileage form templatescalculating pmthow to calculate the average collection periodlifo reserve journal entrythree column cashbookconsignment goods accountingfinancial accounting trial balancethe balance in premium on bonds payablediscount excel formulaaccounting formulas and calculationscontinuous compounding formulaexamples of spreadsheets for small businessformula for fixed assets coverage ratiostatement of changes in equity templatemeaning of amortisefundamental accounting conceptexplain petty cash transactionspv of a perpetuityinventory sheets templatenpv depreciationexample of a ledger bookdepreciation formulasamortization expense journal entrycompounded continuously meanscapital lease lessordebt to value ratio calculatoramortization of bond premium journal entrypresent value of an annuity tablejournal entries depreciation