DISC Function

What is the DISC Function?

The DISC function is a Finance function in Excel that calculates a bond or security discount rate. It returns the discounted rate as a percentage, which helps you understand other details about the bond.

The syntax for Disc is

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

Where

• settlement = the purchase date of the coupon. It is also known as the security settlement date or the date after which the security was traded to the buyer.
• maturity = maturity date of the security or the expiration date of the security.
• pr = security price per $100 face value.
• redemption = security redemption value per $100 face value.
• basis [optional] = day count basis.
  • 0 = US (NASD) 30/360
  • 1 = Actual/actual
  • 2 = Actual/360
  • 3 = Actual/365
  • 4 = European 30/360.

This article will teach how to calculate DISC through a step-by-step guide and demonstrate some examples of this function.

Examples of DISC function

When finding a bond's discounted rate, we will need a settlement date, a maturity date, the security price, and the redemption price. Once we have those values, we will insert them into the parentheses.

Now that we’ve imputed our arguments into the syntax, we will return with the discount rate. In this case, our discount rate is 0.9 or 90%.

Now, let’s look into the DISC function in greater detail by looking at some examples.

We have the settlement, maturity, security, and redemption dates listed for Alex, Sarah, Pedro, Jasmine, and Carl. We want to find out their discount rates.

First, let’s look at the information from Alex. His settlement and maturity dates are listed as January 15th, 2014, and July 5th, 2015, respectively, with a security price of $84 and a redemption price of $95.

By inputting all those numbers in the DISC formula, Alex's discount rate for 2014 is 25%.

Now, let’s look at Sarah. Her settlement and maturity dates are listed (March 6th, 2015, and August 20th, 2016, respectively), and she has a security and redemption price of $72 and $130, respectively.

Once we’ve inputted those integers in the syntax, Sarah’s discount rate was 98% in 2015.

Next, let’s look at Pedro’s information. His settlement and maturity dates are February 1st, 2016, and June 5th, 2016, respectively. His security and redemption price is $88 and $75, respectively. We found that Pedro’s discount rate was -50% from the information above.

Pedro has a negative discount because his security price is higher than his redemption price.

Then, let’s see Jasmine’s information. She has a settlement and maturity date of April 12th, 2017, and December 10th, 2017, respectively, with a security and maturity price of $100 each. Based on the information gathered, we found that Jasmine has a 0% discount.

Jasmine got a 0% discount because her security price = maturity price.

Finally, let’s see the information presented by Carl. He has a settlement date of May 6th, 2018, and a maturity date of October 11th, 2018, with a security and redemption price of $25 and $50, respectively. Carl has a 116% discount rate based on the information provided.

DISC vs. YIELDDISC Function

Now that we’ve learned how DISC works let’s assume you want to find the annual yielded discount. We can find the annual yield for a discounted security using the YIELDDISC function.

YIELDDISC is a very useful tool for financial analysts. It calculates the yield on a bond to find the generated income from a specific year. It’s not the same as YIELD, as YIELDDISC calculates the yield on a discounted security.

The syntax for YIELDDISC is

= YIELDDISC(settlement, maturity, pr, redemption, [basis])

Where

• settlement = settlement date of the security.
• maturity = maturity date of that security.
• pr = price of the security per face value of $100.
• redemption = redemption value of the security per face value of $100.
• basis [optional argument] = financial day count basis from 1 to 4
  • 0 (defaulted/omitted basis) = US(NASD) 30/360
  • 1 = actual/actual
  • 2 = actual/360
  • 3 = actual/365
  • 4 = European 30/360

To find the yielded discount, you’ll need to have: a settlement date, maturity date, security price, and redemption price. You must incorporate those values inside the syntax to find the yield discount.

Once you’ve done that, hit enter, and you’ll get the yielded discount, which in this case, it’s approximately 105.9%. As you can also see, it’s not the same answer as the discount rate because it’s a yielded discount.

Using examples, let’s look at how to calculate this function in greater detail. We’ll use the same examples from the DISC function and find the yielded discount.

For Alex, we can see that there’s not a massive difference between his rate of discount and yield discount. So we put 1/15/2014 for our settlement, 7/5/2014 for the maturity, $84 for the PR, and $95 for the redemption, and Alex got a yielded discount of 28%.

For Sarah, the difference between DISC and YIELDDISC is over 100%. All the dates and prices remain constant, like in the last example, but she got 177% for her YIELDDISC.

For Pedro, we still see that his discount is negative because his security price is higher than the redemption. While inputting all the dates and prices into the formula, we see that his YIELDDISC is -43%, which is a slightly bigger number (closer to 0) but still negative.

Jasmine’s discount remained constant throughout. As a result, she has both a 0% discount and a yielded discount because her security price equals her redemption price.

Carl has the biggest difference in DISC and YIELDDISC, with a 116% percent increase in the YIELDDISC at 232%.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• CORREL Function
• COUNT Function
• MEDIAN Function
• MID Function
• YEAR Function