Understanding and Calculating Money Market Yield for Bonds in Excel

Money market yield is a measure of the return on an investment in short-term debt securities, such as Treasury bills, commercial paper, or certificates of deposit. These securities are highly liquid and have maturities of less than one year. Money market yield is also known as CD-equivalent yield or bond equivalent yield.

Money market yield is calculated by adjusting the holding period yield, which is the percentage change in the value of the investment over the holding period, to an annualized rate based on a 360-day year. This is done to make the yield comparable to other securities that pay interest on an annual basis, such as bonds or CDs.

Money market yield can be used to compare the returns of different money market instruments, or to evaluate the profitability of investing in the money market. Money market yield is typically lower than the yield on stocks and bonds, but higher than the interest rate on standard savings accounts. This reflects the low risk and low return characteristics of money market securities.

Basic Theory:

Money Market Yield is essentially a short-term yield calculation that takes into account the interest income generated by a bond based on its current market price. The formula for Money Market Yield is:

Procedures to Calculate in Excel:

1. Gather Information:
   • Face Value of the Bond
   • Purchase Price of the Bond
   • Days to Maturity
2. Apply the Formula in Excel:
   • In Excel, set up a table with the necessary headers: “Face Value,” “Purchase Price,” “Days to Maturity,” and “Money Market Yield.”
   • Input the relevant data into the corresponding columns.
   • In the “Money Market Yield” column, apply the MMY formula:
     =((B2-C2)/C2)*(365/D2)
   • Ensure the cell reference (e.g., B2, C2, D2) corresponds to the correct data in your table.

Explanation:

Let’s consider a scenario:

• Face Value of the Bond (FV): $1,000
• Purchase Price (PP): $950
• Days to Maturity (DTM): 90 days

Substitute these values into the MMY formula:

Calculation:

Result:

The Money Market Yield for this bond is approximately or .

Other Approaches:

1. Excel Functions:
   • Instead of manually entering the formula, you can use Excel functions. For example, you can use the RATE function to calculate the Money Market Yield:
     =RATE(D2, , -C2, B2, C2)
2. Data Validation:
   • Use Excel’s data validation feature to ensure that input values meet specific criteria, avoiding errors in the calculation.