How to Use the INTRATE and RECEIVED Functions in Microsoft Excel for Fully Invested Securities

When investing in short-to-medium-term fixed-income instruments such as commercial paper, bankers’ acceptances, guaranteed investment certificates (GICs), and certificates of deposit (CDs), investors frequently encounter “fully invested” securities. In a fully invested security, the investor pays the full capital upfront at settlement and receives all accrued interest alongside their original principal in a single lump sum upon the maturity date. Microsoft Excel provides two specialized financial functions to model these transactions: INTRATE (which calculates the annualized interest rate) and RECEIVED (which determines the total payout amount received at maturity).

Understanding Fully Invested Financial Instruments

Fully invested securities differ fundamentally from discounted bills and coupon-bearing bonds:

• Lump-Sum Repayment: The instrument pays no interim coupons. All accumulated interest and principal are disbursed simultaneously at maturity.
• Upfront Capital Deployment: The investment is initiated with a positive investment value ($investment$) rather than a discounted price per $100 face value.
• Annualized Interest Rate: The effective return is determined by the total interest earned over the exact number of calendar days between settlement and maturity, divided by a standard year basis.

Part 1: The INTRATE Function

The INTRATE function calculates the interest rate for a fully invested security based on the initial investment amount, redemption value, and settlement/maturity dates.

Syntax

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

Parameter Definitions

• settlement (Required): The security’s settlement date (when the capital is deposited).
• maturity (Required): The maturity date when principal and interest are returned.
• investment (Required): The total capital amount invested upfront (entered as a positive number).
• redemption (Required): The total amount received at maturity, including principal and accumulated interest.
• basis (Optional): The day-count convention flag:
  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360 (Standard in US and Euro commercial paper markets)
  • 3: Actual/365 (Standard in UK money markets)
  • 4: European 30/360

Practical INTRATE Example

Assume a corporate fund deposits capital into a commercial certificate of deposit with the following terms:

• Settlement Date: 15 February 2026 (Cell A2)
• Maturity Date: 15 November 2026 (Cell B2)
• Investment Amount: £250,000 (Cell C2)
• Redemption Payout: £260,500 (Cell D2)
• Day Count Basis: 3 (Actual/365) (Cell E2)

To determine the effective annualized interest rate, enter in cell F2:

=INTRATE(A2, B2, C2, D2, E2)

Format the result as a percentage. Excel calculates an annualized interest rate of 5.62%, reflecting the exact 273 days of elapsed investment time.

Part 2: The RECEIVED Function

The RECEIVED function calculates the total capital amount received at maturity when the initial investment amount and the agreed annualized interest rate are known.

Syntax

=RECEIVED(settlement, maturity, investment, discount, [basis])

Parameter Definitions

• settlement (Required): The acquisition date.
• maturity (Required): The maturity date.
• investment (Required): The initial principal deposited.
• discount (Required): The security’s annualized interest or discount rate (entered as a percentage or decimal, e.g. 5.5% or 0.055).
• basis (Optional): The day-count convention indicator (matching the conventions listed above).

Practical RECEIVED Example

A treasury manager allocates £500,000 into a 180-day bank deposit offering a 5.25% annual rate on an Actual/365 basis:

• Settlement Date: 01 March 2026 (Cell A5)
• Maturity Date: 28 August 2026 (Cell B5)
• Investment: £500,000 (Cell C5)
• Interest Rate: 5.25% (Cell D5)
• Basis: 3 (Actual/365) (Cell E5)

To calculate the total payout disbursed at maturity:

=RECEIVED(A5, B5, C5, D5, E5)

Format the resulting cell as Currency. Excel returns £512,945.21, indicating that the investment earns exactly £12,945.21 in interest over the 180-day holding period.

Mathematical Comparison: INTRATE vs. DISC

Financial analysts often confuse INTRATE with DISC:

• DISC: Applied to securities purchased at a discount from an arbitrary $100 face value (where the redemption value is fixed at 100).
• INTRATE: Applied to securities based on absolute currency investments where interest is explicitly added to the invested principal.

Common Errors and Validation Rules

To ensure calculations remain robust and accurate in financial models:

• Maturity Order: The maturity date must be chronologically later than the settlement date. If settlement >= maturity, Excel returns a #NUM! error.
• Positive Cash Values: Both investment and redemption must be greater than zero.
• Date Formats: Reference genuine Excel date serial numbers (such as those generated via the DATE() function) rather than typed text strings to avoid regional date parsing failures.
• Basis Validation: The basis integer must range between 0 and 4.