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

fundamental accounting concepthow to create suspense account in tallybonds payable balance sheetretained earning meaningcash flow statement analysis exampletraceable fixed costscontribution margin and fixed costsjournal entry to record net incomesimple bank reconciliation templatefob shipping point freight collectmanufacturing overheadsexcel npv formulaexcel prove it practice testsum of the years depreciation calculatorwhat is deferred revenue expenditurepayroll payable journal entrypv on excelaccounting basic quizpetty cash log sheet templateprofit margin per unit formulais a prepaid expense an assetvoucher receipt sampleyear end closing entries accountingmarkup percentage calculatoran accounts receivable ledger ishow to calculate cost of goods sold in managerial accountingmanufacturing overhead variancestatement of retained earnings equationtotal overhead appliedwhat is prepaid insurance on a balance sheetwhat is petty cash voucherdepreciation and salvage valuewhat is the formula for total variable costpresent value factor annuityterm loan double entrysum of the years digits depreciation calculatoraccounting for gift cardsnon profit chart of accounts samplecontra equity account examplesis accounts receivable a credit or debittransfer of receivables with recoursewhat is accounts receivable factoringaccounting for consignment inventorylump sum discountgearing in accountingexcel pvcalculate effective interest rate on bondslifo examplespayroll journal entryexamples of accrued expensemanufacturing overhead calculatoraccruals double entryexamples of contra asset accountsdividend declaration form templategeneral ledger accounts exampledefine adjusting entriesrelevance of variance analysis to budgeting and standard costinghow to calculate account receivable turnovercalculating perpetuity valuespetty cash analysis sheetformulas for simple interest and compound interestlifo method formulahow to work out markupexamples of revenue and capital expenditurejournal entry for provision for expensesblank accounting worksheetmileage form templatesbookkeeping entriesexamples of income statementswhat is bank reconciliation statement definitiontotal variable cost equationbank overdraft in balance sheetexamples of prepaid expenses