How to calculate investment yields - Capital Gain %, Dividend % and Profit %

In this post, I will explain the method used to calculate your investment CapGain %, Dividend % and Profit % in Portfolio Slicer. I will start by first explaining how to calculate Capital Gain and Profit and then I will explain how to calculate the yield (percentage) and how these values are added over the time.

Capital Gain, Dividends and Profit

Capital Gain is calculated using formula:

"Capital Gain" = "Investment Ending Value" - "Investment Starting value" - "Investments during the period"

Example 1: Investment value on Dec 31, 2016 is equal to 1000$ and on Dec 31, 2017 is equal to 1700$, so for year 2017"Capital Gain" = 1700$-1000$ = 700$.

Example 2: Investment value on Dec 31, 2016 is equal to 1000$, in March 2017 you bought additional shares for that investment worth 200$ and on Dec 31, 2017 same investment total value is equal to 1700$, so for the year 2017"Capital Gain" = 1700$-1000$ - 200$ = 500$.

Dividends are calculated by simply adding all dividend payments over the selected period.

Example 3: On June 29, 2017 investment paid 200$ dividends and on Dec 31, 2017 investment paid 150$ dividends, so over the year 2017 investment paid in total 200$+150$=350$ dividends.

Profit is calculated by simply adding Capital Gain and Dividends, that is:

Profit = "Capital Gain" + "Dividends"

All these calculations are simple and "additive" - that is you can simply add values over small period to get value of the larger period. For example, if investment pays 100$ each month in dividends, then over the year this investment will pay 1200$ (100$ x 12) total dividends.

Calculating yield (%) - against what value?

Calculating “Capital Gain %”, “Dividend %” and “Profit %” is more complicated. To calculate these values we would take “Capital Gain”, “Dividend” or “Profit” over the period and divide it by (usually) investment value at the start of the period:

“Capital Gain %” = “Capital Gain” / “Investment Staring Value” * 100%

Let us look again at Example 1 from the previous paragraph:

Example 1: Investment value on Dec 31, 2016 is equal to 1000$ and on Dec 31, 2017 is equal to 1700$, so for year 2017”Capital Gain” = 1700$-1000$ = 700$.

As this is very simple case, to calculate “Capital Gain %” we would simply divide “Capital Gain” by investment value at the start of the period, that is: ⁷⁰⁰⁄₁₀₀₀ = 0.7 = 70%

Now let us look again at Example 2 from the previous paragraph:

Example 2: Investment value on Dec 31, 2016 is equal to 1000$, in March 2017 you bought additional shares for that investment worth 200$ and on Dec 31, 2017 same investment total value is equal to 1700$, so for the year 2017”Capital Gain” = 1700$-1000$ - 200$ = 500$.

If we use previous formula, then “Capital Gain %” is ⁵⁰⁰⁄₁₀₀₀ = 0.5 = 50%. But what about 200$ worth of new investments that were added in March 2017? We have options:

Ignore these 200$ of additional investment. This would be reasonable in cases when additional investments were made very close to the end of the analyzed period, for example, Dec 29th, 2017. But otherwise ignoring additional investments will distort results. If we use this method, then “Capital Gain %” value will be as calculated before - 50%.
Add these 200$ to initial investment value, so formula becomes: “Capital Gain %” = “Capital Gain” / (“Investment Starting Value” + “Investment changes during period”. This would be reasonable calculations in cases when additional investments were made very early in the analyzed period, for example, Jan 15th, 2017. But otherwise adding additional investments will distort results. If we use this method, then “Capital Gain %” value for this example will be: 500/(1000+200)=41.67%
Split period into 2 periods - one before additional investment and another just after additional investment, calculate “Capital Gain %” for these 2 periods separate and then add these results (you cannot simply add % results - you need to link them geometrically and that will be described later in this article). This would be proper way to do “Capital Gain %” calculation, but technically it is very hard to implement.

Keep in mind that sometimes these investment buys/sells can have a significant impact on model calculations. You might have little (or no at all) investment at the start of the period and then make one or multiple large purchases or sales of the investment during the analyzed period. To reduce the impact of such investment changes it is always a good idea to do calculations for a shorter period in times and then add these results using proper formulas.

Portfolio Slicer uses second option to calculate “Capital Gain %”, but to minimize the impact of external data flows (like buying additional or selling existing investments), these calculations are done for each month and then these monthly results are added together (geometrically linked). In Portfolio Slicer we have internal measure [Compare To Value] that we use in calculation of “Yield %” as a divider. Measure [Compare To Value] is calculated by taking investment value at the start of selected period and adding deposits (if investments include cash) or adding investment values during the selected period.

How to add multiple period yields (%)

As per previous chapter, Portfolio Slicer calculates yields (%) monthly and then add them up to Quarter/Year/All time. But you cannot simply add yield (%) values of the periods to get results for larger period. Lets look at example below.

In the first table (cells A1:E5) we have Capital Gain and Capital Gain % calculation for 3 months. Investment value at the start of October is 1000$ and at the end of December is 1700. In column D for each month we calculate Capital Gain that is 100$ in October, 200$ in November and 400$ in December. In Column E we calculate “Capital Gain %”. If we sum “Capital Gain %” over 3 months, total value is 58.951%.

In the second table (cells A7:E8) we have same investment data summarized at the quarter level. Investment starting value is 1000$ and ending value is 1700$. So total Capital Gain is 700$ and “Capital Gain %” is 70% and that is correct value that we would expect for this investment at quarterly level. So why in the first table sum of “Capital Gain %” monthly value was 58.951%? That is because you cannot simply add yield (%) values, you need to geometrically link them!

To add yield (%) values you must use formula:

SumYield=((1 + YieldPeriod1) * (1 + YieldPeriod2) * (1 + YieldPeriod3) * … * (1 + YieldPeriodN)) - 1

In above example in third table (cells A10:F14), we added column F that has calculated value (1+CapGain%) and then in F14 cell we use formula “=PRODUCT(F11:F13)-1” to geometrically link CapGain% values. With such calculations our result in cell F14 is as expected - 70%.

Portfolio Slicer calculated all yields monthly and then links monthly results geometrically.

You can read more about linking results geometrically here and here.

As I said before, Profit = "Capital Gain" + "Dividends". But, the same does not work with yields, that is "Profit %" <> "Capital Gain %" + "Dividends %". To calculate "Profit %", you need to calculate monthly "Profit %" values and then geometrically link them.