How to Use the DISC Function in Excel
Introduction
This article delves into the DISC function available in Microsoft Excel and Google Sheets. This function is designed to compute the discount rate of a security, incorporating critical data points such as the settlement date, maturity date, rate, and yield. It is a valuable tool in financial analysis for assessing the worth of investment securities.
Syntax
The syntax for the DISC function is consistent across both Excel and Google Sheets:
=DISC(settlement, maturity, pr, redemption, frequency, [basis])
- Settlement: The date when the security is to be delivered and payment must be made.
- Maturity: The expiration or due date of the security.
- Pr: The price of the security per $100 of face value.
- Redemption: The redemption value per $100 of face value at maturity.
- Frequency: The frequency of coupon payments per year (e.g., 1 for annual, 2 for semi-annual).
- Basis (optional): The day count convention to be used in calculation, denoted as an integer.
Examples
Example 1: Basic Usage
Let’s compute the discount rate for a security with these attributes:
Settlement | Maturity | Pr | Redemption | Frequency |
---|---|---|---|---|
1-Jan-2022 | 1-Jan-2025 | 95 | 100 | 2 |
To achieve this, input the following formula in Excel or Google Sheets:
=DISC("1-Jan-2022", "1-Jan-2025", 95, 100, 2)
This formula will return the discount rate for the specified security.
Example 2: Using Basis
Here, we calculate the DISC using a particular day count basis:
Settlement | Maturity | Pr | Redemption | Frequency | Basis |
---|---|---|---|---|---|
1-Jan-2022 | 1-Jan-2025 | 95 | 100 | 2 | 1 |
Include the basis parameter in the formula as follows:
=DISC("1-Jan-2022", "1-Jan-2025", 95, 100, 2, 1)
This computation will determine the discount rate using the specified day count basis.
Conclusion
The DISC function in Excel and Google Sheets serves as an effective mechanism for evaluating the discount rates of securities based on various parameters. Familiarity with its syntax and function will facilitate complex financial analyses efficiently.
More information: https://support.microsoft.com/en-us/office/disc-function-71fce9f3-3f05-4acf-a5a3-eac6ef4daa53