The Mystery of the Discounted Bond
Unlike standard corporate bonds that pay you interest every six months, many government bonds (like Treasury Bills) are “Zero-Coupon” bonds. They do not pay regular interest. Instead, the government sells you the bond at a massive discount today, and then pays you the full face value when the bond eventually matures. Your entire profit is simply the mathematical difference between the discounted price you paid and the final payout.
If you are looking at a financial portfolio, you might see that you purchased a $100,000 Treasury Bill for $96,500. It is easy to calculate that you made a flat $3,500 profit. However, a raw dollar amount is useless for financial analysis. To properly compare this bond against other investments (like a stock market index fund or a savings account), you must know the exact percentage of the discount rate.
Calculating the true discount rate manually is a nightmare of fractional algebra, especially because the bond might have an odd lifespan (e.g., 214 days instead of a perfect year). To instantly reverse-engineer the math and reveal the exact percentage rate of the discount, you must use the DISC (Discount Rate) function in Microsoft Excel.
Understanding the Syntax
The DISC function is highly specialized. It requires five specific pieces of data to perform its reverse-engineering calculation.
=DISC(settlement, maturity, pr, redemption, [basis])
- settlement: The exact date you originally purchased the bond at the discounted price.
- maturity: The exact date the bond officially expires and you receive the final payout.
- pr (Price): The discounted price you paid, expressed per $100 of face value.
- redemption: The final payout amount per $100 of face value (this is almost always exactly
100). - [basis]: (Optional) The day-count methodology used by the market. For US Treasury Bills, this is usually
2(Actual/360).
Example 1: Calculating the Discount Rate
Assume you are auditing a massive corporate investment portfolio. You see that the company purchased a massive block of Treasury Bills on February 1, 2024. The bills are scheduled to mature on August 15, 2024.
The total face value of the bills is $1,000,000, but the company only paid $975,000 for them. Your Chief Financial Officer (CFO) demands to know the exact percentage discount rate the company secured on that transaction.
Before you run the formula, you must calculate the Price (pr) variable. You take the price paid ($975,000) and divide it by the face value ($1,000,000). The result is 0.975. Multiply by 100, and your Price variable is 97.5.
Let’s map out the variables cleanly in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 2, 1) - Cell A2 (Maturity):
=DATE(2024, 8, 15) - Cell A3 (Price):
97.5 - Cell A4 (Redemption):
100 - Cell A5 (Basis):
2
To reveal the hidden percentage, click on cell B1 and type:
=DISC(A1, A2, A3, A4, A5)
How this works:
- Excel calculates the exact physical number of days between February 1st and August 15th.
- It analyzes the gap between the discounted price (97.5) and the final payout (100).
- It mathematically calculates the exact annualized discount rate required to generate that specific profit in that specific timeframe.
- It instantly outputs 0.0461.
If you highlight cell B1 and click the “%” button on the Excel toolbar, it will format the number beautifully as 4.61%. You can confidently report to the CFO that the corporate treasury secured a 4.61% discount rate on the transaction.
The Error Traps
If the DISC function instantly crashes and returns a #NUM! error, verify your dates immediately. The Settlement date must be strictly older than the Maturity date. The function cannot mathematically calculate a discount rate if time is flowing backward.
By mastering the DISC function, you gain the ability to forensically audit past financial transactions and instantly determine the true mathematical value of any zero-coupon bond.