Complete Guide to Excel's RECEIVED Function for Bond Valuation
=RECEIVED(settlement, maturity, investment, discount, [basis])The RECEIVED function is a specialized financial tool in Excel designed to calculate the amount received at maturity for a fully invested security purchased at a discount. This advanced function is essential for financial analysts, investment professionals, and treasury managers who need to determine the redemption value of discounted securities such as Treasury bills, commercial paper, and other short-term debt instruments. Understanding the RECEIVED function enables you to perform accurate bond valuation calculations, assess investment returns, and make informed decisions about discounted securities. Unlike simple interest calculations, the RECEIVED function accounts for the specific day-count basis used in financial markets, ensuring compliance with industry standards and accurate financial reporting. This comprehensive guide will walk you through the syntax, practical applications, common pitfalls, and advanced techniques for using the RECEIVED function effectively in your Excel financial models and analysis.
Syntax & Parameters
The RECEIVED function calculates the amount received at maturity when a security is purchased at a discount. The complete syntax is =RECEIVED(settlement, maturity, investment, discount, [basis]). The settlement parameter represents the date when you purchase the security, typically the trade date or value date. The maturity parameter specifies when the security matures and you receive the full redemption amount. The investment parameter indicates the total amount you invest in the security at the settlement date. The discount parameter represents the annual discount rate as a decimal (for example, 0.05 for 5%). This rate determines how much less than face value you pay for the security. The optional basis parameter specifies the day-count convention used in the calculation: 0 for 30/360 (default), 1 for actual/actual, 2 for actual/360, 3 for actual/365, or 4 for 30/360 European. Each basis method produces slightly different results based on how days are counted between settlement and maturity dates. Selecting the correct basis is critical for accuracy, as different markets use different conventions. For U.S. Treasury securities, basis 1 (actual/actual) is typically appropriate, while money market instruments often use basis 2 (actual/360).
settlementmaturityinvestmentdiscountbasisPractical Examples
Treasury Bill Investment Analysis
=RECEIVED(DATE(2024,1,15), DATE(2024,4,15), 1000000, 0.0525, 1)This formula calculates the actual amount received at maturity using the actual/actual day-count basis (basis=1), which is standard for U.S. Treasury securities. The investment of $1,000,000 is the amount paid at settlement, and the function returns the maturity value.
Commercial Paper Valuation
=RECEIVED(DATE(2024,3,1), DATE(2024,4,30), 500000, 0.0475, 0)This example uses basis 0 (30/360), which is the default and commonly used for corporate commercial paper. The formula determines the redemption value after accounting for the discount over the 60-day holding period.
Multi-Security Portfolio Analysis
=RECEIVED(A2, B2, C2, D2, E2)This formula references cells containing settlement dates (A2), maturity dates (B2), investment amounts (C2), discount rates (D2), and basis values (E2). This approach allows for easy replication across multiple securities and dynamic updating when parameters change.
Key Takeaways
- RECEIVED calculates maturity value for discounted securities by applying the discount rate over the holding period, essential for Treasury bill and commercial paper valuation
- The basis parameter (0-4) determines day-count convention and significantly affects results; U.S. Treasuries typically use basis 1 (actual/actual) while money market instruments use basis 2 (actual/360)
- Common errors include date sequencing mistakes, percentage vs. decimal confusion, and invalid basis values; implement validation checks to prevent these issues
- Combine RECEIVED with functions like INTRATE, PRICEDISC, and IF for comprehensive financial analysis, yield verification, and conditional calculations
- RECEIVED is specifically designed for short-term discounted instruments; for coupon-bearing bonds, use PRICE or YIELD functions instead
Pro Tips
Create a helper column to calculate the holding period in days using =DAYS(maturity, settlement). This helps verify your date inputs are correct and identifies potential date entry errors before they affect calculations.
Impact : Prevents silent errors from incorrect dates, which are among the most common sources of calculation mistakes in financial models.
Use named ranges for basis values (e.g., name 'ActualActual' = 1, 'Thirty360' = 0) to make formulas more readable and reduce errors when switching between different day-count conventions.
Impact : Improves formula clarity, reduces maintenance errors, and makes it easier for other users to understand and modify your financial models.
Build a comparison matrix showing RECEIVED calculations across multiple basis methods for the same security. This reveals the sensitivity of your valuation to day-count convention choices.
Impact : Identifies which basis method most appropriately matches your security type and reveals the materiality of basis selection on your valuation results.
Combine RECEIVED with data validation to create dropdown lists for basis selection, ensuring only valid basis values (0-4) are entered and reducing formula errors.
Impact : Prevents #NUM! errors from invalid basis values and makes your spreadsheet more user-friendly for team members unfamiliar with basis numbering conventions.
Useful Combinations
RECEIVED with IF for Conditional Maturity Value
=IF(D2>0.10, RECEIVED(A2,B2,C2,D2,1), RECEIVED(A2,B2,C2,D2,0))This combination uses IF to select the appropriate day-count basis based on the discount rate. If the discount rate exceeds 10%, it uses actual/actual basis (1), otherwise 30/360 basis (0). This enables dynamic basis selection based on market conditions or instrument type.
RECEIVED with INTRATE for Rate Verification
=INTRATE(A2, B2, C2, RECEIVED(A2,B2,C2,D2,1))This formula nests RECEIVED within INTRATE to verify calculations. It calculates the received amount, then uses it to compute the implied interest rate, which should match your discount rate assumptions. This creates a validation check for your financial model.
RECEIVED with SUMPRODUCT for Portfolio Analysis
=SUMPRODUCT(RECEIVED(A2:A10, B2:B10, C2:C10, D2:D10, E2:E10))This combination applies RECEIVED across multiple securities simultaneously using SUMPRODUCT, calculating total maturity values for an entire portfolio. It enables efficient portfolio-level analysis without individual formulas for each security.
Common Errors
Cause: The settlement date is on or after the maturity date, creating an invalid time period. This violates the fundamental requirement that securities must have a positive holding period.
Solution: Verify that the settlement date is earlier than the maturity date. Check your date inputs for accuracy using the DATE function or ensure dates are entered in the correct chronological order.
Cause: The discount rate is entered as a percentage (e.g., 5) instead of a decimal (0.05), or non-numeric values are used in numeric parameters. Excel expects decimal format for rate calculations.
Solution: Convert percentage values to decimals by dividing by 100 or entering as 0.05 instead of 5. Verify all parameters are numeric and properly formatted. Use the VALUE function if converting text to numbers.
Cause: Cell references in the formula point to deleted rows, columns, or sheets, breaking the formula's connection to source data. This commonly occurs when restructuring spreadsheets.
Solution: Check all cell references are valid and point to existing cells. Use Ctrl+` to toggle formula view and verify references. Consider using named ranges for better error detection and formula clarity.
Troubleshooting Checklist
- 1.Verify settlement date is before maturity date; RECEIVED requires positive time periods between dates
- 2.Confirm discount rate is entered as a decimal (0.05) not percentage (5); use /100 conversion if needed
- 3.Check that all date parameters use consistent date formatting; mix of text and date formats causes errors
- 4.Validate basis parameter is between 0-4; invalid basis values trigger #NUM! errors
- 5.Confirm investment amount is positive and numeric; text or negative values produce #VALUE! errors
- 6.Test formulas with known data where you can manually verify calculations; this identifies logic errors early
Edge Cases
Settlement and maturity dates are very close (1-2 days apart)
Behavior: RECEIVED calculates correctly but the received amount is very close to the investment amount, resulting in minimal gain. The basis method becomes more critical as small day-count differences have proportionally larger effects.
Solution: Verify dates are intentional; use actual/actual basis (1) for maximum precision with short holding periods
This scenario is common with overnight or very short-term money market instruments
Discount rate is extremely high (>50%) or extremely low (<0.1%)
Behavior: Excel calculates mathematically correct results, but these rates are economically unrealistic for most securities. Very high rates produce received amounts significantly higher than investment; very low rates produce minimal gains.
Solution: Verify discount rate inputs; consider whether such extreme rates reflect data entry errors or unusual market conditions
Extreme rates may indicate data quality issues or unusual market scenarios requiring special investigation
Investment amount is very large (>$1 billion) or very small (<$1,000)
Behavior: RECEIVED calculates correctly regardless of scale; the percentage gain remains constant while absolute gains scale with investment size.
Solution: No special handling required; Excel handles large and small numbers accurately with appropriate cell formatting
Ensure appropriate number formatting for readability; consider using thousands separators for large amounts
Limitations
- •RECEIVED assumes a single discount rate for the entire holding period; it cannot model securities with variable or floating discount rates that change during the holding period
- •The function does not account for credit risk, market risk, or liquidity adjustments; it performs purely mathematical calculations based on discount rate and dates without considering real-world risk factors
- •RECEIVED is specifically designed for zero-coupon or fully discounted securities and cannot be used for coupon-bearing bonds or securities with periodic interest payments; these require PRICE or YIELD functions
- •The function's accuracy depends entirely on correct date entry and basis selection; errors in these inputs produce silently incorrect results without warning, requiring careful validation and verification
Alternatives
Calculates the price per $100 face value of a discounted security, providing a normalized comparison across securities of different face values.
When: When you need to compare pricing across multiple securities with different face values or when working with market data expressed per $100 of face value.
Provides complete control over the calculation methodology and allows for custom adjustments or variations not available in built-in functions.
When: When dealing with non-standard securities, custom day-count conventions, or when integrating calculations into complex financial models requiring formula transparency.
Compatibility
✓ Excel
Since 2007
=RECEIVED(settlement, maturity, investment, discount, [basis]) - Available in all versions from Excel 2007 through Excel 365 with identical syntax✓Google Sheets
=RECEIVED(settlement, maturity, investment, discount, [basis]) - Google Sheets supports RECEIVED with identical syntax and parametersFunction behavior and results match Excel implementations; basis parameter works identically across platforms
✓LibreOffice
=RECEIVED(settlement, maturity, investment, discount, [basis]) - LibreOffice Calc includes RECEIVED with full compatibility to Excel