How to Calculate Yield Using Excel. Yield is defined as the annual rate of return on an investment expressed as a percentage. In other words, it is the annual percentage return on an investment. With Microsoft Excel, yield is expressed as a single formula and using it takes less time to arrive at an answer then when done manually. In this example,...
Yield is defined as the annual rate of return on an investment expressed as a percentage. In other words, it is the annual percentage return on an investment. With Microsoft Excel, yield is expressed as a single formula and using it takes less time to arrive at an answer then when done manually. In this example, we calculate the yield on a bond that was purchased (settled) in 2003 for $90.25 for each $100 in face value. It pays an annual rate of 2.75% and matures in 2009.
Open Microsoft Excel.
Enter the Settlement date of "4/9/2003" in cell A2. Beside it, label the cell "Settlement date."
Label cell B3 "Settlement date (serial number)."
Enter "=N(A2)" in cell A3, then press enter. This cell will hold the settlement date's serial number format. The formula "=N(A2)" is as follows: N is the formula for converting a date to a serial number and A2 is the cell that contains the date that needs to be converted. This is necessary in order for Microsoft Excel to form its calculations. The resulting number will be 37720.
Enter the maturity date of "12/25/2009" in cell A4 and label the field as "Maturity date."
Enter "=N(A4)" in cell A5 and label the field as "Maturity date" also. Again, this is necessary in order to get the serial date for Excel calculations. The resulting number is 40172.
Enter "0.0275" in cell A6. This is the annual coupon rate, which is the annual percentage interest rate paid on the investment. "0.0275" is equivalent to 2.75%. Label the field "Rate."
Enter "90.25" in cell A7, which is the price per $100 of face value. This is the price paid for the investment for every $100 of value of the investment. Label the field as "Price per $100 of face value."
Enter "100" in cell A8, which is the redemption of $100 of face value. The "100" represents the redemption value of the investment. Label the field "Redemption value per $100 of face value."
Enter "4" in cell A9. Four represents the number of payments per year the buyer receives from the investment. The buyer will receive quarterly payments. If the buyer received two payments per year, the number would have been "2". Label as "Coupon payments/year."
Enter "0" in cell A10. "0" represents the day count basis to use. If the "basis" is zero or omitted, Excel assumes 365 days in the year and calculates according to U.S. corporate bonds instead of European bonds.
Enter "=Yield(A3,A5,A6,A7,A8,A9,A10)" then press enter. Label the field "Yield." The result is 0.044377783.
Convert the yield to a percentage. Click on the field, then click on the "%" button in the menu. The result is 4%, which means this investment pays 4% annually on the buyer's investment.
Tips & Warnings
If unfamiliar with investing and the jargon, do not invest without seeking a financial professional.
Check out these related posts