While standard bonds distribute periodic coupon interest on a semi-annual or annual basis, many money market instruments—such as commercial paper, certificates of deposit (CDs), and non-callable short-term notes—are fully invested securities. These instruments generate returns entirely through interest accrued and disbursed at maturity rather than through regular coupons. In Google Sheets, evaluating such investments without manual day-count formulas is streamlined by two specialized financial functions: INTRATE and RECEIVED.
Core Financial Concepts: INTRATE vs RECEIVED
Both functions evaluate non-periodic securities, but they solve for opposite sides of the investment equation:
INTRATE: Calculates the effective annualised interest rate generated by a security based on the initial capital invested and the final amount redeemed at maturity.RECEIVED: Calculates the final total amount received at maturity for a fully invested security given the initial investment sum, investment dates, and stated discount or interest rate.
Syntax and Parameter Signatures
Both functions require accurate calendar dates and financial day-count conventions:
=INTRATE(settlement, maturity, investment, redemption, [day_count_convention])
=RECEIVED(settlement, maturity, investment, discount, [day_count_convention])The parameter breakdown is structured as follows:
| Parameter | Function Scope | Data Type | Description |
|---|---|---|---|
settlement |
Both | Date | The date after issue when the security is delivered to the buyer. Use DATE(). |
maturity |
Both | Date | The final expiration date when the instrument matures. Must be later than settlement. |
investment |
Both | Currency / Number | The principal purchase price paid for the security (must be positive). |
redemption |
INTRATE |
Currency / Number | The total redemption amount received at maturity, including principal and interest. |
discount |
RECEIVED |
Percentage / Number | The annualised discount rate applied to the security (e.g. 0.045 for 4.5%). |
day_count_convention |
Both (Optional) | Integer | 0: US 30/360 (default), 1: Actual/Actual, 2: Actual/360, 3: Actual/365, 4: European 30/360. |
Step-by-Step Implementation in Google Sheets
To construct an automated valuation model in Google Sheets:
- Open your spreadsheet and assign your financial parameters into structured cells:
- Cell
B2(Settlement Date):=DATE(2026, 3, 15) - Cell
B3(Maturity Date):=DATE(2026, 12, 15) - Cell
B4(Initial Capital Invested):£100,000 - Cell
B5(Maturity Redemption Payout):£103,850 - Cell
B6(Day Count Basis):3(Actual/365 for UK Gilts/Sterling paper)
- Cell
- To calculate the effective annual interest rate of this investment, enter in cell
B8:=INTRATE(B2, B3, B4, B5, B6)Google Sheets returns
5.12%annualised yield. - To calculate the expected gross payout from a discounted £50,000 corporate paper note purchased at a 4.25% discount rate over the same period, enter in cell
B9:=RECEIVED(B2, B3, 50000, 4.25%, B6)
Day-Count Basis Selection and Yield Accuracy
Because short-term securities mature within weeks or months, selecting the correct day-count convention is critical to avoid basis-point discrepancies:
| Basis Value | Convention Name | Standard Financial Markets |
|---|---|---|
0 |
US (NASD) 30/360 | US Corporate bonds, Municipal debt |
1 |
Actual/Actual | US Treasury Bills and Sovereign Government Bonds |
2 |
Actual/360 | Euro-commercial paper, Money market interbank deposits |
3 |
Actual/365 | UK Sterling commercial paper, Commonwealth sovereign debt |
4 |
European 30/360 | Eurozone corporate and municipal notes |
Troubleshooting Common Calculation Errors
When modeling fixed-income cash flows, address these common formula flags:
#NUM!: Occurs whensettlement≥maturity, or wheninvestment≤ 0, or whenredemption≤investment.#VALUE!: Caused by invalid date inputs entered as plain unformatted text. Ensure all dates are wrapped inDATE(YYYY, MM, DD)or referenced from validated date cells.