**The PRICE, PRICEDISC and PRICEMAT functions in Excel help you calculate the value of a security. Due to the same structure, Taimienphi.vn will write these 3 functions in an article, please follow along.**

**What is stock? **Securities are evidence confirming the lawful rights and interests of the owner over the issuer’s assets or capital holdings. Securities are presented in the form of certificates, book entries or electronic data. Securities include stocks, bonds, investment fund certificates and derivative securities. In essence, securities are a special commodity.

How to use the PRICE, PRICEDISC and PRICEMAT functions in MS Excel?

**PRICE, PRICEDISC, PRICEMAT function in Excel**

**PRICE, PRICEDISC, PRICEMAT function in Excel**

The following article we will guide in detail the meaning and how to use the functions PRICE, PRICEDISC, PRICEMAT – Calculate the value of stock in excel.

*1. The PRICE function*

**Describe: **Returns the value per $ 100 of a security that pays periodic returns.

**Syntax: **

**= PRICE (settlement, maturity, rate, yld, redemption, frequency, basis).**

**Inside:**

– **settlement**: Securities settlement date is the date after the issue date of securities sold to buyers, which is a required parameter.

– **maturity**: The maturity or expiry date of the security, is the required parameter.

– **rate**: Annual interest rate of the security.

– **yld**: Annual profit of the securities.

– **redemption**: Redemption value of securities in units of $ 100.

– **frequency**: Number of interest payments per year, frequency = 1 -> pay 1 time per year, frequency = 2 -> pay interest twice a year, frequency = 4 -> pay interest 4 times per year.

– **basis**: The basis for counting dates has the following values:

+ basis = 0 or skip: A month has 30 days, a year has 360 days based on US standards.

+ basis = 1: Number of days per month = number of actual days per month, number of days per year equals the number of actual days per year.

+ basis = 2: Number of days per month = number of actual days per month, number of days per year is 360 days.

+ basis = 3: Number of days per month = number of actual days per month, number of days per year = 365 days.

+ basis = 4: Number of days per month = 30 days, number of days per year 360 days according to European standards.

**Note:**

– If **settlement**, **maturity**, **frequency **is the decimal function takes the integer value of those parameters.

– In case of invalid date data entry, the function returns an error **#NUM!**

– If **yld ****rate ****frequency ** The function returns the error value **#NUM!**

– If the value of **frequency **not in episodes {1, 2, 4} or values **basis **not in the file {0, 1, 2, 3, 4} function returns the error value.

– Case **settlement **>**maturity **The function returns the error value **#NUM!**

**Illustration**

Suppose you need to calculate the value of a security (based on $ 100) knowing the stock settlement date is December 3, 2018, the stock expiry date is 2/19/2020, the annual interest rate is 12.6%, the profit is 5.9% per annum, interest is payable twice a year, the basis for calculating dates is the actual number of days per month and the number of actual days per year.

In the cell to calculate, enter the formula: =**PRICE (A3, B3, C3, D3,100, E3,1)**

Press **Enter **-> The value of securities at par value of $ 100 is:

*2. PRICEDISC function*

**Describe: **The function performs the face value calculation of $ 100 of a discounted stock.

**Syntax:**

**= PRICEDISC (settlement, maturity, discount, redemption, basis)**

**Inside:**

– **settlement**: Securities settlement date is the date after the issue date of securities sold to buyers, which is a required parameter.

– **maturity**: The maturity or expiry date of the security, is the required parameter.

– **discount**: Discount rate of the security.

– **redemption**: Redemption value of securities, calculated in US $ 100 units.

– **basis**: The basis for counting dates has the following values:

+ basis = 0 or skip: A month has 30 days, a year has 360 days based on US standards.

+ basis = 1: Number of days per month = number of actual days per month, number of days per year equals the number of actual days per year.

+ basis = 2: Number of days per month = number of actual days per month, number of days per year is 360 days.

+ basis = 3: Number of days per month = number of actual days per month, number of days per year = 365 days.

+ basis = 4: Number of days per month = 30 days, number of days per year 360 days according to European standards.

**Note:**

– The function is calculated by the formula:

– If **settlement**, **maturity**, **frequency **is the decimal function takes the integer value of those parameters.

– In case of invalid date data entry, the function returns an error **#NUM!**

– If **yld ****rate ****frequency ** The function returns the error value **#NUM!**

– If the value of **frequency **not in episodes {1, 2, 4} or values **basis **not in the file {0, 1, 2, 3, 4} function returns the error value.

– Case **settlement **>**maturity **The function returns the error value **#NUM!**

**Illustration:**

Suppose you need to calculate the value of a security (based on $ 100) knowing the stock settlement date is 3 December 2018, the stock expiry date is 2/19/2020, the discount rate is 4.66% basis to Calculate days is the actual number of days per month and the number of actual days per year.

In the cell to calculate, enter the formula:

Press **Enter **The result returned is:

*3. PRICEMAT function*

**Describe: **The function calculates the value based on the $ 100 face value of a security that pays interest on the maturity or expiry date of the security.

**Syntax:**

**= PRICEMAT (settlement, maturity, issue, rate, yld, basis)**

**Inside:**

– **settlement**: Securities settlement date is the date after the issue date of securities sold to buyers, which is a required parameter.

– **maturity**: The maturity or expiry date of the security, is the required parameter.

– **issue**: Stock issuing date, required.

– **rate**: Annual interest rate of the security.

– **yld**: Annual profit of the securities.

– **basis**: The basis for counting dates has the following values:

+ basis = 0 or skip: A month has 30 days, a year has 360 days based on US standards.

+ basis = 1: Number of days per month = number of actual days per month, number of days per year equals the number of actual days per year.

+ basis = 2: Number of days per month = number of actual days per month, number of days per year is 360 days.

+ basis = 3: Number of days per month = number of actual days per month, number of days per year = 365 days.

+ basis = 4: Number of days per month = 30 days, number of days per year 360 days according to European standards.

**Note when using the PRICEMAT function:**

– Calculation formula of the function:

– If **settlement**, **maturity**, **frequency **is the decimal function takes the integer value of those parameters.

– In case of invalid date data entry, the function returns an error **#NUM!**

– If **yld ****rate ****frequency ** The function returns the error value **#NUM!**

– If the value of **frequency **not in episodes {1, 2, 4} or values **basis **not in the file {0, 1, 2, 3, 4} function returns the error value.

– Case **settlement **>**maturity **The function returns the error value **#NUM!**

**Illustration:**

Suppose you need to calculate the value of a security knowing that the securities settlement date is December 3, 2018, the stock expiry date is 2/19/2020, the issue date is 2/19/2016, the annual interest rate is 12.8%, the annual profit gained is 6.9% basis for calculating days as the actual number of days per month and the number of actual days per year.

In the cell to calculate, enter the formula:

Press **Enter **The result returned is:

Above is the detailed instructions and usage 3 **PRICE, PRICEDISC, PRICEMAT function in Excel**. Use these 3 functions to help you calculate the value of a stock in an Excel spreadsheet.

In addition, you should not ignore the common Excel functions below:

– Using **Vlookup function**, the lookup function in excel

– Using **Index function** in excel

– Using **and function** in excel

https://thuthuat.taimienphi.vn/ham-price-pricedisc-pricemat-in-excel-44324n.aspx

Good luck!

.