PRICE, PRICEDISC, PRICEMAT function in Excel

PRICE, PRICEDISC, PRICEMAT function in Excel


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

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)

ham price pricedisc pricemat in excel 2

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

ham price pricedisc pricemat in excel 3

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:

ham price pricedisc pricemat in excel 4

– 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.

ham price pricedisc pricemat in excel 5

In the cell to calculate, enter the formula:

ham price pricedisc pricemat in excel 6

Press Enter The result returned is:

ham price pricedisc pricemat in excel 7

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:

ham price pricedisc pricemat in excel 8

– 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.

ham price pricedisc pricemat in excel 9

In the cell to calculate, enter the formula:

ham price pricedisc pricemat in excel 10

Press Enter The result returned is:

ham price pricedisc pricemat in excel 11

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!

.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *