The DISC function in Excel gives you the discount rate of a security you buy for less than its face value. It’s meant for things like Treasury bills and commercial paper, which pay no interest along the way.
You pay a little less than face value today and get the full amount back at maturity. DISC takes that gap and turns it into a yearly rate, which is how T-bill rates are quoted.
One thing to know before you start is that DISC doesn’t work with plain ranges. If you point it at a range of dates and prices, you get a #VALUE! error instead of a list of rates, and Example 2 shows the small fix.
Follow along with the example file
DISC Function Examples.xlsx
DISC Function Syntax
Here is the syntax of the DISC function:
=DISC(settlement, maturity, pr, redemption, [basis])
- settlement – the date you buy the security.
- maturity – the date the security ends and pays you back.
- pr – the price you pay, per $100 of face value.
- redemption – the amount you get back at maturity, per $100 of face value. For a T-bill, this is 100.
- [basis] – optional. The day count method: 0 (US 30/360), 1 (actual/actual), 2 (actual/360), 3 (actual/365), or 4 (European 30/360). If you leave it out, Excel uses 0.
DISC measures the discount against the redemption value, not against the price you paid. So a bill bought at 97.85 that pays back 100 has a discount of 2.15 on 100, and DISC scales that 2.15% up to a full year.
The basis decides how the days between the two dates are counted and how many days make a year. T-bills and commercial paper are quoted on an actual/360 basis, so for those you’ll want to enter 2 instead of relying on the default.
If the price is higher than the redemption value, DISC doesn’t show an error. It returns a negative rate.
You get the #NUM! error if the settlement date is on or after the maturity date, if pr or redemption is 0 or less, or if basis is anything other than 0 to 4.
The dates should be real dates in cells, or dates built with the DATE function. If a date can’t be read as a date, DISC returns the #VALUE! error.
The DISC function works in Excel 365, 2024, 2021, 2019, and 2016 on Windows, and in Excel 365, 2024, and 2021 for Mac. The spilling formula in Example 2 needs a version with dynamic arrays (Excel 365, 2024, or 2021).
When to Use the DISC Function
Use this function when you need to:
- Find the discount rate of a Treasury bill from its price
- Compare the rates on several T-bills with one formula
- Check what the basis argument does to a quoted rate
- Get the rate from the dollar amounts on your purchase confirmation
- Work out the rate when you know the term in days instead of the maturity date
Let me show you a few practical examples of how to use the DISC function.
Example 1: Find the Discount Rate of a Treasury Bill
Let’s start with a simple example.
Below are the details of a 26-week Treasury bill bought at 97.85 per $100 of face value, and I want its discount rate in cell B7.
Here is the formula:
=DISC(B2,B3,B4,B5,2)
The formula returns 4.253%. The bill pays back $100 for every $97.85 you pay, so the discount is $2.15, which is 2.15% of the face value.
DISC then scales that to a full year. With basis 2 (actual/360), it counts the 182 actual days from November 5 to May 6 and uses a 360-day year, so 2.15% × 360/182 gives 4.253%.
I used basis 2 because that’s how the US Treasury quotes the discount rate on T-bills. Example 3 shows what changes if you leave it out.
Keep in mind that the discount rate isn’t the return on your money. It divides the discount by the $100 face value instead of the $97.85 you actually paid, so it comes out a bit lower than what you earn.
If you want the rate you actually earn on what you paid, use the INTRATE function instead.
Example 2: Get the Discount Rate for Several T-Bills With One Formula
Now let’s say you keep a log of the T-bills you’ve bought, and you want to compare their rates.
Below is a list of eight T-bills with their purchase dates, maturity dates, and prices, and I want the discount rate for each bill in column E.
Here is the formula:
=DISC(+B2:B9,+C2:C9,+D2:D9,100,2)
This one formula spills all eight discount rates into E2:E9.
The plus sign (+) in front of each range is what makes this work. With plain ranges, DISC returns a single #VALUE! error, but the plus sign turns each range into a list of values, so DISC returns one rate for each bill.
It’s the same trick that fixes other Excel functions that won’t spill when you give them a range, like the EOMONTH function.
Important: Every range needs its own plus sign, because leaving one off still spills the formula but gives wrong results. For example, without the plus sign before D2:D9, you get a #VALUE! error at the top and 2.109% for the 8-Week Bill instead of 4.179%.
The redemption value is 100 for every bill, so I typed it straight into the formula, along with the basis of 2.
Since every bill is now turned into a yearly rate, you can compare a 4-week bill with a 52-week bill directly. In this log, the 4-week bill from November has the highest rate (4.217%) and the 52-week bill has the lowest (3.850%).
The results need the cells from E2 down to E9 to be empty. If anything is in the way, you get the #SPILL! error instead.
If you are using Excel 2019 or earlier, enter the below formula in cell E2 and then copy it down the range:
=DISC(B2,C2,D2,100,2)
Example 3: See What Happens When You Leave Out the Basis
The basis argument is optional, so it’s easy to skip. Let’s see what that does to a T-bill’s rate.
Below are the details of a 13-week T-bill bought at 98.97, and I want its discount rate with basis 2 in cell B7 and with the basis left out in cell B8.
Here is the formula with basis 2:
=DISC(B2,B3,B4,B5,2)
This returns 4.075%, which is the rate the way T-bills are quoted. It takes the 1.03% discount over the 91 actual days from February 4 to May 6, on a 360-day year.
And here is the same formula with the basis left out:
=DISC(B2,B3,B4,B5)
This returns 4.030%. Without a basis, Excel uses US 30/360, which treats every month as having 30 days.
So it counts 92 days between these dates instead of the actual 91, and the same discount gets spread over one extra day. That’s the same day count the DAYS360 function gives for these two dates.
The gap here is small, but 4.030% isn’t the rate anyone would quote for this bill. So for T-bills and commercial paper, always enter 2 as the basis.
Example 4: Use the Dollar Amounts You Paid
The DISC function asks for the price per $100 of face value, but your purchase confirmation usually shows the total amounts. You can use those directly.
Below are the details of a T-bill where I paid $24,737.50 for $25,000 of face value, and I want its discount rate in cell B7.
Here is the formula:
=DISC(B2,B3,B4,B5,2)
The formula returns 4.154%, which is the same rate you’d get with the price per $100 (98.95).
This works because DISC only uses the price as a share of the redemption value. $24,737.50 out of $25,000 is the same share as 98.95 out of 100, so there’s nothing to convert.
Important: Enter the price and the redemption value in the same units. If you use the dollar price ($24,737.50) but leave redemption at 100, DISC doesn’t show an error and instead returns a wild negative rate of about -97,467%.
Example 5: Find the Discount Rate When You Know the Term in Days
Commercial paper (short-term loans that companies raise by selling notes at a discount) is usually described by its term in days, like 30, 60, or 90 days. You don’t need to work out the maturity date yourself.
Below are the details of 60-day commercial paper bought at 99.28, and I want its discount rate in cell B7 using the term in B3.
Here is the formula:
=DISC(B2,B2+B3,B4,B5,2)
The formula returns 4.320%.
Here, B2+B3 adds 60 days to the purchase date in B2, which gives March 12, 2027. DISC uses that as the maturity date, so the 0.72% discount over 60 days works out to 4.320% a year.
This works because Excel stores dates as numbers, one for each day, so adding 60 to a date moves it 60 calendar days ahead. If your term is in weeks, as it is for T-bills, multiply the weeks by 7 before you add them.
So these are five practical ways to use the DISC function in Excel.
It gives you the quoted discount rate of a T-bill or commercial paper from its price, and it works just as well on a whole list of them, on dollar amounts, and on terms in days.
If you have the quoted discount rate and want the price instead, the PRICEDISC function does the reverse.
I hope you found this Excel tutorial useful.
Other Excel Articles You May Also Like:
