A:

The expected return of your portfolio can be calculated using Microsoft Excel if you know the expected return rates of all the investments in the portfolio. Using the total value of your portfolio, the value of each individual investment and its respective return rate, your total expected return can be calculated quite simply. You can also calculate the expected return of a portfolio outside of Excel by using a basic formula.

Calculating Total Expected Return In Excel

First, enter the following data labels into cells A1 through F1: Portfolio Value, Investment Name, Investment Value, Investment Return Rate, Investment Weight and Total Expected Return.

In cell A2, enter the value of your portfolio. In column B, list the names of each investment in your portfolio. In column C, enter the total current value of each of your respective investments. In column D, enter the expected return rates of each investment.

In cell E2, enter the formula =(C2/A2) to render the weight of the first investment. Enter this same formula in subsequent cells to calculate the portfolio weight of each investment, always dividing by the value in cell A2. In cell F2, enter the formula =((D2*E2)+(D3*E3)+...) to render the total expected return.

Example

In the example above, assume the three investments are government-issued bonds that carry annual coupon rates of 3.5%, 4.6% and 7%, respectively.

After labeling all your data in the first row, enter the total portfolio value of $100,000 in cell A2. Enter the names of the three investments in cells B2 through B4. In cells C2 through C4, enter the values $45,000, $30,000 and $25,000. In cells D2 through D4, enter the respective coupon rates referenced above.

In cells E2 through E4, enter the formulas =(C2/A2), =(C3/A2) and =(C4/A2) to render the investment weights of 0.45, 0.3 and 0.25, respectively.

Finally, in cell F2, enter the formula =((D2*E2)+(D3*E3)+(D4*E4)) to find the annual expected return of your portfolio. In this example, the expected return is:

= ((0.45 * 0.035) + (0.3 * 0.046) + (0.25 * 0.07))
= 0.01575 + 0.0138 + 0.0175
= .04705, or 4.7%

RELATED FAQS
  1. What is the formula for calculating return on equity (ROE) in Excel?

    Find out more about return on equity, the formula to calculate it and how to calculate return on equity in Microsoft Excel. Read Answer >>
  2. How do I calculate a price to sales ratio using Excel?

    Learn what the price to sales ratio is, how to calculate the ratio using a formula, and how to calculate the price to sales ... Read Answer >>
  3. How can I calculate degree of operating leverage on Excel?

    Find out more about the degree of operating leverage, what the ratio measures and how to calculate the degree of operating ... Read Answer >>
  4. What is the formula for calculating working capital in Excel?

    Find out more about working capital, what working capital measures and how to calculate a company's working capital using ... Read Answer >>
  5. What is the formula for calculating the price-to-earnings (P/E) ratio in Excel?

    Find out more about the price-to-earnings, or P/E, ratio, the P/E ratio formula and how to calculate the P/E ratio in Microsoft ... Read Answer >>
  6. What is the formula for calculating return on assets (ROA) in Excel?

    Find out more about the return on assets, or ROA, ratio and the formula for calculating a company's return on assets in Microsoft ... Read Answer >>
Related Articles
  1. Trading

    Improve Your Investing With Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  2. Insights

    Simulating Stock Prices Using Excel

    We will use the average of the change in log prices, the volatility, the normal distribution and Excel to formulate the future prices of an asset.
  3. Managing Wealth

    3 Steps to Assess Your Portfolio's Annual Performance

    Learn about three simple steps you can use to evaluate the annual performance of your investment portfolio, and why rate of return isn't enough.
  4. Personal Finance

    How Cell Phones Have Changed Your Budget

    The cost of owning a cell phone wasn't a factor for people less than two decades ago. Today, there seems to be no end to the additional charges that come from owning a smartphone.
  5. Financial Advisor

    The Workings Of Equity Portfolio Management

    Achieve analytical efficiency by applying your evaluation to a key set of stocks.
  6. Investing

    Introduction To Value Investing

    Find out what value investing is and how you can put it to work for your investment portfolio.
  7. Investing

    Calculating Annualized Total Return

    The annualized total return is the average return of an investment each year over a given time period.
RELATED TERMS
  1. Protected Cell Company (PCC)

    A corporate structure in which a single legal entity is comprised ...
  2. Financial Modeling

    The process by which a firm constructs a financial representation ...
  3. Estimated Long-Term Return

    A unit investment trust's estimated return over the life of the ...
  4. Expected Return

    The amount one would anticipate receiving on an investment that ...
  5. Portfolio Return

    The monetary return experienced by a holder of a portfolio. Portfolio ...
  6. Return

    The gain or loss of a security in a particular period. The return ...
Hot Definitions
  1. IRR Rule

    A measure for evaluating whether to proceed with a project or investment. The IRR rule states that if the internal rate of ...
  2. Short Covering

    Short covering is buying back borrowed securities in order to close an open short position.
  3. Covariance

    A measure of the degree to which returns on two risky assets move in tandem. A positive covariance means that asset returns ...
  4. Liquid Asset

    An asset that can be converted into cash quickly and with minimal impact to the price received. Liquid assets are generally ...
  5. Nostro Account

    A bank account held in a foreign country by a domestic bank, denominated in the currency of that country. Nostro accounts ...
  6. Retirement Planning

    Retirement planning is the process of determining retirement income goals and the actions and decisions necessary to achieve ...
Trading Center