The PRICEDISC function in Google Sheets is useful when you need to compute the price of a discount security.
Discount securities are non-interest bearing, meaning these securities or bonds are issued at face value.
The rules for using the PRICEDISC
function in Google Sheets are as follows:
- The function requires four arguments that describe the security (settlement date, maturity date, discount rate, and redemption value).
- The function then outputs the calculated price of the security at maturity.
- Optionally, you may indicate the day count convention to use when making the price calculation.
Let’s take a look at a quick example!
You’ve decided to buy a discount security. The security was delivered to you on January 2nd, 2018, and it can be redeemed after five years on January 2nd, 2023. The redemption value of the security is $5000 with a discount rate of 2.5%. How much is the price of the security?
With the PRICEDISC
function, it becomes a straightforward calculation. We can employ this formula to compare the prices of all kinds of securities so you can make better decisions beforehand.
Now that we know how to use the PRICEDISC
function let’s explore how we can use it on an actual sample spreadsheet.
The Anatomy of the PRICEDISC Function
So the syntax (the way we write it) of the PRICEDISC
function is as follows:
=PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention])
Let’s take a look at this formula in order to understand what each of these terms means:
- = the equal sign is how we start any function in Google Sheets.
- PRICEDISC() is our
PRICEDISC
function. It computes the price of a discount security, based on its expected yield. - settlement refers to the settlement date of the given security.
- maturity refers to the end date of the security when it can be redeemed at face or par value.
- discount is just the discount rate of the security when it was purchased.
- redemption refers to the redemption value of the security.
- day_count_convention is an optional argument that indicates what day count method to use. By default, it is set to 0, which indicates the US (NASD) 30/360 convention.
Now that we know how each argument is used, let’s go through an example of the PRICEDISC
function being used in an actual Google Sheets spreadsheet.
A Real Example of Using PRICEDISC Function
In the table below, we have everything we need to compute the price of the discount security. The settlement and maturity fields must be valid dates. We can enforce this by using Google Sheets’ data validation feature.
To get the result in cell B7, we just need to use the following formula:
=PRICEDISC(B1,B2,B3,B4,B5)
You can make your own copy of the spreadsheet above using the link I have attached below.
In the second example below, we have a much shorter duration security, having only one year between the settlement and maturity date. Therefore, the redemption and current price have much less of an increase.
If you’re ready to try out the PRICEDISC
function in Google Sheets, let’s begin writing it ourselves!
How to Use PRICEDISC Function in Google Sheets
- To start using the
PRICEDISC
function, we must select the cell that will contain our result. In this example, we’ll select cell B7.
- Next, we simply type the equal sign ‘=‘ to begin the function, followed by ‘PRICEDISC(‘.
- As seen in the image below, a tooltip box may appear with hints on how to use the
PRICEDISC
function. Clicking on the arrow on the top-right-hand corner of the box will minimize it.
- The next step is to type the arguments needed for this function. In the sample worksheet, all these values are seen in cells B1:B5. Hitting the Enter key on your keyboard will let the function evaluate.
Frequently Asked Questions (FAQ)
- Why does my formula return a #VALUE! error?
If you receive a#VALUE!
error, you can check if either the settlement date or the maturity date is invalid. You may write in your dates using theDATE
formula or use data validation to avoid bad input for your date arguments to ensure validity.
- Why does my formula return a #NUM! error?
It is a requirement that the discount and redemption arguments be greater than 0. Otherwise, your formula will result in a#NUM!
error. You may also receive this error if the settlement date is greater than or equal to the maturity date provided. Also, the day_count_convention argument must also strictly be an integer between 0 to 4, inclusive.
This step-by-step guide shows how easy it is to use the PRICEDISC
function in Google Sheets when working with discounted securities.
Feel confident in using the PRICEDISC
functions in Google Sheets together with the various other Google Sheets formulas available to create powerful worksheets from scratch. In order to stay updated with our latest Google Sheets guides and tutorials, make sure to subscribe to our newsletter.