How to Use the PRICEDISC and DISC Functions in Google Sheets for Discounted Securities

In fixed-income portfolio management and corporate treasury operations, many short-term debt instruments—such as commercial paper, banker’s acceptances, and zero-coupon municipal notes—do not pay periodic coupon interest. Instead, they are issued at a price lower than their face value (at a discount) and redeemed at par (typically 100 or 1,000 currency units) upon maturity. The investor’s entire financial return represents the difference between the acquisition cost and the redemption value.

When modeling these money market instruments in Google Sheets, standard bond formulas like PRICE or PRICEMAT will return errors or inaccurate figures because they expect periodic coupon rates or maturity interest accruals. To accurately value discounted securities, financial analysts use two dedicated formulas: PRICEDISC and DISC. This guide explains their mathematical logic, parameter structure, day-count conventions, and real-world spreadsheet workflows.

Understanding Discounted Money Market Securities

A discounted security pays zero nominal interest during its lifespan. The annualised rate of return is expressed as a discount rate, which measures the annualised percentage difference between the purchase price and the redemption value relative to the redemption value itself:

• Discount Basis: Unlike an investment yield (which measures return relative to the cash invested), a discount rate measures return relative to the final face value.
• Short-Term Horizon: Discounted paper generally matures within one year (commonly 30, 60, 90, 180, or 270 days).
• Clean Pricing: Because no coupon interest accumulates, the quoted clean price is identical to the dirty price.

Syntax and Parameter Breakdown

The PRICEDISC Function

The PRICEDISC function computes the fair purchase price per £100 (or $100) nominal face value given a known annual discount rate:

=PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention])

The DISC Function

The DISC function solves the reverse calculation: it computes the annualised discount rate implied by a given purchase price:

=DISC(settlement, maturity, price, redemption, [day_count_convention])

Parameter Definitions

• settlement: The transaction settlement date when ownership and funds are exchanged (must strictly precede the maturity date).
• maturity: The contractual maturity date when the issuer repays the full par redemption value.
• discount (PRICEDISC): The annualised discount rate expressed as a decimal or percentage (e.g., 0.0475 or 4.75%).
• price (DISC): The price paid per 100 face value units of the security.
• redemption: The redemption price per 100 face value units (almost universally 100).
• day_count_convention (Optional): The financial day-count basis:
  • 0 or omitted: US (NASD) 30/360.
  • 1: Actual/Actual (exact calendar days).
  • 2: Actual/360 (common for commercial paper and bankers’ acceptances).
  • 3: Actual/365 (standard UK money market convention).
  • 4: European 30/360.

Step-by-Step Modeling Example in Google Sheets

Scenario: Pricing Commercial Paper

A corporate treasury desk agrees to acquire a 90-day commercial note issued by a blue-chip enterprise with the following terms:

• Settlement Date: 1 April 2026
• Maturity Date: 1 July 2026
• Agreed Annual Discount Rate: 4.60% (0.046)
• Redemption Par: 100
• Day Count Basis: Actual/360 (Option 2)

Set up your Google Sheets model as follows:

A1: Parameter              | B1: Value
A2: Settlement Date         | B2: 2026-04-01
A3: Maturity Date           | B3: 2026-07-01
A4: Annual Discount Rate    | B4: 4.60%
A5: Redemption Face Value   | B5: 100
A6: Day Count Convention    | B6: 2
A7: Calculated Clean Price  | B7: =PRICEDISC(B2, B3, B4, B5, B6)

Google Sheets evaluates the equation and returns 98.84 per 100 face value. To purchase £1,000,000 face value of this commercial note, the investor transfers £988,389 upon settlement and receives £1,000,000 upon maturity.

Auditing the Implied Discount Rate with DISC

If a secondary market dealer offers the same paper at an ask price of 98.75, verify the dealer’s quoted discount rate using DISC:

=DISC(B2, B3, 98.75, B5, B6)

The formula returns 4.95%, indicating that acquiring the note at 98.75 yields a higher annualised discount return than the initial 4.60% benchmark.

Day-Count Convention Comparison Matrix

Because discounted money market notes typically span fewer than 365 days, selecting the wrong day-count basis materially misprices high-value institutional trades:

Basis Code	Convention Name	Standard Financial Application	Formula Example
0	US (NASD) 30/360	Corporate commercial paper, short-term muni debt	=PRICEDISC(B2, B3, B4, B5, 0)
1	Actual/Actual	Short-term sovereign paper, government agency debt	=PRICEDISC(B2, B3, B4, B5, 1)
2	Actual/360	US Treasury bills, Euro commercial paper (ECP)	=PRICEDISC(B2, B3, B4, B5, 2)
3	Actual/365	UK Sterling commercial paper, Commonwealth notes	=PRICEDISC(B2, B3, B4, B5, 3)
4	European 30/360	Continental European money market instruments	=PRICEDISC(B2, B3, B4, B5, 4)

Troubleshooting Common Spreadsheet Errors

• #NUM! Error: Occurs when settlement >= maturity or when discount <= 0 or price <= 0. Verify that settlement precedes maturity and that discount rates are positive values.
• #VALUE! Error: Occurs if dates are passed as unrecognised text strings. Always use formatted spreadsheet dates or the DATE(year, month, day) constructor.
• Discount Rate vs Bond Equivalent Yield: A discount rate understated return relative to true investment yield. To convert an Actual/360 money market discount to an investment yield (BEY), use the TBILLYIELD function or explicit cash flow compounding.