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

accounting equation examplehow to calculate account receivable turnoverbeginning inventory purchasesjournal entry for warrantycash receipts from customers formulatemplate for vouchersdifference between markup and profitwhat are retained earningunearned creditnormal balance debit or creditactivity ratios formulainvoice received journal entryexample of double declining depreciationamortisation intangible assetsaccounting entry for bad debtsaccounts receivable spreadsheet templateformat of debtors accountinstallment notes payablebad debts journal entryvariable cost calculationroce accountingbasic bookkeeping test questionspartnership balance sheet samplecash disbursement bookaccounting cycle quizhow to calculate assets liabilities and equitygross markup calculatormargin calculator excelmargin vs markup calculatorprepaid expense exampleaccounting trial balance worksheetstraight bond value calculatorsalvage value in depreciationsingle step income statement templatepercent markup on costwhat is journal entry for prepaid expensesthe accounting cycle explainedmonthly bank statement templatedeferred tax computationbad debts journal entryledger sheets to printtrade and cash discount calculationaverage settlement period for trade receivableshow to calculate labor rate variancedividend journal entriesjob costing journal entriesexcess and obsolete inventoryaccrued expense adjusting entryaccrued interest entrycalculate total fixed cost formulamaterial requisition formatpv table of annuityjournal entry for collection of accounts receivabledebit and credits accountinglabor efficiency formuladifference between bank statement and bank reconciliationhow to prepare an amortization schedulefixed assets turnoverincome gearing ratiofifo equivalent units of productionvoucher template wordhow to calculate discount factor in exceldiscounted cash flows excelthe normal balance of the accumulated depreciation account is debitgross profit markup calculatorgeneral ledger paperintangible items examplesvolume variance definitionnonprofit bookkeeping & accounting for dummies pdfaccounting template excelweighted average formula accountingreturn on capital employed analysiscost of finished goods manufactured formuladeclining method of depreciationhow to calculate material usage variancewhat is verifiability in accountingleveraged lease accounting exampleimprest system petty cash