How to use DISC Function in Excel

The DISC Function is an Excel Financial function. It will calculate the rate of discount for a bond. It helps to know the discount rate when we know the other details about the bond

Syntax:= =DISC (settlement, maturity, pr, redemption, [basis])

The DISC function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Pr    Required. The security’s price per $100 face value.
  • Redemption    Required. The security’s redemption value per $100 face value.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4

European 30/360

Example: Let’s look at some Excel DISC function examples and explore how to use the DISC function as a worksheet function in Microsoft Excel:

DISC Function - How to use DISC Function in Excel

Syntax:  =DISC(B1,B2,B3,B4,B5)

Result:

DISC Function in Excel 1 - How to use DISC Function in Excel

Based on the Excel spreadsheet above, the following DISC examples would return:

Syntax: =DISC(B1,B2,B3,B4,B5)
Result: 0.051455399

Syntax: =DISC(DATE(2015,7,6),DATE(2020,5,15),750,1000,1)
Result: 0.051455399

Note:

  • #NUM! error – Occurs when:
    • The given maturity date is less than or equal to the settlement date.
    • Inputs for the arguments pr, redemption or basis are invalid numeric values. That is, if pr is less than or equal to 0, if redemption is less than or equal to 0, or basis given in the formula is not equal to 1, 2, 3, or 4.
  • Settlement, maturity, and basis are truncated to integers.
  • Dates are stored in sequential serial numbers, so they can be used in calculations. By default, January 1, 1900 is serial number 1, and July 16, 2016 is serial number 42567 because it is 42,567 days after January 1, 1900.
  • #VALUE! error – Occurs when:
    • The given maturity date or settlement date is not a valid Excel date.
    • Any of the given argument is a non-numeric value.

Add a Comment

Your email address will not be published. Required fields are marked *