Excel provides us with numerous functions to calculate and manage securities.
Let’s start with the functions that calculate the Yield of a security. These are YIELD, YIELDMAT and YIELDDISC
Practice File:
Transcript
We can see here the details of a security.
We have calculated three types of yield in our example.
It’s the yield for a security that pays periodic interest.
The yield for a security that pays interest only at maturity.
And the yield for a discounted security.
This way we can decide which kind of security is more profitable to us as investors.
In the current scenario the yield seems better if we decide to be paid at maturity.
Let’s see another example. Here the yield is yet to be calculated.
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 cell B10 we type equals with the name of the function. Then the settlement date, the maturity date, the annual coupon rate, the price per 100$ of face value and the redemption value per 100$ of face value. 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.
In B11 we calculate the Yield for a security that pays at maturity, thus we use the YIELDMAT function.
Same as before we select the settlement and maturity date, followed by the issue date, the rate and the price per $100 of face value. We omit the basis again and we have our result
Lastly we will calculate the Yield for a discounted security in cell B12.
We type the name of the function and select, as before, the settlement and maturity dates, the price per $100 of face value and finally the redemption value per $100 of face value.
We press Enter and have the result.
In this scenario, as the previous one the yield is better if the security pays at maturity.