Buzz
What are Securities? Securities are evidence of the legitimate rights and interests of the owner over the assets or capital of the issuing organization. Securities are represented in the form of certificates, ledger entries, or electronic data. Securities include various types such as stocks, bonds, investment fund certificates, derivative securities. In essence, securities are a special type of commodity.
How to use PRICE, PRICEDISC, PRICEMAT functions in MS Excel
Excel Functions: PRICE, PRICEDISC, PRICEMAT
In this article, we will provide a detailed guide on the meanings and usage of the PRICE, PRICEDISC, PRICEMAT functions - Calculating the value of securities in Excel.
1. PRICE Function
Description: Returns the value per $100 of a security that pays periodic interest.
Syntax:
= PRICE(settlement, maturity, rate, yld, redemption, frequency, basis).
Parameters:
- settlement: The settlement date is the date after the issue date when the security is sold to the buyer, a required parameter.
- maturity: The maturity date or expiration date of the security, a required parameter.
- rate: The annual interest rate of the security.
- yld: The annual yield of the security.
- redemption: The redemption value of the security per $100.
- frequency: The number of interest payments per year, where frequency=1 -> once a year, frequency =2 -> twice a year, frequency =4 -> four times a year.
- basis: The day-count basis to use, with the following values:
+ basis =0 or omitted: A month has 30 days, a year has 360 days based on the US standard.
+ basis =1: The number of days in the month = the actual number of days in the month, the number of days in the year is equal to the actual number of days in the year.
+ basis =2: The number of days in the month = the actual number of days in the month, the number of days in the year is 360.
+ basis =3: The number of days in the month = the actual number of days in each month, the number of days in the year = 365.
+ basis =4: The number of days in the month = 30 days, the number of days in the year = 360 days according to the European standard.
Notes:
- If settlement, maturity, frequency are decimal numbers, the function takes the integer value of those parameters.
- In case of entering invalid date data, the function returns an error #NUM!
- If yld < 0 or rate < 0 or frequency < 0 -> the function returns an error value #NUM!
- If the value of frequency is not in the set {1, 2, 4} or the value of basis is not in the set {0, 1, 2, 3, 4} the function returns an error value.
- In the case of settlement >maturity , the function returns an error value #NUM!
Illustrative Example
Suppose you need to calculate the value of a security (based on $100) given the settlement date as 12/3/2018, the maturity date as 19/2/2020, an annual interest rate of 12.6%, an annual yield of 5.9%, two interest payments per year, and the basis for counting days as the actual number of days in each month and the actual number of days in a year.
In the cell where you want the result, enter the formula: =PRICE(A3,B3,C3,D3,100,E3,1)
Press Enter -> the security value at the face value of $100 is:
2. PRICEDISC Function
Description: This function calculates the value of a discounted security at $100 face value.
Syntax:
= PRICEDISC(settlement, maturity, discount, redemption, basis)
Key Parameters:
- settlement: The settlement date is the day following the issuance date, the date when the securities are sold to the buyer, and is a mandatory parameter.
- maturity: The maturity date or expiration date of the securities, and is a mandatory parameter.
- discount: The discount rate of the securities.
- redemption: The redemption value of the securities, calculated per $100 face value.
- basis: The basis used for counting days, with the following values:
+ basis =0 or omitted: A month is considered to have 30 days, and a year has 360 days based on the US standard.
+ basis =1: The number of days in a month is the actual number of days in that month, and the number of days in a year is the actual number of days in that year.
+ basis =2: The number of days in a month is the actual number of days in that month, and the number of days in a year is 360 days.
+ basis =3: The number of days in a month is the actual number of days each month, and the number of days in a year is 365 days.
+ basis =4: The number of days in a month is 30 days, and the number of days in a year is 360 days based on the European standard.
Important Notes:
Calculation Formula:
- If settlement, maturity, frequency are decimal numbers, the function takes the integer value of those parameters.
- In case of entering invalid date data, the function returns an error #NUM!.
- If yld < 0 or rate < 0 or frequency < 0, the function returns an error value #NUM!.
- If the value of frequency is not in the set {1, 2, 4} or the value of basis is not in the set {0, 1, 2, 3, 4}, the function returns an error value.
- In the case of settlement >maturity , the function returns an error value #NUM!.
Illustrative Example:
Suppose you need to calculate the value of a security (based on $100 face value) given the settlement date is 3/12/2018, the maturity date is 2/19/2020, the discount rate is 4.66%, and the basis for counting days is the actual number of days in each month and year.
In the cell where you want to calculate, enter the formula:
Press Enter, and the result returned is:
3. PRICEMAT Function
Description: The function calculates the value based on the $100 face value of a security with interest payment at the maturity or expiration date of the security.
Syntax:
= PRICEMAT(settlement, maturity, issue, rate, yld, basis)
Key Parameters:
- settlement: The date on which the security is settled, following the issuance date when the security is sold to the buyer, is a mandatory parameter.
- maturity: The maturity or expiration date of the security, is a mandatory parameter.
- issue: The issuance date of the security, is a mandatory parameter.
- rate: The annual interest rate of the security.
- yld: The annual yield of the security.
- basis: The basis used for counting days, with the following values:
+ basis =0 or omitted: A month has 30 days, and a year has 360 days based on the US standard.
+ basis =1: The number of days per month equals the actual days in the month, and the number of days per year is the actual days in the year.
+ basis =2: The number of days per month equals the actual days in the month, and the number of days per year is 360.
+ basis =3: The number of days per month equals the actual days in each month, and the number of days per year is 365.
+ basis =4: The number of days per month is 30 days, and the number of days per year is 360 days based on the European standard.
Considerations when using PRICEMAT function:
- Formula of the function:
- If settlement, maturity, frequency are decimal numbers, the function takes the integer value of these parameters.
- In case of entering invalid date data, the function returns an error #NUM!.
- If yld < 0='' or=''>rate < 0='' or=''>frequency < 0='' -=''>, the function returns an error value #NUM!.
- If the value of frequency is not in the set {1, 2, 4} or the value of basis is not in the set {0, 1, 2, 3, 4}, the function returns an error value.
- In the case of settlement >maturity , the function returns an error value #NUM!.
Illustrative Example:
Suppose you need to calculate the value of a security, knowing that the settlement date is 3/12/2018, the maturity date is 2/19/2020, the issue date is 2/19/2016, the annual interest rate is 12.8%, and the annual yield is 6.9%. The basis for counting days is the actual days in each month and the actual days in the year.
In the cell where you want to calculate, enter the formula:
Press Enter and the result returned is:
Here is a comprehensive guide along with the usage of 3 Excel functions: PRICE, PRICEDISC, PRICEMAT. Employing these functions assists you in calculating the value of securities in Excel spreadsheets.
Additionally, do not overlook these commonly used Excel functions:
- How to utilize the VLOOKUP function, the search function in Excel
- How to use the INDEX function in Excel
- How to utilize the AND function in Excel
Wishing you success!
