September 23, 2023

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

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

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

READ:  How to use PRICE Function in Excel #### Excel

View all posts by Excel →