MS Excel: INTRATE function for interest rate calculation

The INTRATE function in Excel calculates the interest rate for a fully invested security between two dates. It is mainly used for short-term investments, such as Treasury bills, where the security does not pay periodic interest (zero-coupon bonds).

In simple words: It finds the simple interest rate earned over a specific period.

Syntax

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

Argument	Description
settlement	The date when the investment is purchased. (Must be after the issue date.)
maturity	The date when the investment matures (redeems).
investment	The amount invested initially (the purchase price).
redemption	The amount paid at maturity (redemption value).
[basis] (optional)	Type of day count basis (defaults to 0 if omitted).

Basis Types Table

Basis	Day Count Basis
0	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

How INTRATE Works

The INTRATE function calculates a simple (not compounded) interest rate over a period based on the investment cost and redemption amount.

It uses this formula internally:

Depending on the selected basis, the calculation of days between settlement and maturity may vary.

Example 1: Basic Use of INTRATE

You purchase a short-term bond for $980 on January 1, 2025, and it matures on June 30, 2025 for $1,000. You want to calculate the simple interest rate.

Table Setup:

Cell	Data
A2	Settlement Date: 01/01/2025
A3	Maturity Date: 06/30/2025
A4	Investment: 980
A5	Redemption: 1000
A6	Basis: 0 (US 30/360)

Formula in Excel:

=INTRATE(A2, A3, A4, A5, A6)

Result:

Interest Rate ≈ 0.04082 → or about 4.08%

Step-by-Step Calculation

1. Difference between Redemption and Investment = 1000 - 980 = 20

2. Investment = 980

3. Days between dates (30/360 basis) = 180 days

4. Interest Rate = (20 / 980) × (360 / 180) = 0.04082

Example 2: Using Actual/Actual Basis

Same data, but you want to calculate based on the actual days.

Change Basis to 1.

Cell	Data
A6	Basis: 1 (Actual/Actual)

Formula:

=INTRATE(A2, A3, A4, A5, 1)

Result:

Interest Rate ≈ 0.04064 → or about 4.06%

• Actual days from Jan 1 to June 30 = 180 days (non-leap year)

Important Notes

• Dates must be valid Excel dates (not text).

• If settlement ≥ maturity, Excel returns a #NUM! error.

• If investment ≤ 0 or redemption ≤ 0, Excel returns a #NUM! error.

• Basis affects the number of days counted between settlement and maturity.

Real-World Applications

• Calculating returns on Treasury bills.

• Analyzing zero-coupon bonds.

• Estimating short-term investment yields.

• Preparing investment performance reports.

Conclusion

The INTRATE function is a straightforward yet powerful tool for calculating the simple interest rate on short-term, fully invested securities.Whether you're working with Treasury bills, short bonds, or other zero-coupon securities, INTRATE helps you quickly find the earned return without manual calculations.