Friday, 26 October 2018

PRICEMAT Finance Function in Google Sheet

PRICEMAT is a finance function in Google sheet used to calculate the price of a security per $ 100 face value that pays interest at the maturity of the security. 

PRICEMAT Finance Function is normally using in the analysis of the business and its finance. As the ultimate aim of the business is to increase the wealth of investors, corporates are always analysing the impact of borrowing money. 

PRICEMAT finance function helps when corporates wish to borrow money by selling securities as they need to find the minimum amount to charge their investors. Because PRICEMAT Finance function in google sheet can calculate the security price of redemption value per $ 100.


PRICEMAT(settlement,maturity,issue,rate,yield, [day_count_convention])

Compulsory Parameters

Settlement: This parameter indicates the settlement date of the security. This date will incur after the issue date of the security.

Maturity: This is the date of maturity of the security.

Issue: This parameter indicating the date of the issue of security.

Rate: Rate is the rate of interest of the security.

Yield: It is the expected annual yield of the security

Optional Parameter

Day Count Conversion: 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.

Application of PRICEMAT Finance Function in Google sheet

Now we know the formula syntax and parameters of PRICEMAT Finance function. Let us consider a practical example to have a clear idea as to how to use this function in the Google sheet.

Suppose the issue date of a security is 1st January 2018, its settlement date is March 1st 2018, and its maturity date is February 28th 2022. The rate of interest is 4.5%, annual yield is 3%. The Day Count Conversion is based on a default value. ie US(NASD)30/360. 

Now we can calculate the PRICEMAT Finance Function in the Google sheet. First of all, we need to rearrange the data in a Google sheet.

PRICEMAT Finance Function

From the above table, we can see that the data has arranged in google sheet and we applied the formula.

The result will be shown in the table shown below.

PRICEMAT Finance Function

Points to Remember

Settlement Date, Maturity Date and Issue Date cells must be filled by Date Function or any other date parsing function.  It should not be filled as Text. The result may be different as per the Day Count Convention used in the formula. In the above example, we have used the default value of Zero representing US(NASD)30/360.

Errors in the application of PRICEMAT Finance Function

There are chances to occur errors in the application of PRICEMAT Finance Function in Google Sheet.

#NUM! Error

There are many reasons for this error. It may happen if we provide a value less than zero as a value for Rate or Yield. 

If we arrange dates in a chronological order, Issue Date will come first, then Settlement Date and the last one will be the Maturity Date.

If the issue date is greater than the Settlement Date, or Settlement Date is greater than or equal to the Maturity Date, in all these cases, the result will return the error #NUM!

PRICEMAT Finance Function

#VALUE! error 

This type of errors may occur when we provide a non-numerical data for any of the parameters in this google sheet the formula

If we are not provided with the Date values as per Google sheet Date functions like Date or To_Date or any other data parsing functions, it may return an error like #VALUE!

PRICEMAT Finance Function

Click here to download the Google sheet file

Hope now you know how to apply the PRICEMAT Finance Function in Google sheet. Please share it with your friends and social media if you like this article.

Qatar Airways