Related Function:

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

Syntax

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

Arguments

Argument Description
settlement he security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer
maturity The security’s maturity date. The maturity date is the date when the security expires
investment The amount invested in the security
redemption The amount to be received at maturity
[basis] Optional. Defines the day count basis to be used in the calculation

  [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 further on the Wikipedia Day Count Convention page

Note: The settlement and maturity dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Data      
2 15-Feb-2013 Settlement date    
3 15-May-2013 Maturity date    
4 $1,000,000 Investment    
5 $1,014,420 Redemption value    
6 2 Actual/360 basis    
7        
8 Formula Result Notes
9 =INTRATE(A2,A3,A4,A5,A6) 5.77% Discount rate, for the terms of the bond above
10 =INTRATE(DATE(2013,2,15),A3,A4,A5,A6) 5.77% Settlement date entered as a function

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either of the supplied settlement or maturity arguments are not valid dates or any of the other arguments are not recognized as numeric values
##NUM! Occurs if:

  the investment or the redemption argument is ≤ 0
  the [basis] argument is supplied and is not equal to 0, 1, 2, 3 or 4
  the maturity date is ≤ the settlement date
#NAME? Occurs when Analysis ToolPak add-in is not enabled
Result is presented as a decimal or 0% Likely due to the formatting of the cell containing the function and can be fixed by formatting the cell as a percentage, with decimal places

INTRATE is calculated as follows:

    \[ INTRATE= \frac{redemption - investment}{investment} \ \text{x}\ \frac{B}{DIM} \]

where,

  • B = number of days in a year, depending on the [basis]
  • DIM = number of days from settlement to maturity