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
1actual/actual
2actual/360
3actual/365
4European 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:

or

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:

 ABCD
1settlement 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:
2maturity 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:


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
or
  • The supplied [basis] argument is not equal to 0, 1, 2, 3 or 4
or
  • The supplied maturity date ≤ the supplied settlement date.
#VALUE!-

Occurs if either:

  • The supplied settlement or maturity arguments are not valid dates
or
  • Any of the supplied arguments are non-numeric.