The following article details the meaning and usage of the PRICE, PRICEDISC and PRICEMAT functions - Calculate the value of stock in excel.
1. The PRICE function
Description: Returns the value per $ 100 of a security that pays periodic returns.
Syntax: = PRICE (settlement, maturity, rate, yld, redemption, frequency, basis) .
Inside:
- settlement : The settlement date of the securities is the date after the issue date of securities to be sold to buyers, which is a required parameter.
- maturity : The maturity or expiry date of a security, which is a mandatory parameter.
- rate : The annual interest rate of the security.
- yld : Annual profit of the security.
- Redemption : Redemption value of the securities is calculated in units of $ 100.
- frequency : Number of interest payments per year, frequency = 1 -> paid 1 time per year, frequency = 2 -> annual interest payment 2 times, frequency = 4 -> annual interest payment 4 times.
- basis : The basis for counting days 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.
Attention:
- 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 the #NUM! Error.
- If yld the function returns the #NUM! Error value
- If the value of frequency is not in the set {1, 2, 4} or the basis value is not in the set {0, 1, 2, 3, 4} the function returns the error value.
- Where settlement> maturity function returns the #NUM! Error value
For example:
Calculate the value of a security (based on $ 100) knowing the stock settlement date is December 3, 2016, the stock expiry date is 2/19/2020, the annual interest rate is 12.6%, the annual profit is 5.9 %, pay interest twice a year, basis for calculating 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: = PRICE (B8, C8, D8, E8, 100, F8,1) .
Press Enter -> value of stock at face value of $ 100 is:
2. PRICEDISC function
Description: The function performs the face value calculation of $ 100 of a discounted stock.
Syntax: = PRICEDISC (settlement, maturity, discount, redemption, basis) .
Inside:
- settlement : The settlement date of the securities is the date after the issue date of securities to be sold to buyers, which is a required parameter.
- maturity : The maturity or expiry date of a security, which is a mandatory parameter.
- discount : Discount rate of the stock.
- Redemption : Redemption value of the security, calculated in units of $ 100.
- basis : The basis for counting days 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.
Attention:
- 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 the #NUM! Error.
- If yld or rate or frequency -> the function returns the #NUM! Error value
- If the value of frequency is not in the set {1, 2, 4} or the basis value is not in the set {0, 1, 2, 3, 4} the function returns the error value.
- Where settlement > maturity function returns the #NUM! Error value
For example:
Calculate the value of securities (based on $ 100) knowing that the securities settlement date is December 3, 2016, the securities expiry date is February 2, 2020, the discount rate is 4.66% of the basis for calculating the date as a number. actual days per month and actual days per year.
In the cell to calculate enter the formula:
Pressing Enter results returns:
3. PRICEMAT function
Description: 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 : The settlement date of the securities is the date after the issue date of securities to be sold to buyers, which is a required parameter.
- maturity : The maturity or expiry date of a security, which is a mandatory parameter.
- issue : Date of issuing securities, is a required parameter.
- rate : The annual interest rate of the security.
- yld : Annual profit of the security.
- basis : The basis for counting days 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.
Attention:
- 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 the #NUM! Error.
- If yld or rate or frequency -> the function returns the #NUM! Error value
- If the value of frequency is not in the set {1, 2, 4} or the basis value is not in the set {0, 1, 2, 3, 4} the function returns the error value.
- Where settlement > maturity function returns the #NUM! Error value
For example:
Calculate the value of a securities that knows the securities settlement date is December 3, 2016, the expiry date is February 2, 2020, the issue date is February 2, 2014, the annual interest rate is 12.8%, interest. The annual profit earned is 6.9% of the 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: = PRICEMAT (B8, C8, D8, E8, F8, G8) .
Pressing Enter results returns:
The above is a detailed guide of meaning and usage of securities value calculation functions.
Good luck!
ncG1vNJzZmismaXArq3KnmWcp51kvbO1wp5kqaqZmLKltdKcZKmqmZiyrq3TZpqapJOquaLAxGaroZ1dq66twcRmpp9lo6m8pLeMoqVmnaiYsq0%3D