What is the RECEIVED Function Guide in Excel?

Calculate maturity values of discount securities with Excel's RECEIVED function. Complete guide with examples and solutions for Treasury Bills. =RECEIVED(settlement, maturity, investment, discount, [basis]) Use AskFormulas to generate validated formulas instantly.

How do I use RECEIVED Function Guide to calculate the amount received at maturity for a $10,000 treasury bill?

To calculate the amount received at maturity for a $10,000 treasury bill, use the formula: =RECEIVED(DATE(2025,1,15), DATE(2025,7,15), 10000, 0.0525, 2). Basic Treasury Bill Calculation This formula returns: $10,268.49.

How can I optimize RECEIVED Function Guide performance?

To optimize RECEIVED Function Guide: 1) Use specific ranges instead of entire columns, 2) Avoid volatile functions within RECEIVED Function Guide, 3) Consider array formulas for bulk operations, 4) Cache results when possible. AskFormulas automatically applies optimization best practices.

What is the syntax for RECEIVED Function Guide?

The RECEIVED Function Guide syntax in Excel is: =RECEIVED(settlement, maturity, investment, discount, [basis]). It takes 5 parameter(s): settlement (required), maturity (required), investment (required), discount (required), basis (optional).

How do I use RECEIVED Function Guide to determine maturity value for commercial paper investment?

To determine maturity value for commercial paper investment, use the formula: =RECEIVED("2025-03-01", "2025-06-01", 50000, 0.045, 2). Commercial Paper Investment This formula returns: $51,875.00.

Can RECEIVED Function Guide handle context in Excel?

Yes, RECEIVED Function Guide can handle context. For example: =RECEIVED(DATE(2025,1,15), DATE(2025,7,15), 10000, 0.0525, 2) will calculate the amount received at maturity for a $10,000 treasury bill. This returns: $10,268.49

RECEIVED Function Guide

Calculate maturity values of discount securities with Excel's RECEIVED function. Complete guide with examples and solutions for Treasury Bills.

Excel

Google Sheets

financial

intermediate

Syntax Preview

Excel

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

Quick Answer

TL;DR

RECEIVED function RECEIVED function is a financial function in Excel that calculates the amount received at maturity for a fully invested security based on a discount rate. It's primarily used for Treasury Bills, commercial paper, and other discount securities, helping investors determine their return at maturity. The function returns the maturity value by applying the discount rate over the investment period.

Comprehensive Explanation

Syntax and Parameters Deep Dive

## What is the RECEIVED Function?

The RECEIVED function is a specialized financial calculation tool in Excel designed for analyzing discount securities - financial instruments that are purchased at a discount from their face value and pay no periodic interest. Instead, the investor's return comes entirely from the difference between the purchase price and the amount received at maturity.

### Core Functionality

RECEIVED operates using a discount security valuation methodology:

1. **Investment Analysis:** Takes your initial investment amount and the discount rate
2. **Time Calculation:** Determines the precise number of days between settlement and maturity using the specified day count basis
3. **Maturity Value Computation:** Calculates the amount you'll receive at maturity using the formula: Investment / (1 - (Discount × Days / Basis_Days))

This calculation is fundamental to short-term money market instruments, providing accurate valuations that account for various day count conventions used in different markets worldwide.

### When to Use RECEIVED

The RECEIVED function is the ideal choice when you need to:

1. **Treasury Bill Analysis:** Calculate the maturity value of T-Bills, the most common application for this function. Treasury departments and portfolio managers use this daily to value short-term government securities.

2. **Commercial Paper Valuation:** Determine returns on corporate commercial paper investments, essential for cash management and short-term investment decisions.

3. **Investment Comparison:** Compare different discount securities with varying terms, rates, and investment amounts to optimize your short-term portfolio allocation.

4. **Portfolio Valuation:** Calculate aggregate maturity values for portfolios containing multiple discount securities with different maturity dates.

### Understanding Discount Securities

Discount securities differ fundamentally from coupon-bearing bonds:

