401k
401ks & IRAs
Auto Insurance
Banking
Bankruptcy
Boat Insurance
Bonds
Borrowing Money
Budgeting Money
Budgeting in Retirement
Building a Home
Buying a Home
Capital Gains Tax
Car Loans
Consumer Rights
Credit & Debt Management
Credit Cards
Credit Scores
Day Trading
Dental Insurance
Disability Insurance
ETFs
Estate Planning
Federal Taxes
Financial Planning
Financing a Home
Frugal Living
General Retirement Planning
General Tax Information
Health Insurance
Home Equity
Homeowners Insurance
IRAs
Insuring Valuables
Investing for Beginners
Investment Properties
Life Insurance
Mortgages
Moving & Storage
Mutual Funds
Other Real Estate
Personal Insurance
Personal Investing
Personal Loans
Personal Money Management
Pet Insurance
Property Management
Property Tax
Real Estate Investments
Renters Insurance
Renters Rights
Renting a Home
Researching Investments
Sales Tax
Saving Money
Saving Money on Taxes
Saving for Retirement
Selling a Home
Setting Financial Goals
Social Security
State Taxes
Stock Market
Tax & Dependents
Tax Deductions
Tax Filing
Tax Planning
Tax Preparation
Tax Returns
Tax Software
Travel Insurance
W-2 Forms

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.