How To Use PRICEDISC Function in Google Sheets

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.

We can use PRICEDISC Function in Google Sheets to get the discount price of a security

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.

Using PRICEDISC Function in Google Sheets to find the discount price of a shorter security bond

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

  1. To start using the PRICEDISC function, we must select the cell that will contain our result. In this example, we’ll select cell B7.Selecting the cell to place our PRICEDISC formula
  2. Next, we simply type the equal sign ‘=‘ to begin the function, followed by ‘PRICEDISC(‘. 
  3. 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.
    Typing PRICEDISC Function in Google Sheets into our Formula Bar

  4. 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.
    PRICEDISC Function in Google Sheets returning the discount price of the security

 

 

Frequently Asked Questions (FAQ)

  1. 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 the DATE formula or use data validation to avoid bad input for your date arguments to ensure validity.
    Invalid date causes a #VALUE! error for PRICEDISC Function in Google Sheets
  2. 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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like