How to Use the DISC and RECEIVED Functions in Microsoft Excel for Discounted Securities Valuation

Short-term debt securities—such as Treasury bills, commercial paper, and bankers’ acceptances—do not pay periodic interest coupons. Instead, these money market instruments are issued at a discount relative to their par face value and redeem at full par upon reaching maturity. Microsoft Excel provides two dedicated financial functions designed specifically for modelling these instruments: DISC, which calculates the implied discount rate of a security, and RECEIVED, which determines the total capital received at maturity for a fully invested discounted bond.

Syntax and Parameter Requirements for the DISC Function

The DISC function computes the annual discount rate of a zero-coupon or discounted security based on its purchase price, redemption value, and holding period.

DISC(settlement, maturity, pr, redemption, [basis])

The function accepts five parameters:

• settlement: The settlement date when the security is acquired (must be a valid Excel serial date, typically produced using DATE(year, month, day)).
• maturity: The legal maturity date when the security expires and redeems.
• pr: The purchase price per £100 (or $100) face value.
• redemption: The redemption face value per £100 (standardly 100).
• basis (optional): The day-count convention utilised by the underlying money market:
  • 0 or omitted: US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360 (predominant standard for US Treasury bills and commercial paper)
  • 3: Actual/365 (standard convention for UK Treasury bills and gilt repos)
  • 4: European 30/360

Calculating the Discount Rate with DISC

Consider an investor purchasing a Treasury bill on 15 March 2026 that matures on 15 September 2026. The purchase price is £97.80 per £100 par value, and the market follows the Actual/360 day-count standard.

Structure your worksheet entries as follows:

• Cell A2 (Settlement): =DATE(2026, 3, 15)
• Cell B2 (Maturity): =DATE(2026, 9, 15)
• Cell C2 (Price): 97.80
• Cell D2 (Redemption): 100
• Cell E2 (Basis): 2

To calculate the annual discount rate, enter this formula in cell F2:

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

Excel returns approximately 0.0431. Format cell F2 as a Percentage with two decimal places (4.31%). This represents the bank discount yield of the instrument.

Syntax and Parameter Requirements for the RECEIVED Function

While DISC determines the percentage discount rate, the RECEIVED function answers the reverse investment question: given an initial investment sum and a known discount rate, what total cash amount will be collected at maturity?

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

The parameters operate as follows:

• settlement: The acquisition date of the security.
• maturity: The maturity date.
• investment: The actual total currency amount invested at settlement.
• discount: The security’s annual discount rate expressed as a decimal or percentage (e.g. 4.25% or 0.0425).
• basis (optional): The day-count convention (0 through 4, matching the DISC convention).

Calculating Maturity Payouts with RECEIVED

Suppose a corporate treasury department invests £250,000 into six-month commercial paper on 1 April 2026, maturing on 1 October 2026, quoted at an annual discount rate of 4.50% using an Actual/365 day-count convention.

Configure the parameters across cells:

• Cell A5 (Settlement): =DATE(2026, 4, 1)
• Cell B5 (Maturity): =DATE(2026, 10, 1)
• Cell C5 (Investment): 250000
• Cell D5 (Discount Rate): 0.045
• Cell E5 (Basis): 3

Calculate the final payout at maturity in cell F5:

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

Excel calculates the total proceeds receivable upon redemption, yielding approximately £255,758.11. Subtracting the initial investment (F5 - C5) yields the total net interest earnings of £5,758.11.

Troubleshooting Common Date and Error Values

Financial functions in Excel are sensitive to date ordering and data types. Common error conditions include:

• #NUM! Error: Occurs if settlement >= maturity, if pr <= 0, if redemption <= 0, or if discount <= 0. Ensure maturity strictly succeeds settlement.
• #VALUE! Error: Occurs when settlement or maturity dates are entered as plaintext strings rather than valid serial date serials. Always use DATE() or validated date cells.
• Basis Mismatches: Entering a basis outside the integer range 0–4 produces a #NUM! error. Confirm local sovereign market conventions (such as US Treasuries using 2 and UK gilts or sterling commercial paper using 3) to prevent pricing inaccuracies.