How to use INTRATE Function in Excel

The INTRATE Function is categorized under Excel Financial functions. It will calculate the interest rate for a fully invested security. The function was introduced in MS Excel 2007. As a financial analyst, we regularly come across scenarios where we need to evaluate investments. The INTRATE function is particularly useful in calculating the interest rate of an unlisted bond.

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

The INTRATE function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Investment    Required. The amount invested in the security.
  • Redemption    Required. The amount to be received at maturity.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Example: Let’s look at some Excel INTRATE function examples and explore how to use the INTRATE function as a worksheet function in Microsoft Excel:

INTRATE Function - How to use INTRATE Function in Excel

Syntax:  =INTRATE(B1,B2,B3,B4,B5)

Result:

INTRATE Function in Excel 1 - How to use INTRATE Function in Excel

Based on the Excel spreadsheet above, the following INTRATE examples would return:

Syntax: =INTRATE(B1,B2,B3,B4,B5)
Result: 0.05793913

Syntax: =INTRATE(DATE(2019,3,1),DATE(2019,6,1),3000000,3044420,2)
Result: 0.05793913

Syntax: =INTRATE(DATE(2019,3,1),DATE(2019,6,1),B3,B4,B5)
Result: 0.05793913

Note:

  1. Settlement, maturity, and basis are truncated to integers.
  2. If settlement or maturity is not a valid date, INTRATE returns the #VALUE! error value.
  3. If investment ≤ 0 or if redemption ≤ 0, INTRATE returns the #NUM! error value.

  4. If basis < 0 or if basis > 4, INTRATE returns the #NUM! error value.
  5. If settlement ≥ maturity, INTRATE returns the #NUM! error value.
  6. INTRATE is calculated as follows: INTRATE Function in Excel - How to use INTRATE Function in Excel

    where:

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

Add a Comment

Your email address will not be published. Required fields are marked *