Bond and security functions

Price a bond, find its yield, measure how sensitive it is to rates and work out accrued interest.

Syntax

Write the function's name and its arguments. Dates are serial day numbers (15 January 2026 is 46037) and rates are fractions, so 6 percent is 0.06. Prices are per 100 of face value.

ACCRINT(
  issue,
  first_interest,
  settlement,
  rate,
  par,
  frequency,
  basis,
  calc_method
)
DISC(settlement, maturity, pr, redemption, [basis])
INTRATE(settlement, maturity, investment, redemption, [basis])
PRICE(
  settlement,
  maturity,
  rate,
  yld,
  redemption,
  frequency,
  [basis]
)
YIELD(
  settlement,
  maturity,
  rate,
  pr,
  redemption,
  frequency,
  [basis]
)
DURATION(settlement, maturity, coupon, yld, frequency, [basis])
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • ACCRINT

    issue

    Required: Yes
  • first_interest

    Required: Yes
  • settlement

    Required: Yes
  • rate

    Required: Yes
  • par

    Required: Yes
  • frequency

    Required: Yes
  • basis

    Required: Yes
  • calc_method

    Required: Yes
  • DISC

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • pr

    Required: Yes
  • redemption

    Required: Yes
  • basis

    Required: No
  • INTRATE

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • investment

    Required: Yes
  • redemption

    Required: Yes
  • basis

    Required: No
  • PRICE

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • rate

    Required: Yes
  • yld

    Required: Yes
  • redemption

    Required: Yes
  • frequency

    Required: Yes
  • basis

    Required: No
  • YIELD

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • rate

    Required: Yes
  • pr

    Required: Yes
  • redemption

    Required: Yes
  • frequency

    Required: Yes
  • basis

    Required: No
  • DURATION

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • coupon

    Required: Yes
  • yld

    Required: Yes
  • frequency

    Required: Yes
  • basis

    Required: No
  • MDURATION

    settlement

    Required: Yes
  • maturity

    Required: Yes
  • coupon

    Required: Yes
  • yld

    Required: Yes
  • frequency

    Required: Yes
  • basis

    Required: No

Examples

A bond bought on 15 January 2026 that matures on 15 January 2031 with a 6 per cent coupon paid twice a year: its price when the market yield is 5 per cent, and the yield you earn if you pay 96 for it.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
ACCRINTdecimalThe interest accrued on a security that pays periodic interestACCRINT(46023, 46203, 46266, 0.06, 1000, 2, 0, false)
DISCdecimalThe yearly discount rate of a security bought below its redemption valueDISC(46037, 46218, 98.5, 100, 2)
INTRATEdecimalThe yearly interest rate earned on a fully invested securityINTRATE(46037, 46218, 985000, 1000000, 2)
PRICEdecimalThe price per 100 of face value of a security that pays periodic interestPRICE(46037, 47863, 0.06, 0.05, 100, 2, 0)
YIELDdecimalThe yearly yield of a security that pays periodic interestYIELD(46037, 47863, 0.06, 96, 100, 2, 0)
DURATIONdecimalThe Macaulay duration in years: the average time to receive the bond's cashDURATION(46037, 47863, 0.06, 0.05, 2, 0)
MDURATIONdecimalThe modified duration: duration divided by 1 plus the yield for one periodMDURATION(46037, 47863, 0.06, 0.05, 2, 0)

DISC is (1 - pr / redemption) / year fraction, and INTRATE is (redemption - investment) / investment / year fraction. The settlement date must be before maturity. frequency must be 1, 2 or 4 in the coupon functions, or the call is refused.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is out of range: settlement not before maturity, a price or redemption of 0 or less, a negative rate or yield, a frequency other than 1, 2 or 4, or no whole coupon period between the datesCorrect the argument the message names
HTQL-8003ACCRINT was given a frequency other than 1, 2 or 4 with calc_method true, or a basis above 4 (#VALUE!)Use the values listed under Parameters
HTQL-8004YIELD did not settle on an answer (#NUM!)Check the price against the coupon and the dates
HTQL-5004 Not enough paramsAn argument is missing. The message lists the names the function expectsGive every argument it lists
Limitations
  • Dates are serial day numbers typed as numbers; a date value or the result of a date function is not accepted. A finance function's result cannot be an argument of another finance function, so type the figure in or name it with LET. The result can be compared in IF and used in ADD, SUBTRACT, MULTIPLY and DIVIDE.
  • PRICE, YIELD, DURATION and MDURATION count whole coupon periods from the year fraction between settlement and maturity, rounded, and discount each period in full. They do not adjust for a part period at settlement.
  • The day count basis sets the divisor of the year fraction: 0, 2 and 4 divide the actual days by 360, and 1 and 3 divide them by 365. ACCRINT follows the day count named by its basis for the days and the year length.
  • ACCRINT takes all eight arguments. Its frequency must be 1, 2 or 4 when calc_method is true; each period is then 52, 26 or 13 weeks.
  • YIELD finds its answer by repeated approximation starting at the coupon rate and answers HTQL-8004 (#NUM!) if it does not settle within 100 steps.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Name the settlement and maturity dates with LET when several functions use them.
  • Pass the same basis to every function that describes the same security.
  • Check a price against YIELD: pricing at a yield and then asking for the yield of that price returns the yield you started with.