In order to be able to calculate the price per $100 face value we pay for an investment, Excel provides us with 3 tools.
The functions PRICE, PRICEMAT, PRICEDISC.
Practice File:
Transcript
In the current worksheet we can see the details of a security.
The issue date is the date the security was issued.
The settlement date is the date after the issue date when the security is traded to the buyer.
The maturity date is the date when the security expires.
In the current worksheet we can see we have already calculated these prices in cells B13 to B15.
We have calculated the price for three scenarios. For a security that pays periodic interest, for a security that pays at maturity and for a discounted security.
We see that at the discounted security we pay less money for each 100$ of face value.
This doesn’t necessarily mean it is the most profitable choice. To determine which scenario is the most profitable we have to look at the yield values. In this case it happens that the discounted security is the way to go since it has higher yield.
It is time to see how these functions work.
In this worksheet we have a different investment plan. We have to calculate the price for each of the three different scenarios.
The first one assumes that the security pays periodic interest. So in cell B13 we type the function PRICE followed by the settlement date, in B4, the maturity date, in B5, the annual coupon rate in B6, the yield in B9, the redemption value per 100$ of face value in cell B8 and finally the frequency of payments per year, cell B7.
The last argument, basis, is optional and it helps us choose the type of day count to use. Since we will always use the default 0 value, we can omit it.
Next we will calculate the price for the scenario that assumes that the security pays at maturity. We type in cell B14 the function, followed by the settlement, maturity and issue dates. Then we select the rate and finally the yield which in this scenario we can find in cell B10. We omitted the basis argument as before.
Lastly we will find out what the price per $100 of face value is, if the security is a discounted one.
We type in cell B15 the name of the function. As before we select the settlement and maturity dates, followed by the discount rate and the redemption value per 100$ of face value.
Take note that here we use the discount rate in cell B12 instead of the Yield for the discounted security in cell B11.
This time also, we omitted the basis argument.