# 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:

• References to cells containing dates
or
• 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:

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:

• 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 ≤ 0orThe supplied [basis] argument is not equal to 0, 1, 2, 3 or 4orThe supplied maturity date ≤ the supplied settlement date. #VALUE! - Occurs if either:The supplied settlement or maturity arguments are not valid datesorAny of the supplied arguments are non-numeric.