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
Required: Yesissue
Required: Yesfirst_interest
Required: Yessettlement
Required: Yesrate
Required: Yespar
Required: Yesfrequency
Required: Yesbasis
Required: Yescalc_methodDISC
Required: Yessettlement
Required: Yesmaturity
Required: Yespr
Required: Yesredemption
Required: NobasisINTRATE
Required: Yessettlement
Required: Yesmaturity
Required: Yesinvestment
Required: Yesredemption
Required: NobasisPRICE
Required: Yessettlement
Required: Yesmaturity
Required: Yesrate
Required: Yesyld
Required: Yesredemption
Required: Yesfrequency
Required: NobasisYIELD
Required: Yessettlement
Required: Yesmaturity
Required: Yesrate
Required: Yespr
Required: Yesredemption
Required: Yesfrequency
Required: NobasisDURATION
Required: Yessettlement
Required: Yesmaturity
Required: Yescoupon
Required: Yesyld
Required: Yesfrequency
Required: NobasisMDURATION
Required: Yessettlement
Required: Yesmaturity
Required: Yescoupon
Required: Yesyld
Required: Yesfrequency
Required: Nobasis
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
ACCRINT | decimal | The interest accrued on a security that pays periodic interest | ACCRINT(46023, 46203, 46266, 0.06, 1000, 2, 0, false) |
DISC | decimal | The yearly discount rate of a security bought below its redemption value | DISC(46037, 46218, 98.5, 100, 2) |
INTRATE | decimal | The yearly interest rate earned on a fully invested security | INTRATE(46037, 46218, 985000, 1000000, 2) |
PRICE | decimal | The price per 100 of face value of a security that pays periodic interest | PRICE(46037, 47863, 0.06, 0.05, 100, 2, 0) |
YIELD | decimal | The yearly yield of a security that pays periodic interest | YIELD(46037, 47863, 0.06, 96, 100, 2, 0) |
DURATION | decimal | The Macaulay duration in years: the average time to receive the bond's cash | DURATION(46037, 47863, 0.06, 0.05, 2, 0) |
MDURATION | decimal | The modified duration: duration divided by 1 plus the yield for one period | MDURATION(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
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An 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 dates | Correct the argument the message names |
HTQL-8003 | ACCRINT 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-8004 | YIELD did not settle on an answer (#NUM!) | Check the price against the coupon and the dates |
HTQL-5004 Not enough params | An argument is missing. The message lists the names the function expects | Give 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 inIFand used inADD,SUBTRACT,MULTIPLYandDIVIDE. PRICE,YIELD,DURATIONandMDURATIONcount 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
basissets the divisor of the year fraction: 0, 2 and 4 divide the actual days by 360, and 1 and 3 divide them by 365.ACCRINTfollows the day count named by itsbasisfor the days and the year length. ACCRINTtakes all eight arguments. Itsfrequencymust be 1, 2 or 4 whencalc_methodistrue; each period is then 52, 26 or 13 weeks.YIELDfinds its answer by repeated approximation starting at the coupon rate and answersHTQL-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
LETwhen several functions use them. - Pass the same
basisto 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.