When considering investing, the return is one of the most crucial factors the investors look at. It is not the most complex concept when we look at the value of a portfolio, however the calculations can become very intricate when we look at large, multi-asset class portfolios with transaction costs, cash flows, dividends and interest payments, and frequent rebalancing.

When looking at investing, we might be interested in looking at the expected returns that are the basis for the ex-ante return. These returns have not been incurred; however, it is useful to evaluate the potential level of risks in the portfolio.

One should also consider other items that have been excluded above to get full precision picture for the returns. These include the exchange rates, commissions, fees, and taxes. We will omit the fees and taxes from the examples below. However, please note that to apply the formulas, one should make sure that the returns are denominated in the same currency.

When considering the returns of the portfolio, we should also consider the weights of each individual instrument. We are going to investigate the example in the attached excel file based on nine US stocks and one German EUR-denominated stock for which we are going to adjust the returns to be in USD. The commissions and fees will be ignored for this calculation.

Step 1: Data

I have downloaded the monthly share prices for the last five years. Yahoo Finance is the source of this historical data, and the adjusted close prices were used. It has already been adjusted for splits, dividend and/or capital gain distributions.

The following stocks were used:

• Apple
• Tesla
• Amazon
• Netflix
• Ford Motor Company
• Lockheed Martin Corporation
• The Walt Disney Company
• The Coca-Cola Company
• Verizon Communications
• Bayerische Motoren Werke AG (BMW)

The stock prices are collated in the ‘Stock Prices’ sheet of the attached Excel document.

Step 2: Calculations

After collecting the stock prices, we need to convert them to the returns by applying the following formula:

R_t = Pt / P_t-1 – 1

You may see the finding in the sheet ‘Returns’ of the attached Excel document.

For this specific example, we also had to adjust the returns of BMW to the USD-denominated returns by applying the following formula:

R _{portfolio currency} = (1 + R _{foreign currency}) / (1 + R _{pc/fc exchange rate}) – 1

Step 3: Portfolio Weights

The calculation of the portfolio returns requires us to know the actual weights of how the financial instruments are distributed inside our portfolio. Table A1:C12 on the ‘Portfolio Weights’ Sheet has been used to create the sample weights for our portfolio.

Results

We would like to look at how the portfolio has performed over the specific time periods. For this example, 1-month, year-to-date, 1-year and 3-year returns are displayed as examples.

For simplicity of calculation, ‘Returns + 1’ displays the additional array of numbers, where you can find a sum of returns and 1. This has been used to populate the table E8:O12 on the last sheet using ‘Product’ function in Excel to calculate the period returns for our portfolio stocks.

As the ultimate step, we need to apply the following formula to ensure that we consider both stock weights and returns when performing the portfolio calculations:

R_p = ∑ (W_i * R_i)

where
Wi is the weight of stock i
Ri is the return of stock I for the specified period in time
∑ denotes a sum of multiple terms, in this case stocks

The table E1:F4 provides the ultimate results.

Please note, none of the information on this blog represents the opinion of my employer and all information does not represent a financial advice.