INTRATE

The INTRATE function is used to calculate the interest rate for a fully invested security.

Syntax

=INTRATE(Settlement, Maturity, Investment, Redemption, Basis)

Settlement The security's purchase date.
Maturity The security's maturity date.
Investment The amount invested in the security.
Redemption The amount that will be received at maturity.
Basis The day-count basis to use.

About INTRATE

When you're looking to determine the interest rate on a fully invested security, the INTRATE function in Excel steps in as your go-to tool. Useful in financial analysis and investment scenarios, this function aids in calculating the annual interest rate earned on a security from purchase to maturity, bearing significance for investors seeking clarity on their investment returns and bond valuation processes.

Crafting an efficient application of INTRATE demands essential inputs on the security under scrutiny. You provide the settlement date, marking the initiation of investment, and the maturity date when the security reaches its full term. Additionally, furnish the initial investment amount and the redemption value, which denotes the monetary sum expected at the security's maturity.

A standout attribute of INTRATE is its adaptability to varying day-count bases. By incorporating the Basis parameter, you tailor the interest rate calculation to align with specific day-count conventions, enhancing precision in interest rate assessments across diverse financial instruments and markets.

INTRATE excels in generating insights into the earned interest return for investments boasting complete utilization of funds. It facilitates calculations that illuminate the annual interest rate relevant to the entire investment tenure, serving as a cornerstone for evaluating investment profitability and decision-making in financial ventures.

In essence, INTRATE emerges as a reliable aid in Excel for discerning the interest rate on fully invested securities. Rely on it as a pivotal financial companion, offering clarity on the accrued returns from investments and empowering informed choices in investment endeavors.

Examples

Imagine you've invested $10,000 in a security with a maturity date of December 31, 2023, and you expect to receive $10,800 at maturity. You purchased the security on June 30, 2022. To determine the interest rate on this investment, you'd use the INTRATE formula:

=INTRATE("6/30/2022", "12/31/2023", 10000, 10800, 0)

This formula will provide the annual interest rate earned on the fully invested security.

Consider an investment of $5,000 in a security with a maturity date of August 15, 2022, and a redemption value of $5,300. The investment was made on January 15, 2022. To calculate the interest rate on this investment using the actual/actual day-count basis, you would use the formula:

=INTRATE("1/15/2022", "8/15/2022", 5000, 5300, 1)

This will reveal the annual interest rate accrued on the fully invested security with the specific day-count basis.

Questions

How does the INTRATE function determine the interest rate?

The INTRATE function calculates the interest rate by employing an iterative process that adjusts the rate until it converges on the actual interest rate that makes the present value of the investment equal to the redemption value at maturity.

Can the INTRATE function handle investments with irregular maturity dates?

Yes, the INTRATE function accommodates investments with irregular maturity dates. It calculates the interest rate based on the actual number of days between the settlement and maturity dates, adapting to the specific investment timeline.

Is it possible to customize the day-count basis for interest rate calculations with the INTRATE function?

Absolutely. The INTRATE function allows you to customize the day-count basis for accurately calculating interest rates by utilizing the Basis argument. You can select from various day-count conventions tailored to the specific needs of your financial analysis.

Related functions

RATE
YIELD
COUPNOMINAL
COUPDAYBS
COUPDAYS
COUPDAYSNC
DURATION
MDURATION