The Excel INTRATE Function

Related Function:

DISC Function

Function Description

The Excel Intrate function calculates the interest rate for a fully invested security.

The syntax of the function is:

INTRATE( settlement, maturity, investment, redemption, [basis] )

where the arguments are as follows:

settlement

The security's settlement date (i.e. the date that the coupon is purchased).

maturity

The security's maturity date (i.e. the date that the coupon expires).

investment

The initial amount invested into the security.

redemption

The amount to be received at maturity.

[basis]

An optional argument, that specifies the day count basis to be used in the calculation.

Possible values of [basis] and their meanings are:

[basis]	Day Count Basis
0 (or omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	European 30/360

The financial day count basis rules are explained in more detail on the Wikipedia Day Count Convention page

Warning: Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the settlement and maturity dates should be input to the Intrate function as either:

References to cells containing dates

Dates returned from other functions or formulas.

Intrate Function Example

In the spreadsheet below, the Excel Intrate function is used to calculate the interest rate of an investment of $1,000, which was used to purchase a security on 01-Apr-2005. The security matured on 31-Mar-2010, with a redemption value of $2,125 and the US (NASD) 30/360 day count basis is used:

	A	B	C	D
1	settlement date:	01-Apr-2005		Interest rate of a security with value $1,000, invested on 01-Apr-2005, which matures on 31-Mar-2010, with a redemption value of $2,125. The US (NASD) 30/360 day count basis is used:
2	maturity date:	31-Mar-2010		=INTRATE( B1, B2, 1000, 2125 )

The formula in cell D2 of the above spreadsheet returns the value 0.225, or 22.5%.

Note that, in the above example:

As recommended, the date arguments are supplied to the function as references to cells containing dates;
The [basis] argument is omitted and so takes on the default of 0 (US (NASD) 30/360 basis).

Further details and examples of the Excel Intrate function are provided on the Microsoft Office website.

Intrate Function Errors

If you get an error from the Excel Intrate function, this is likely to be one of the following:

Common Errors

#NUM!

Occurs if either:

The supplied investment or the supplied redemption is ≤ 0

The supplied [basis] argument is not equal to 0, 1, 2, 3 or 4

The supplied maturity date ≤ the supplied settlement date.

#VALUE!

Occurs if either:

The supplied settlement or maturity arguments are not valid dates

Any of the supplied arguments are non-numeric.

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions