The price function in Excel is a financial function that calculates the price of a security based on a periodic yield, number of payments per year, and the principal amount. This function is commonly used in finance and investment to determine the value of securities such as bonds, stocks, and mutual funds.
To use the price function in Excel, you need to input the following arguments: settlement date, maturity date, rate, yield, redemption, and frequency.
The settlement date is the date on which the security is purchased. The maturity date is the date on which the security matures, or reaches its end of life. The rate is the interest rate that the security pays out, while the yield is the annual return on investment. The redemption is the amount that will be returned to the investor at maturity, while the frequency is the number of payments per year.
To illustrate how the price function works, let’s consider an example. Suppose you are considering purchasing a bond with a face value of $1,000, a maturity date of January 1, 2023, and an annual interest rate of 5%. You want to determine the price of the bond based on a yield of 3% and 12 payments per year.
To calculate the price of the bond using the price function, you would input the following formula into Excel:
=PRICE(A1, A2, A3, A4, A5, A6)
Where A1 is the settlement date, A2 is the maturity date, A3 is the rate, A4 is the yield, A5 is the redemption, and A6 is the frequency.
In this example, the settlement date is January 1, 2020, the maturity date is January 1, 2023, the rate is 5%, the yield is 3%, the redemption is $1,000, and the frequency is 12. Therefore, the formula would be as follows:
=PRICE(“1/1/2020”, “1/1/2023”, 5%, 3%, 1000, 12)
Excel would then calculate the price of the bond based on these inputs and return a result of $947.94. This means that you would need to pay $947.94 in order to purchase the bond.
It’s important to note that the price function in Excel is based on the assumption that the security pays out its interest payments at the end of each period. Therefore, if the security pays out its interest payments at the beginning of each period, you would need to use the PRICEB function instead.
The PRICEB function is identical to the PRICE function, but it assumes that the interest payments are paid at the beginning of each period. Therefore, if you are using the PRICEB function, you would input the same arguments as the PRICE function, but the result would be slightly different due to the different timing of the interest payments.
In addition to the PRICE and PRICEB functions, there are several other functions that are related to pricing securities in Excel. These include the PRICEDISC function, which calculates the price of a discount bond, and the PRICEMAT function, which calculates the price of a bond with a maturity date that falls on a non-business day.
There are also several factors that can affect the price of a security, including the time to maturity, the risk of default, and the credit rating of the issuer. These factors can all have an impact on the yield of a security, which in turn affects the price.