INTRATE function

The INTRATE function in Excel is used to calculate the interest rate for a security based on the purchase price, the redemption price, and the number of days the security is held. This function is useful for calculating the annualized interest rate of a security or investment, particularly for bonds or other fixed-income instruments.

Syntax

INTRATE(settlement, maturity, investment, redemption)

Parameters

• settlement: The settlement date is the date after the issue date when the security is traded to the buyer. This is when the buyer takes ownership of the security.
• maturity: The maturity date is the date when the security expires or matures, and the principal is paid back.
• investment: The purchase price of the security (the amount paid to purchase it).
• redemption: The redemption price is the price at which the security can be redeemed or the amount the investor will receive when the security matures.

How It Works

The INTRATE function calculates the interest rate of a security by comparing the difference between the purchase price (investment) and the redemption price over the number of days the security is held, taking into account the year count (365 days). The result is an annualized interest rate.

The general formula is:

$$\text{Interest Rate} = \frac{\text{Redemption Price} – \text{Investment Price}}{\text{Investment Price}} \times \frac{360}{\text{Days Held}}$$

Example

Let’s say:

• The settlement date is January 1, 2025.
• The maturity date is July 1, 2025.
• The purchase price (investment) is $950.
• The redemption price is $1,000.

To calculate the interest rate for the security using the INTRATE function, the formula would look like this:

=INTRATE("2025-01-01", "2025-07-01", 950, 1000)

Explanation of the Example:

• "2025-01-01": The settlement date is January 1, 2025.
• "2025-07-01": The maturity date is July 1, 2025.
• 950: The investment price is $950 (what the buyer paid for the security).
• 1000: The redemption price is $1,000 (the amount the buyer will receive at maturity).

The INTRATE function will calculate the interest rate earned on the security based on the given dates and prices.

Important Notes

• The settlement and maturity dates should be entered as valid Excel date formats.
• The function uses a 360-day year for interest calculations, which is a common convention for bond markets.
• The result of the INTRATE function represents the annualized interest rate.
• The INTRATE function is typically used for securities such as bonds, where the difference between the purchase price and the redemption price represents the return earned.

Summary

The INTRATE function in Excel is used to calculate the interest rate for a security based on its purchase price, redemption price, and the holding period. It is particularly useful for determining the annualized interest rate for fixed-income securities such as bonds, where the interest rate is implied by the difference in price over time.