PRICE, PRICEDISC, PRICEMAT - Calculate the value of stock in Excel
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 <0 or rate <0 or frequency <0 -> 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 <0 or rate <0 or frequency <0 -> 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!
You should read it
- Practical exercises on Notebook price list in Excel
- The easiest way to calculate the percentage (%)
- 3 ways to calculate totals in Excel
- Calculate the total value of the filtered list in Excel
- SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel
- Calculation of percentages in Excel
- How to use the SUM function to calculate totals in Excel
- How to use the kernel function (PRODUCT function) in Excel
- How to use DSUM function in Excel
- How to calculate totals in Excel
- How to calculate the number of days in Excel
- How to calculate the average in Excel
Maybe you are interested
AI predicts storms 10 days in advance, with outstanding results
How to calculate the number of days between two time points extremely quickly using Excel
Reasons why you should not use your laptop outdoors on sunny days
Why do people only stay in submarines for a maximum of 90 days, but can stay in space for 6 months or more?
5 Great apps to countdown the days on your phone
One day on the Moon is equal to how many days on Earth?