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. 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. 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 >>
  3. 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 >>
  4. How do I calculate the equity risk premium in Excel?

    Find out how to calculate the equity risk premium for an individual security using Microsoft Excel, including how to estimate ... Read Answer >>
  5. How can I calculate a bond's coupon rate in Excel?

    Find out how to use Microsoft Excel to calculate the coupon rate of a bond using its par value and the amount and frequency ... Read Answer >>
  6. How can you calculate Book Value Of Equity Per Share (BVPS) in Excel?

    Learn about the book value of equity per share, what it measures and how to calculate a company's book value of equity per ... Read Answer >>
Related Articles
  1. Trading

    Improve your investing with Excel

    Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.
  2. Insights

    Cell Phone Evolution

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

    The Workings of Equity Portfolio Management

    Portfolio management is a necessity, not an afterthought, in achieving analytical efficiency.
  6. Investing

    Gauge Portfolio Performance By Measuring Returns

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

    Rebalance Your Portfolio to Stay on Track

    Like a tune-up for a car, this realignment should minimize trouble down the road. Learn how to use rebalance as a strategy to minimize your portfolio risk.
  8. Investing

    Verizon Selling Bonds Backed by Cell Phone Bills

    Verizon became the first cell phone carrier to issue a bond that is backed directly by its outstanding bills to its cell phone customers.
  9. Investing

    Global Blood Sickle Cell Trial Moves Forward (GBT)

    Global Blood Therapeutics secured FDA approval to proceed with a Phase 3 trial for its GBT440 drug to treat sickle cell disease.
  10. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
RELATED TERMS
  1. Financial Modeling

    The process by which a firm constructs a financial representation ...
  2. Expected Return

    Expected return is the amount of profit or loss an investor would ...
  3. Total Return

    Total return is a performance measure that reflects the actual ...
  4. Portfolio Weight

    Portfolio weight is the percentage composition of a particular ...
  5. Average Return

    The simple mathematical average of a series of returns generated ...
  6. Annual Return

    Annual return is the compound average rate of return for a stock, ...
Hot Definitions
  1. Intrinsic Value

    Intrinsic value is the perceived or calculated value of a company, including tangible and intangible factors, and may differ ...
  2. Current Assets

    Current assets is a balance sheet account that represents the value of all assets that can reasonably expected to be converted ...
  3. Volatility

    Volatility measures how much the price of a security, derivative, or index fluctuates.
  4. Money Market

    The money market is a segment of the financial market in which financial instruments with high liquidity and very short maturities ...
  5. Cost of Debt

    Cost of debt is the effective rate that a company pays on its current debt as part of its capital structure.
  6. Depreciation

    Depreciation is an accounting method of allocating the cost of a tangible asset over its useful life and is used to account ...
Trading Center