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 that 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 into cell A2. Then, 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, respectively. In cells D2 through D4, enter the respective coupon rates referenced above.

Next, 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. How do I calculate how long it takes an investment to double (AKA 'The Rule of 72') ...

    Find out more about the rule of 72, what the rule of 72 measures and how to calculate the rule of 72 for investments using ... Read Answer >>
  2. How do you calculate return on equity (ROE) in Excel?

    Find out more about return on equity, the formula to calculate ROE and how to calculate this measure of a company's profitability ... Read Answer >>
  3. What is the formula for calculating the capital asset pricing model (CAPM) in Excel?

    Find out more about the capital asset pricing model (CAPM) and the formula for calculating it in Microsoft Excel. Read Answer >>
  4. What is the difference between Macaulay duration and modified duration?

    Find out more about the forward price to earnings ratio and how to calculate the forward price to earnings ratios of companies ... 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. How do you calculate present value in Excel?

    Learn what present value and future value are and how to use Microsoft Excel to calculate present value, given the future ... Read Answer >>
Related Articles
  1. Insights

    Simulating stock prices using excel

    Here, we'll use the average of the change in log prices, volatility, normal distribution and Excel to formulate the future prices of an asset. This can amount to a very valuable tool for investors ...
  2. Insights

    Cell Phone Evolution

    Follow the evolution of cell phones from 20 pound bricks to the sleek smart phones of today.
  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

    Schedule Loan Repayments With Excel Formulas

    Learn how to calculate all the particulars of a loan using Excel, and find out how to set up a schedule of repayment for a mortgage or any other loan.
  5. Investing

    The Workings of Equity Portfolio Management

    Portfolio management is a necessity, not an afterthought, in achieving analytical efficiency.
  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

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  8. Investing

    Gauge Portfolio Performance By Measuring Returns

    Calculate returns frequently and accurately to ensure you're meeting your investing goals.
  9. Investing

    Why Quants are Tracking You Through Your Phone

    We asked three quantitative analysts to name the most interesting data sets they've heard of, from cell phones tracking their owners for third parties, to investing on the outcome of court cases. ...
RELATED TERMS
  1. Mean Return

    Mean return, in securities analysis, is the expected value, or ...
  2. Compound Interest

    Compound interest is interest calculated on the initial principal ...
  3. Protected Cell Company (PCC)

    A protected cell company (PCC) is a corporate structure, a single ...
  4. Average Return

    The simple mathematical average of a series of returns generated ...
  5. Portfolio Return

    Portfolio return is the gain or loss achieved by a portfolio. ...
  6. Goal Seeking

    Goal seeking is the process of calculating in reverse to find ...
Trading Center