What is PRICEDISC in Google Sheets?
The PRICEDISC function in Google Sheets calculates the price of a discounted security (e.g., bond) based on its face value, discount rate, and the number of days until maturity. This financial function helps determine the price you’d pay for such a security, given its yield and other relevant parameters.
The function is handy for discount securities without interest, where the price is lower than the face value, and the difference represents the return on investment. We can use the PRICEDISC function in Google Sheets to calculate the price of a discounted security like a Treasury bill. For example, let us find the price of a $100 treasury bill maturing in 6 months with a 6% annual discount rate. Use the following formula.
=PRICEDISC(DATE(2022,1,1), DATE(2022,7,1), 0.06, 100)
This will return the purchase price of the T-bill.
Key Takeaways
- PRICEDISC is a function in Google Sheets that returns the price per $100 face value of a discounted security that does not pay periodic interest.
- It is particularly useful for evaluating the present cost of short-term investments, such as Treasury bills, based on their discount rate and maturity.
- The function will return an error if the settlement date is the same as or later than the maturity date. The dates should also be entered in the correct format.
- PRICEDISC assumes the security is issued at a discount and will be redeemed at full face value upon maturity. Hence, it is suitable for discount-based financial instruments.
Syntax
The syntax of the PRICEDISC in Google Sheets is as follows:
=PRICEDISC(settlement, maturity, discount, redemption, [basis])
Arguments:
- settlement: The date the security is purchased. This should be after the issue date.
- maturity: The date the security expires and the redemption value is paid.
- discount: The discount rate of the security at the time of purchase.
- redemption: The redemption value of the security, is expressed as a percentage of the $100 face value.
- [basis]: An optional argument specifying the day count convention. It defaults to 0 if omitted. (US (NASD) 30/360). Other options are:
| Basis | Description |
|---|---|
| 0 | US (NASD) 30/360 (default) |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
How to Use PRICEDISC function in Google Sheets?
PRICEDISC is used to calculate the price per $100 face value of a discounted security that does not pay interest. It stands for Price at a Discount. It is useful for financial analysts dealing with short-term bond investments or discounted securities. The PRICEDISC function can be used either manually or from the Google Sheets menu.
Entering PRICEDISC Manually
Let us look at a simple example to calculate the price of a security with a discount rate of 5% that matures in 90 days.
Step 1: Enter the values into a Google Sheet:
- Settlement Date: 1/1/2025
- Maturity Date: 4/1/2025
- Discount Rate: 0.05
- Redemption Value: 100
Step 2: Go to a blank cell and start typing the following:
=PRICEDISC(
Step 3: Enter the parameters in the given order using the cell references:
=PRICEDISC(B1, B2, B3, B4) and close the parentheses.
Step 4: Press Enter. You get the price of the discounted security based on the input. This tells you how much you’d pay today for a security worth $100 at maturity after the discount is applied.
Using PRICEDISC from the Google Sheets Menu
- Select the cell where you want the result to appear.
- Click on the Insert menu.
- Go to Function -> Financial.
- Choose PRICEDISC. Enter the settlement date, maturity date, discount rate, and redemption value.
- Press Enter to get the price per $100 face value of the discounted security.
Examples
We use PRICEDISC in financial analysis when we want to borrow money by selling bonds instead of stocks. After knowing the redemption value, interest rate, and the start and end dates, we can calculate the bond’s price using PRICEDISC. Let us look at some examples showcasing the same.
Example #1 – Calculate the price of a US Treasury bill
Consider an investor who is planning to buy a short-term US Treasury bill that has a face value of $100. The bill matures in 6 months, and the annual discount rate is 4%. The investor must find out how much they would pay today for this T-bill.
Step 1: Let us enter the required data in the sheet as shown below.
Step 2: Click on a blank cell and enter the PRICEDISC formula:
=PRICEDISC(B1, B2, B3, B4)
Step 3: Press Enter. The result is approximately $99, indicating the price the investor will pay today for a $100 T-bill maturing in 3 months at a 5% annual discount rate.
This helps investors understand how much they’re paying up and what return they can expect upon maturity.
Example #2 – Calculate the price of commercial paper
In this example, a company is issuing commercial paper. It is a short-term unsecured debt instrument used to raise funds. The commercial paper has a face value of $1,000 and matures in 180 days. It is available at an annual discount rate of 6%. A potential investor wants to find out how much they would pay today to purchase this commercial paper.
Step 1: Enter all the details in a sheet as shown below. Click on a blank cell and enter the PRICEDISC formula:
=PRICEDISC(B1, B2, B3, B4)
Step 2: Press Enter. The output will be approximately $970.00, which is the price the investor needs to pay today to receive $1,000 when the commercial paper matures after 180 days.
This calculation helps investors determine the effective cost of the investment and assess whether the return justifies the discount rate offered.
Example #3 – Calculate the price of a discounted security
A person is considering buying a zero-coupon security — a type of investment that doesn’t pay periodic interest but is sold at a discount and redeemed at full value. The bond has a face value of $5,000 and matures in 90 days. The discount rate being offered is 4% annually. Let us calculate how much they should pay for this security today.
Step 1: Enter the required details in a Google Sheet.
- Settlement Date: 8/1/2025
- Maturity Date: 10/30/2025
- Discount Rate (annual): 0.04
- Redemption (Face) Value: 5000
Step 2: Enter the PRICEDISC Google Sheets formula.
=PRICEDISC(B1, B2, B3, B4)
When you press Enter, you will get the result: 4950.56. This is the price the investor should pay today to receive $5,000 at maturity.
Important Things to Note
- Check whether the settlement and maturity dates are valid and in chronological order, or the function may return an error.
- The discount rate should be entered as a decimal (for example, 5% should be written as 0.05) to ensure accurate calculation.
- The redemption value is typically S100 or S1000 to avoid misleading results. It represents the amount received at maturity.
- Both the settlement date and maturity date must be valid date values, and the settlement date should always be before the maturity date.
Frequently Asked Questions (FAQs)
Some of the errors we get when using PRICEDISC include:
#NUM! error
This error occurs when:
• The settlement date is greater than or equal to the maturity date.
• When the rate of discount, redemption, or basis has invalid values. For instance, if we have provided a discount rate less than or equal to zero, a redemption value less than or equal to zero, or a basis that is a number other than 0,1,2,3,4.
#VALUE! error
This error occurs when
• The given settlement or maturity arguments are invalid dates.
• #VALUE! error
• Any of the arguments are non-numeric
One uses it when valuing bonds when we wish to determine the fair price of a bond that is sold at a discount to its face value.
We use it in financial analysis to calculate the present value of a financial instrument that we sell at a discount.
For making investment decisions, we must determine if a discounted security is a good investment based on its price and expected yield.
The PRICE function in Google Sheets calculates the price of a bond that pays regular interest. It considers coupon payments, yield, and redemption value.
It is ideal for bonds with periodic interest like government or corporate bonds.
The PRICEDISC function in Google Sheets calculates the price of a discounted security with no periodic interest. It uses discount rate and time to maturity to find the purchase price.
This function is ideal for calculations related to zero-coupon instruments like Treasury bills or commercial paper.
Download Template
This article must help understand PRICEDISC Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is PRICEDISC Function in Google Sheets. We learn its syntax and how to use it to calculate the price of a discounted security with examples. You can learn more from the following articles. –
PDURATION Function in Google Sheets
Leave a Reply