- **No Periodic Interest:** Unlike traditional bonds, discount securities pay no interest during their life
- **Purchase Discount:** Bought below face value, with the discount representing the investor's return
- **Short Maturity:** Typically mature in less than one year (90-360 days)
- **High Liquidity:** Generally very liquid instruments in secondary markets
- **Lower Risk:** Often government-backed or issued by highly rated corporations

### Key Advantages

RECEIVED provides several critical benefits:

- **Accuracy:** Eliminates manual calculation errors in discount security valuation
- **Day Count Flexibility:** Supports five different day count conventions for global market compatibility
- **Speed:** Instant calculations for portfolio analysis and investment decisions
- **Standardization:** Uses market-standard formulas ensuring consistency with industry practices

### Platform Compatibility

RECEIVED is available in:

- **Excel 2007-2021 and 365:** Full support with consistent behavior across versions
- **Excel for Mac:** Identical functionality to Windows versions
- **Excel Online:** Complete compatibility with desktop versions

**Important:** Google Sheets does not have a native RECEIVED function. Users must implement manual calculations using the formula: investment / (1 - (discount × days / basis_days)).

## Syntax and Parameters Explained

### The Settlement Parameter (Required)

The settlement date represents when the security transaction is settled and ownership transfers to the buyer. This is typically 1-2 business days after the trade date.

**Important considerations:**
- Must be a valid Excel date serial number or DATE function result
- Should not be on or after the maturity date
- String dates like "2025-01-15" work but DATE function is recommended
- Weekend and holiday settlement dates are handled by the function

**Best practice:** Use DATE(year, month, day) format for clarity and to avoid regional date format issues.

### The Maturity Parameter (Required)

The maturity date is when the security expires and the investor receives the maturity value. This defines the investment holding period.

**Key points:**
- Must be after the settlement date
- Typically 30 to 360 days after settlement for discount securities
- Determines the time component in the discount calculation
- The exact day difference impacts the final maturity value significantly

### The Investment Parameter (Required)

This is the amount you invest (purchase price) in the security - your initial cash outlay.

