The Math of the Discounted Bond
In the world of high-level finance, not all bonds pay you interest every six months. The United States Government issues specific types of bonds—like Treasury Bills—that do not pay a single penny of regular interest. Instead, they are “Zero-Coupon” or “Discounted” bonds.
The math works entirely backward. The government promises to hand you exactly $10,000 on a specific date in the future (the maturity date). Because you are not receiving any regular interest payments along the way, the government sells you the bond today for significantly less than $10,000. For example, they might sell it to you for $9,500. Your entire profit is simply the $500 difference when the bond finally matures.
If you want to buy or sell one of these discounted bonds on the secondary market before it matures, you need to calculate exactly what it is worth today, based on the current market interest rates. To instantly calculate the exact, fair market value of a zero-coupon bond, you must use the highly specialized PRICEDISC (Price of a Discounted Bond) function in Microsoft Excel.
Understanding the Syntax
The PRICEDISC function is significantly simpler than the standard PRICE function because it completely ignores regular interest payments (since there are none).
=PRICEDISC(settlement, maturity, discount, redemption, [basis])
- settlement: The exact date you are physically purchasing (or selling) the bond.
- maturity: The exact date the bond officially expires and the government pays out the massive lump sum.
- discount: The current market discount rate (the prevailing interest rate for this specific type of asset).
- redemption: The final payout amount per $100 of face value (this is almost always exactly
100). - [basis]: (Optional) The specific day-count methodology used by the financial market. For US Treasury Bills, this is usually
2(Actual/360). If you omit it, Excel defaults to a standard 30/360 banking calendar.
Example 1: Pricing a Treasury Bill
Assume it is March 1st, 2024. You want to buy a Treasury Bill from another investor on the secondary market. The Treasury Bill officially matures (expires) on September 1st, 2024. The current market discount rate for short-term government debt is currently hovering at 5.2%.
What is the exact mathematical price you should pay the investor today to acquire that bond?
Let’s map out the variables in your spreadsheet. (Always use the DATE function to prevent Excel from confusing American and European date formats).
- Cell A1 (Settlement):
=DATE(2024, 3, 1) - Cell A2 (Maturity):
=DATE(2024, 9, 1) - Cell A3 (Discount Rate):
5.2% - Cell A4 (Redemption):
100 - Cell A5 (Basis):
2
To calculate the exact fair market price, click on cell B1 and type:
=PRICEDISC(A1, A2, A3, A4, A5)
How this works:
- Excel calculates the exact number of physical calendar days between March 1st and September 1st.
- It analyzes the 5.2% market rate.
- It calculates the exact mathematical discount required to guarantee that 5.2% return over that specific timeframe.
- It instantly outputs 97.34.
Interpreting the Output
Like all professional bond pricing tools in Excel, the number 97.34 is not the total cash price. It is the price per $100 of face value.
If the Treasury Bill you are buying has a massive face value of $50,000, you must mathematically convert Excel’s output to find the final cash price. You simply take the 97.34, divide it by 100, and multiply it by $50,000.
The result is $48,670. You should hand the investor exactly $48,670 today. When the bond matures in September, the government will hand you $50,000, generating a perfectly calculated profit. By mastering the PRICEDISC function, you remove the guesswork from trading zero-coupon government debt.