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|
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:
Based on the Excel spreadsheet above, the following INTRATE examples would return:
- Settlement, maturity, and basis are truncated to integers.
- If settlement or maturity is not a valid date, INTRATE returns the #VALUE! error value.
If investment ≤ 0 or if redemption ≤ 0, INTRATE returns the #NUM! error value.
- If basis < 0 or if basis > 4, INTRATE returns the #NUM! error value.
- If settlement ≥ maturity, INTRATE returns the #NUM! error value.
INTRATE is calculated as follows:
- B = number of days in a year, depending on the year basis.
DIM = number of days from settlement to maturity.