**Requirements:**
- Must be a positive number
- Represents the actual amount paid at settlement
- Can be any currency amount (function doesn't specify currency)
- Should match your actual investment amount including any fees if you want total return

**Example:** If you purchase a T-Bill for $10,000, this parameter would be 10000.

### The Discount Parameter (Required)

The discount rate is the annual rate used to calculate the security's return, expressed as a decimal.

**Critical formatting note:** This must be in decimal format:
- 5.25% = 0.0525
- 4.5% = 0.045
- 6% = 0.06

**Common mistake:** Entering 5.25 instead of 0.0525 will calculate a 525% discount rate, resulting in errors or wildly incorrect values.

**Market context:** Discount rates vary by:
- Security type (T-Bills typically lower than commercial paper)
- Credit quality (higher risk = higher discount rates)
- Term length (longer terms often higher rates)
- Market conditions (rates fluctuate with monetary policy)

### The Basis Parameter (Optional)

The basis parameter determines which day count convention to use for calculating the time factor. Different markets use different conventions:

**Basis 0 (US NASD 30/360) - Default:**
- Assumes 30 days per month and 360 days per year
- Most common for corporate bonds
- Simplifies calculations but less precise

**Basis 1 (Actual/actual):**
- Uses actual days in the month and year
- Most accurate for irregular periods
- Common for government bonds

**Basis 2 (Actual/360):**
- Actual days in period, 360-day year
- **Standard for US Treasury Bills**
- Most common for money market instruments
- Recommended for T-Bill calculations

**Basis 3 (Actual/365):**
- Actual days in period, 365-day year
- Used in some international markets

**Basis 4 (European 30/360):**
- European variant of 30/360 convention
- Different end-of-month rules than US 30/360

**Selection guide:** For Treasury Bills, always use basis 2. For corporate securities, use basis 0 unless specified otherwise by the issuer.

Practical Examples

Common Errors and Solutions

#NUM!

RECEIVED returns #NUM! error

Cause:

Settlement date is on or after maturity date, discount rate is less than or equal to zero, or the calculation results in a mathematical impossibility (discount rate too high for the time period)

Solution:

1. Verify settlement date is before maturity date using =A2<B2 2. Ensure discount rate is positive and reasonable (typically 0.01 to 0.15 for most securities) 3. Check that your discount rate is in decimal format (0.05 not 5) 4. For very high discount rates or long periods, the formula may be undefined - recalculate inputs 5. Verify dates are valid Excel date serial numbers

Prevention:

Add data validation to ensure settlement < maturity and 0 < discount < 1. Use conditional formatting to highlight invalid date ranges.

Frequency: 40%

Example:

#VALUE!

Invalid value error in RECEIVED calculation

Cause:

Invalid date format, non-numeric values for investment or discount parameters, or text values where numbers are expected

Solution:

1. Use DATE function for dates instead of text strings: DATE(2025,1,15) 2. Verify investment and discount are numeric: =ISNUMBER(C2) 3. Check for hidden characters or spaces in numeric cells 4. Ensure discount rate is decimal (0.05) not percentage format 5. Convert text numbers using VALUE function if needed

Prevention:

Format cells properly: dates as Date format, investment as Currency, discount as Number with 4 decimal places. Use data validation to restrict input types.

Frequency: 35%

Example:

#NUM!

Invalid basis parameter error

Cause:

The basis parameter is not 0, 1, 2, 3, or 4. Any other value is invalid and causes this error.

Solution:

1. Use only valid basis values: 0 (US 30/360), 1 (Actual/actual), 2 (Actual/360), 3 (Actual/365), or 4 (European 30/360) 2. For Treasury Bills, use basis 2 3. When in doubt, omit the parameter to use default basis 0 4. Check if cell reference for basis contains valid integer 5. Ensure no formula errors in the basis cell

Prevention:

Create a dropdown list in Excel with valid basis options (0-4) using Data Validation. Add helper text explaining which basis to use for different security types.

Frequency: 15%

Example:

Incorrect Results

RECEIVED returns a value but it seems wrong

Cause:

Discount rate entered as percentage instead of decimal (5.25 instead of 0.0525), wrong day count basis selected, or dates reversed

Solution:

1. Verify discount rate format: should be 0.0525 for 5.25%, not 5.25 2. Check day count basis: T-Bills require basis 2, not default 0 3. Confirm settlement date is before maturity date 4. Compare result to manual calculation: investment / (1 - discount × days / basis_days) 5. Use formula auditing tools to trace precedents

Prevention:

Format discount rate cells as Percentage with 2 decimal places, or Number with 4 decimals. Add a validation check: =IF(D2>1, "Rate should be decimal (0.0525 not 5.25)", "OK")

Frequency: 25%

Example:

Advanced Tips and Best Practices

Day Count Basis Selection

For Treasury Bills, always use basis 2 (Actual/360) as this is the standard market convention. For corporate bonds and commercial paper, basis 0 (30/360) is more common unless specified otherwise by the issuer. Using the wrong basis can cause valuation differences of 0.5-1% on longer-term securities.

Relationship to DISC Function

RECEIVED and DISC are inverse functions. Use DISC to calculate the discount rate when you know the investment and maturity values, and RECEIVED to calculate maturity value when you know the discount rate. They work as a pair for complete discount security analysis: =DISC(settlement, maturity, maturity_value, received_value, basis) gives you back the discount rate.

Date Input Best Practice

Always use the DATE function or cell references for date parameters rather than text strings to avoid regional date format issues. Excel interprets "03/04/2025" as March 4 in US format but April 3 in European format. Using DATE(2025,3,4) eliminates ambiguity and works consistently worldwide.

Discount Rate Format Warning

The discount parameter MUST be expressed as a decimal (e.g., 0.0525 for 5.25%), not as a percentage. Entering 5.25 will calculate as a 525% discount rate, producing either errors or impossibly large maturity values. This is the single most common mistake when using RECEIVED.

Portfolio Analysis Technique

Combine RECEIVED with SUMPRODUCT to calculate weighted average maturity values across a portfolio of discount securities with different terms and rates. This is essential for portfolio managers tracking aggregate portfolio maturity values and calculating portfolio-level metrics.

Error Handling in Production

Always wrap RECEIVED in IFERROR for production spreadsheets to handle invalid data gracefully. This prevents error cascades through dependent calculations and makes troubleshooting easier. Consider using custom error messages that indicate what went wrong.

Combining with Other Financial Functions

RECEIVED works powerfully with other financial functions: Use with PRICE and PRICEDISC for comprehensive bond analysis, combine with INTRATE to calculate implied interest rates, or use with YIELDDISC to compare yields across different security types.

RECEIVED vs Related Functions

Real-World Applications

Conclusion and Next Steps

Day Count Basis Comparison

Compare how different day count bases affect maturity value

Result: Values vary by basis

## Comparing RECEIVED with Similar Functions

### RECEIVED vs PRICE

**PRICE** calculates the price of a coupon-bearing bond per $100 face value, while **RECEIVED** calculates the maturity value of a discount security.

Choose **RECEIVED** when:
- Working with discount securities (T-Bills, commercial paper)
- Securities pay no periodic interest
- You know the investment amount and need maturity value
- Dealing with short-term money market instruments (  1 year)

Choose **PRICE** when:
- Working with coupon-bearing bonds
- Securities pay periodic interest
- You need the current market price of a bond
- Analyzing long-term fixed income securities

### RECEIVED vs PRICEDISC

**PRICEDISC** and **RECEIVED** are closely related but serve opposite purposes in the discount security lifecycle.

**PRICEDISC** calculates the price per $100 face value of a discount security. It tells you what price to pay today.

**RECEIVED** calculates the maturity value from a known investment. It tells you what you'll receive at maturity.

**Key relationship:** If you use PRICEDISC to determine the purchase price per $100, then invest based on that price, RECEIVED tells you the maturity proceeds. They form a complete investment analysis cycle.

### RECEIVED vs DISC

**DISC** and **RECEIVED** are inverse functions:

**DISC** calculates: discount rate = (maturity value - price) / maturity value × (basis / days)
**RECEIVED** calculates: maturity value = investment / (1 - discount × days / basis)

Use **DISC** when:
- You know the purchase price and maturity value
- You need to calculate the effective discount rate
- Comparing securities with known payouts

Use **RECEIVED** when:
- You know the discount rate and investment
- You need to project maturity value
- Planning future cash flows

**Verification technique:** You can verify your RECEIVED calculation by using DISC on the result: =DISC(settlement, maturity, RECEIVED(settlement, maturity, investment, discount, basis), investment, basis) should return your original discount rate.

### RECEIVED vs TBILLPRICE/TBILLYIELD

The TBILL functions are specialized for Treasury Bills specifically:

**TBILLPRICE:** Returns the price per $100 face value for a T-Bill
**TBILLYIELD:** Returns the yield for a T-Bill
**RECEIVED:** Returns maturity value for any discount security

**Advantages of RECEIVED:**
- Works with any discount security, not just T-Bills
- Returns actual dollar amount, not per-$100 value
- More flexible for custom investment amounts
- Handles various day count bases

**When to use TBILL functions:**
- Working exclusively with US Treasury Bills
- Need standardized per-$100 calculations
- Industry standard reporting requirements
- Compliance with specific T-Bill conventions

### Practical Comparison Example

For a $10,000 investment in a 180-day T-Bill at 5% discount:

| Function | Usage | Result |
|----------|-------|--------|
| RECEIVED | =RECEIVED(settlement, maturity, 10000, 0.05, 2) | $10,250.00 |
| DISC | =DISC(settlement, maturity, 10250, 10000, 2) | 0.05 (5%) |
| PRICEDISC | =PRICEDISC(settlement, maturity, 0.05, 100, 2) | $97.50 per $100 |
| TBILLPRICE | =TBILLPRICE(settlement, maturity, 0.05) | $97.50 per $100 |

### Migration and Integration Strategy

For comprehensive discount security analysis, use these functions together:

1. **Initial Analysis:** Use PRICEDISC to determine fair purchase price
2. **Investment Planning:** Use RECEIVED to project maturity values
3. **Rate Verification:** Use DISC to verify implied rates match expectations
4. **Yield Comparison:** Use YIELDDISC or TBILLYIELD for yield comparisons
5. **Portfolio Reporting:** Combine RECEIVED with SUM/SUMPRODUCT for aggregate analysis