Price Function also a financial function in Google Sheet. This Function is used to calculate the price of a bond or security which pays interest periodically.
Syntax
=PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention]).
Compulsory Parameters
Settlement: Settlement means the date on which the security or bond is settled. It is a date after the issuance the bond or security is delivered to the security buyer. This function must be expressed in date formate in the sheet.
Maturity: Maturity date is the end date of the bond or security and the amount is paid back to the bondholder at face or par. This parameter must be expressed in date format
Rate: It is the annual interest rate of the bond.
Yield: The expected annual yield of the bond or security.
Redemption : Redemption value.
Frequency: The number of times coupon payments done per year. The frequency may be 1,2, or 4.
Optional Parameters
Day count convention: This integer value indicated the method of the financial day counting
- 0 – indicates that 30 days in the month and 360 days in a year. There are some specific adjustments to the dates entered at the end of the months
- 1- This is another day count convention. This calculates based on the actual number of days between the specified dates, and the number of days in the intervening years.
- 2- indicates that the calculation is based on the actual number of days between specified dates, assuming the 360 days a year.
- 3- indicates that the calculation is based on the actual number of days between specified dates, assuming the 365 days a year.
- 4- indicates European 30/360, similar to the first option (0). This calculates as per 30 day month and a 360-day year, but it adjusts month-end dates as per European financial conventions
Let us go for an example !!!!!
We have some data in below table. Here we are trying to analyse the formula “price” in google sheet as we need to understand the changes in ‘price’ value once the ‘day_cout-convention’ changes
Depends on the day counting method, the output also showing slight differences. Anyway, 30/360, Actual/Actual and European 30/360 are giving the similar output for ‘price’ function
For more details, you can click here
These are the main points in using a ‘Price ‘ function in google sheet. Inviting your comments and feedback.