Skip to main content

Excel ODDLYIELD Function

The ODDLYIELD function returns the yield of a security with an odd (short or long) first period.

oddlyield function 1


Syntax

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


Arguments

  • settlement (required): The settlement date of the security.
  • maturity (required): The maturity date of the security.
  • last_interest (required): The first coupon date of the security.
  • rate (required): The annual interest rate of the security.
  • pr (required): The price of the security.
  • redemption (required): The security's redemption value per $100 face value.
  • frequency (optional): The frequency of the coupon payments:
    • frequency = 1, if paid annually;
    • frequency = 2, if paid semi-annually;
    • frequency = 3, if paid quarterly.
  • basis (optional): The type of day count basis to use when calculating interest for the security (default = 0):
    • basis = 0 or omitted, if day count = US (NASD) 30/360;
    • basis = 1, if day count = Actual/actual;
    • basis = 2, if day count = Actual/360;
    • basis = 3, if day count = Actual/365;
    • basis = 4, if day count = European 30/360.

Return Value

The ODDLYIELD function returns a numeric value.


Function Notes

  • ODDLYIELD will return the #VALUE! error if last_interest, maturity or settlement is not a valid date.
  • ODDLYIELD will return the #NUM! error if:
    • rate < 0 or pr ≤ 0;
    • redemption ≤ 0;
    • frequency is not the number 1, 2, or 4;
    • basis < 0 or basis > 4;
    • maturity > last_interest > settlement is not satisfied;
    • The interval between maturity and last_interest does not match the frequency interval.
  • last_interest, settlement, maturity, frequency, and basis are truncated to integers.
  • The result of the formula is a decimal. To convert it to a percent, please click here.

Example

To get the yield of a security with the information as shown in the below table, please copy or enter the formula below in a blank cell, and press Enter to get the result:

=ODDLYIELD(C3,C4,C5,C6,C7,C8,C9,C10,C11)

oddlyield function 2

If a security has the same information above except that the settlement date is June 15, 2021, you can add a DATE function to replace the C3 in the formula:

=ODDLYIELD(DATE(2021,6,15),C4,C5,C6,C7,C8,C9,C10,C11)


Related functions

Excel ODDLPRICE Function

The ODDLPRICE function returns the price per $100 face value of a security with an odd (short or long) last period.

Excel ODDFYIELD Function

The ODDFYIELD function returns the yield of a security with an odd (short or long) first period.

Excel ODDFPRICE Function

The ODDFPRICE function returns the price per $100 face value of a security with an odd (short or long) first period.


The Best Office Productivity Tools


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations