INTRATE Function

What is the INTRATE Function?

The INTRATE Function[1] 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.

Formula

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

The INTRATE function uses the following arguments:

1. Settlement (required argument) – This is the settlement date of the security. The settlement date of a security is the one after the issue date when the security is traded to the buyer.
2. Maturity (required argument) – This is the maturity date of security or the date when the security expires.
3. Investment (required argument) – The amount that is invested in the security.
4. Redemption (required argument) – This is the amount we will receive at maturity.
5. Basis (optional argument) – The type of day count basis to be used. The possible values of the basis are:

How to use the INTRATE Function in Excel?

As a worksheet function, INTRATE can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:

Example 1

Let us see the results from this function when we provide the following data:

As per the data above, the INTRATE function is used to calculate the interest rate of an investment of $1,000, which was used to purchase a security on January 4, 2017. The security will mature on April 30, 2018, with a redemption value of $1,125 and the US (NASD) 30/360 day count basis is used.

The formula to use will be:

The formula used in INTRATE calculations is as follows:

Where:

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

We get the results below:

Remember:

• The date arguments are provided to the function as references to cells containing dates.
• The [basis] argument is omitted so it took the default of 0 (US (NASD) 30/360 basis).

A few notes about the INTRATE Function:

1. #VALUE! error – Occurs when:
   • The settlement dates given in the argument is not in a valid format.
   • The maturity date is not given in a valid format.
   • Any of the arguments provided is non-numeric.
2. #NUM! error – Occurs when:
   • The given value of the investment is less than or equal to 0.
   • The given redemption value is less than or equal to 0.
   • The given [basis] argument is not equal to 0, 1, 2, 3, or 4.
   • The given maturity date is less than or equal to the supplied settlement date.
3. Remember that Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2018 is serial number 43,101, because it is 43,101 days after January 1, 1900.
4. Settlement, maturity, and basis are truncated to integers if they are in decimal format.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling analysis. To learn more, check out these additional CFI resources:

• Financial Modeling Best Practices
• Advanced Excel Formulas Course
• Advanced Excel Formulas
• Financial Analyst Program
• See all Excel resources