ODDLYIELD Excel Formula

Author Zaheer    Category Formulas     Tags ,

Most of the professionals that are engaged in extensive computation utilize Microsoft Excel as their tool in managing their data. In order to use the program efficiently, users must be familiar of the different functions. As much as possible learn and memorize the codes and formulas, so that when certain alterations or conversions are needed during computation, it could be done easily. As mentioned earlier, it is used in terms of computation for financial and accounting data; one of the functions that should be learned and understood well is the ODDLYIELD function.

What is ODDLYIELD Function?

ODDLYIELD function of Microsoft Excel computes the security yield with an odd final period. The last period could be longer or shorter compared to the other periods.

ODDLYIELD Function Formula:

The syntax to use on ODDLYIELD function is:

=ODDLYIELD(settlement, maturity, last_interest,rate, pr, redemption, frequency, [basis])

Any person need to be familiar with this syntax in order to use the function efficiently and be able to obtain result accurately. Once a wrong syntax is entered in Microsoft Excel spreadsheet, hundred percent incorrect figures are obtained.

Understanding the Formula:

  • settlement – this is the value of the date of settlement
  • maturity – - this is the date of maturity. This is the final date of the odd period
  • last_interest – this is the security date of the last interest payment prior to the day of maturity.
  • rate – this is the annual coupon rate of security
  • pr – the security cost
  • redemption – the complete amount to be obtained at maturity
  • frequency – the usual value of this is 1, 2, 4, 6 or 12.
  • basis -  this is the day count basis. The usual value of this is 0 = US 30/60, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365 and 4 = EU 30/360

Things to Consider When Using ODDLYIELD Function:

  • It is only the argument date part used for last_interest, maturity and settlement. The dates must satisfy the condition that maturity is greater than settlement and settlement should be greater than last_interest.
  • The rate should be non-integral numerical type.
  • The redemption and pr should be greater than 0 and could be in numeric type.
  • The basis and the frequency must be in numeric type but non-integral type must be shortened and the answer should contain one of the valid integers that are mentioned above.
  • Null values within the input are prohibited.

For a better understanding of the function, take a look at the example below and analyze it.

Presume that a certain bond will mature on September 24, 2011 having an interest rate of 4.6% and the cost of $100. The final date of interest prior to the maturity was August 24, 2010 and it was bought on June 6, 2011. It has annual interest payment, a redemption cost amounting to $100 and 30/360 day count basis.


settlement – June 6, 2011

maturity – September 24, 2011

last_interest – August 24, 2010

rate – 0.046

pr – 100

redemption – 100

frequency – 1

basis – 0

Having this data, these could be keyed-in in Excel by entering the values in certain cells. After supplying the values, click any cell where you want the answer to appear and in the cell, type the ODDLYIELD formula. Once all the required arguments are typed in, press the enter key and the result will be 0.0444 or 4.44%.

Post comment