What Is DISC Excel Function?
The DISC Excel function is a powerful tool used to calculate the discount rate for a security. It allows users to determine the yield of a security based on its price, settlement date, maturity date, and redemption value. By inputting these variables into the function, Excel can quickly and accurately calculate the discount rate, providing valuable insights for investors and financial analysts.
The DISC Excel function is particularly useful in bond pricing and analysis, as it helps to assess the attractiveness of an investment by determining the return that can be expected.
In the example, using the given values in the table we enter the formula shown below using the DISC Excel function.
The entered formula is =DISC (B1, B2, B3, B4)
The result obtained is 4%.
Table of contents
Key Takeaways
- The DISC Function in Excel is used to calculate the rate of discount for a bond based on certain details such as settlement date, maturity date, price, redemption value, and day count basis. It helps determine the discount rate when other bond details are known.
- Excel DISC Function formula is =DISC (settlement, maturity, pr, redemption, [basis]).
- Key financial terms such as settlement, maturity, and basis are simplified to whole numbers.
- Dates are represented as sequential serial numbers for easy calculations, with January 1, 1900, being assigned serial number 1 and July 16, 2016, assigned serial number 42567, representing the number of days since January 1, 1900.
Syntax
- Settlement – This is the mandatory argument. This is the date when the coupon was bought and settled or the date the security is traded to the buyer after issuance.
- Maturity – This is the mandatory argument. This is the date when the security reaches maturity or expires.
- Pr – This is the mandatory argument. The security’s redemption value is the amount that will be paid out for every $100 face value of the security.
- Redemption – This is the mandatory argument. The security’s redemption value is the amount that will be paid out for every $100 face value of the security.
- Basis – (Optional) This document specifies the day count basis that will be utilized.
| Basis | Day Count Basis |
|---|---|
| 0 | US(NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
How To Use DISC Function In Excel?
To effectively utilize the DISC function in Excel, follow these steps.
#1 – Access From The Excel Ribbon
- Select an empty cell and go to the Formulas tab.
- Now, go to the Excel ribbon and click on the Financial option.
- Look in the drop-down menu and select the DISC option.
- The Function Arguments window opens. Now, input the values for settlement, maturity, pr, redemption, and basis. Click OK.
#2 – Enter The Worksheet Manually
Step 1: Enter the formula in the assign a vacant cell for the output, input =DISC( or type =D And double-click on DISC from Excel’s suggestions.
Step 2: Press Enter Key.
Examples
Example #1
The DISC formula helps calculate discount rates for security. For example, a $20,000 redemption value of a security bond with a pr value of $10,000 with settlement date and maturity date can be shown using a table.
Step 1: Insert the DISC formula in cell B5 to calculate the number of coupons for bonds.
=DISC (B1, B2, B3, B4, 4)
Step 2: We can see the result as shown in the below image.
Example #2
Let us learn how to calculate the discount rate of security for an asset purchased for $100 on 01/01/2021. The asset has a basis is 1, and a redemption value is 120. We can calculate the discount rate of security using the DISC Excel function.
Step 1: Select cell B6 and enter the DISC Excel formula. Here, we are giving the reference values of the data stored in the table. Remember, the dates should be in the DATE format and not as text, as it could lead to problems in the calculation. Else when using the date values in the formula, you may use the DATE excel function.
= DISC (B1, B2, B3, B4, 1)
Step 2: We can see the result as shown in the below image.
Example #3
An asset was purchased for $1000 with a settlement date of January 20, 2000, with a redemption value of 2000. The asset’s maturity date is December 31, 2020. We will calculate the result with the Actual/360 basis value using the DISC Excel function.
Let’s start the calculation by entering the values as prescribed below;
Step 1: Insert the formula to calculate the number of coupons payable in cell B6.
Step 2: The complete formula is = DISC (B1, B2, B3, B4, 2)
Step 3: We can see the result as shown in the below image.
Important Things To Note
- #NUM! The error occurs when the maturity date is equal to or earlier than the settlement date, when the values for pr, redemption, or basis are less than or equal to 0, or when the basis in the formula is not 1, 2, 3, or 4.
- The #VALUE! error in Excel occurs when the maturity date or settlement date is not a valid Excel date or when any of the arguments is a non-numeric value.
Frequently Asked Questions (FAQs)
• One of the primary benefits of the DISC function is gaining insights into one’s strengths and areas for development, leading to increased self-awareness and personal growth.
• It helps managers tailor their leadership style to effectively motivate and engage their team members based on their unique personality profiles.
• One limitation is that the results generated by the DISC assessment are based on self-reported information provided by the individual taking the assessment. This means that there is a possibility of biases or inaccuracies in the data collected, which could affect the reliability and validity of the results.
• It is important to note that the DISC assessment measures personality traits and behavioral tendencies, but it may not capture other important factors such as skills, abilities, or values.
The accuracy of the output of the DISC function, a financial function used in Excel to calculate the discount rate for a security, is generally quite high in comparison to manual calculations. The function takes into consideration various factors such as settlement dates, maturity dates, coupon payments, and par values to determine the discounted price of a security.
This automated process not only saves time but also reduces the likelihood of human error, resulting in more precise results. However, it is important to note that we may get discrepancies if we enter the incorrect inputs or if there are errors in data formatting.
Download Template
This article must help us understand the DISC Excel Function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to DISC Excel Function. Here we explain how to use DISC function with examples & downloadable excel template. You can learn more from the following articles. –
Leave a Reply