Friday, 19 October 2018

PRICEDISC Finance Function in Google sheet

PRICEDISC Finance Function


PRICEDISC Function is a finance function in Google sheet. This function is using to find the price of a discounted security on the basis of a future return.


PRICEDISC function is normally using in financial modelling and future analysis of the business. In some situations, the corporates are not willing to sell shares to borrow money because of the disadvantages like the obligation of divide payment, its repayment from after-tax profit etc. 


In those situations, they may issue Securities or Bonds. So the PRICEDISC function is used to calculate the price of the security per $ 100 face value of a discounted security which is a non-interest-bearing but interest is implied in the face value of the bond.




Syntax

PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention])


Compulsory Parameters

Settlement: It means the date of the settlement. It is the date after the issue date of the security when it delivered to the buyer.


Maturity: It is the end date or when the security is expires

Discount: It is the discount rate of the security 

Redemption: It is the redemption value  of the security 

Day_Count_Convention: This integer value indicated the method of the financial day counting

  • 0 - indicates that 30 days in the month and 360 days in a year. There are some specific adjustments to the dates entered at the end of the months. This may be the default value -US (NASD) 30/360
  • 1- This is another day count convention. This calculates based on the actual number of days between the specified dates, and the number of days in the intervening years.
  • 2- indicates that the calculation is based on the actual number of days between specified dates, assuming the 360 days a year.
  • 3- indicates that the calculation is based on the actual number of days between specified dates, assuming the 365 days a year. 
  • 4- indicates European 30/360, similar to the first option (0). This calculates as per 30-day month and a 360-day year, but it adjusts month-end dates as per European financial conventions.

How to use  PRICEDISC Function in Google sheet 


Now we know the syntax, the compulsory and optional parameters to calculate the PRICEDISC Function in Google sheet

Let us consider an example to understand the application in google sheet

Suppose the security purchased on 1st October 2018 and the maturity date of the security will be on 30th September 2023. The rate of discount is 3% and the redemption value is $ 100. The day count convention is based on the 30/360, it is the US(NASD)

First, we can arrange the data in a table as shown below

PRICEDISC Finance Function


Once we enter the formula 

=PRICEDISC(C3,C4,C5,C6,C7),

then the table will show the result as given below 



PRICEDISC Finance Function



From this example, based on the conditions, $ 85 is the price of the security.

Day_count_convention is mentioned as zero because the example provided it as US (NASD) 30/360.  This value may change based on the day_count_convention provided in the question.

Dates like settlement and maturity must be provided by using a date parsing function like 'Date', 'To_date' etc.




Common Errors While applying PRICEDISC Finance Function



#VALUE!   Error


The result of PRICEDISC Financial function may give an error message #VALUE!  in case if :

  • The date provided for 'Settlement' or Maturity is invalid or
  • The provided parameter is a non-numeric

Please refer the below Table as an example



PRICEDISC Finance Function
Wrong Date Format                                             Non Numeric Value

#NUM! Error

The result of PRICEDISC Financial function may give an error message #NUM!  in case if :

  • There is any logical error in the dates like Settlement date is greater than the Maturity date or
  • There is a non-numerical value or invalid number for parameters like Rate, Discount, Redemption or Day_Count_Convention.

Please refer the below Table as an example


PRICEDISC Finance Function


Hope you understand PRICEDISC Finance Function and its application in Google sheet . Please comment if you wish to convey any message and share it with social media and your friend.

Qatar Airways