Microsoft Excel has several yield rate functions that are useful for calculating the return on investments that pay interest. These typically take the form of bonds. Bonds are priced based on their discounted cash flows. The principal amount or face value is redeemed at maturity and during the term of the bond, interest is paid. Interest is expressed as the coupon rate. The rate that a bond pays is the yield rate. Use the =YIELD function to calculate the yield rate. You will need to know:
Settlement Date: Date when the security is traded and purchased as an investment
Maturity Date: Date when the security matures and pays off to the investor
Rate: Coupon rate
Pr: Price of the security per $ 100 face value
Redemption: The value that you receive when you redeem the security
Frequency: How often the coupon rate is paid; 1=annually, 2=semi-annual, 4=quarterly
Basis: How the number days is counted, such as 365 calendar days or 360 bankers year
Listed below is an example of the yield rate calculation:
This example is located in Tab 3 of your Lesson Workbook.