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:

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

Result:

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

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:

   where:

   • B = number of days in a year, depending on the year basis.

   • DIM = number of days from settlement to maturity.