The DISC function in Excel is used to calculate the discount rate for a security. This function is typically used in financial analysis to determine the discount rate given the settlement date, maturity date, price, and redemption value of a security. Here’s how you can use the DISC function:
Syntax
DISC(settlement, maturity, pr, redemption, [basis])
- settlement: The settlement date of the security. This is the date after the security is traded to the buyer.
- maturity: The maturity date of the security. This is the date when the security expires.
- pr: The price per $100 face value of the security.
- redemption: The redemption value per $100 face value of the security.
- basis (optional): The type of day count basis to use. The options are:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Assume you have a bond with the following details:
- Settlement date: January 1, 2023
- Maturity date: July 1, 2023
- Price: $97
- Redemption value: $100
- Basis: 0 (US 30/360)
You would set up your Excel function like this:
=DISC(DATE(2023,1,1), DATE(2023,7,1), 97, 100, 0)
Steps to Implement
- Enter Dates: First, ensure that the settlement and maturity dates are correctly formatted as date values in Excel.
- Enter Values: In adjacent cells, input the security’s price and redemption values.
- Use the Function: Click on the cell where you want to display the discount rate and enter the DISC function using the syntax above.
- Finalize: Hit enter, and Excel will compute the discount rate based on the inputs provided.
Notes
- Make sure that the settlement date is before the maturity date; otherwise, the function will return an error.
- Ensure that all parameters are input correctly, particularly the dates and prices, to avoid any errors in calculation.
- The function is particularly useful in financial modeling for short-term treasury bills and similar investments.
By using the DISC function, you can quickly and easily determine the discount rate for financial instruments, aiding in investment analysis and decision-making.
Unlock Your Potential
Excel
Access
Power BI
