MS Excel: DISC function for discounted price calculation

The DISC function in Excel calculates the discounted price of a security based on the discount rate and face value. It’s commonly used in finance to determine the discount amount for securities such as Treasury bills or bonds that are sold at a discount.

The DISC function is based on the discount yield and is primarily used to compute the discounted price of a financial instrument before its maturity date.

Syntax

DISC(settlement, maturity, price, redemption, basis)

Argument	Description
settlement	The settlement date of the security, i.e., the date after issuance when the security is traded to the buyer.
maturity	The maturity date of the security, i.e., when the principal is repaid.
price	The price of the security per $100 face value.
redemption	The redemption value of the security per $100 face value (usually $100).
basis	The day count basis to be used for the calculation: 0 = US (NASD) 30/360 1 = Actual/actual 2 = Actual/360 3 = Actual/365 4 = European 30/360

Key Points

• The DISC function is primarily used to calculate the discounted price of a security sold at a discount from its face value.

• It helps you determine how much you would pay for a security, considering its discount rate.

• The result is typically the discounted price of a security based on the given settlement and maturity dates.

Practical Example

Scenario:

You are interested in calculating the discounted price of a Treasury bill. The face value is $1,000, the price of the security is $950, and the redemption value is $1,000. The settlement date is January 1, 2025, and the maturity date is January 1, 2026. Use the actual/actual day count basis.

Formula:

=DISC("2025-01-01", "2026-01-01", 950, 1000, 1)

Explanation:

• settlement: The settlement date is January 1, 2025.

• maturity: The maturity date is January 1, 2026.

• price: The price of the security is $950.

• redemption: The redemption value is $1,000 (face value).

• basis: The day count basis is 1, meaning actual/actual.

Result: 0.05263 or 5.263%

Explanation:This means the discount yield of the security is 5.263%. The function calculates how much of a discount there is based on the face value and price at settlement.

Another Example:

Let’s say you want to calculate the discounted price for a security with a face value of $500, settlement date of March 1, 2025, maturity date of March 1, 2026, price of $480, and redemption value of $500. Use the 30/360 day count basis.

Formula:

=DISC("2025-03-01", "2026-03-01", 480, 500, 0)

Result: 0.04074 or 4.074%

Explanation:This means the discount yield is 4.074%, which reflects the discounted price of the security based on the settlement and maturity dates, along with the price and redemption values.

Summary

Item	Value
Face Value (Redemption)	$1,000
Price	$950
Settlement Date	January 1, 2025
Maturity Date	January 1, 2026
Day Count Basis	Actual/Actual
Discount Yield	5.263%

Important Notes

• The DISC function is useful for determining the discounted price of a security before maturity.

• It uses the discount yield to compute how much you would pay for the security based on its current price and face value.

• If the basis argument is omitted, Excel uses the US (NASD) 30/360 method by default.

• The redemption value is typically $100 for most securities unless otherwise specified.

When to Use DISC?

• To calculate the discounted price of securities such as Treasury bills or bonds.

• For financial analysis and evaluating discounted securities in investment portfolios.

• When working with securities that are sold at a discount from their face value.

• In tax reporting, where the discount yield on securities may impact your income calculations.

Conclusion

The DISC function in Excel is an essential tool for calculating the discount yield and discounted price of securities. It’s particularly useful for understanding how much to pay for discounted securities, such as Treasury bills, based on their face value and purchase price.