How to calculate multiple returns of assets

Question

I have tried to set up a R-code to calculate returns of each column. My problem is that the code should consider multiple investments of different time periods in each asset (column).

I have managed to extract the relevant prices of each day in which each investment was owned (rows) as an xts-object. NAs indicate that a given stock was not owned on the given date. Therefore, I have a table as shown below (as a xts object). Also, please note that the weekend is not included in the index column:

 Stock 1 Stock 2 Stock 3 Stock 4 Stock 4
2019-10-18 100 NA NA 750 NA
2019-10-21 105 NA NA 1000 6
2019-10-22 110 NA NA NA 7
2019-10-23 NA NA NA 750 8
2019-10-24 10 NA NA 500 8
2019-10-25 7.5 NA NA NA 8
2019-10-28 5 NA NA 500 8 
2019-10-29 NA NA 200 250 9

To interpret the table an example follows: Stock 1 was owned in the period from 2019-10-18 to 2019-10-22 where it was sold. On 2019-10-23 Stock 1 was not owned, whereas it was bought again on 2019-10-24 and sold again on 2019-10-28, and therefore a NA on 2019-10-29. The numbers in the column of Stock 1 reflect the stock prices for the given days.

The output I search to obtain is a vector summarizing each independent investment - which is not based upon the investment size:

 Stock 1 Stock 2 Stock 3 Stock 4 Stock 5
Cumulative Return -40% NA 0% -50% 50%

Notice that each investment make use of the first and last row of each investment period to calculate the cumulative return. As an example -40% is calculated as: 110/100-1 + 5/10-1.

If multiple investments are made in an asset (column), it will always be of non-overlapping periods as in the example above.

Thanks in advance!

1) whats the rationale behind choosing the date? In the case of your example 18-22 oct (does a NA on the following date signal the end of a investment period?). 2) Why do Stock 2 and 3 have different results in your desired output when the input is identical? — Base_R_Best_R
1) The dates are just weekdays, so missing dates just signal a weekend. A NA on a specific date for a given stock signals that the stock was not owned on that day - for example stock 1 is owned in the period of 2019-10-18 to 2019-10-22 and again bought on 2019-10-24 and sold again on 2019-10-28. 2) Stock 2 and 3 have different results because stock 3 has an "active" value of 200 on the lastest date (last row) (and therefore I included a 0% as the result to signal that the stock is currently owned - whereas a NA signals a stock that has not been owned). I hope this clarifies your question! — SorenA
An alternative explanation of the 0% for stock 3 is just the return of the "active" period where it has only been owned on the specific date, so the calculation is just: 200/200-1 (to make it consistent with the other calculations). — SorenA

Enrico Schumann Enrico Schumann · Accepted Answer · 2019-11-01T12:53:34

The number you want to compute is similar to what is (unfortunately) called time-weighted returns; though for such returns, the single-trade returns are chained together via multiplication, not via summing. In fact, if that would be acceptable, it is quite easy to compute. Start with data:

library("PMwR")
library("zoo")

data <- structure(
    c(100, 105, 110, NA, 10, 7.5, 5, NA, NA, NA,
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
      NA, NA, 200, 750, 1000, NA, 750, 500, NA, 500,
      250, NA, 6, 7, 8, 8, 8, 8, 9),
    .Dim = c(8L, 5L),
    .Dimnames = list(NULL, c("Stock1", "Stock2", "Stock3",
                             "Stock4", "Stock5")),
    index = structure(c(18187, 18190, 18191, 18192, 18193, 
                        18194, 18197, 18198), class = "Date"),
    class = "zoo")

data
##            Stock1 Stock2 Stock3 Stock4 Stock5
## 2019-10-18  100.0     NA     NA    750     NA
## 2019-10-21  105.0     NA     NA   1000      6
## 2019-10-22  110.0     NA     NA     NA      7
## 2019-10-23     NA     NA     NA    750      8
## 2019-10-24   10.0     NA     NA    500      8
## 2019-10-25    7.5     NA     NA     NA      8
## 2019-10-28    5.0     NA     NA    500      8
## 2019-10-29     NA     NA    200    250      9

Note that I use zoo here, but you can always call as.xts (as xts inherits from zoo). From PMwR I use function returns. For time-weighted returns (i.e. the product of single-trade returns), simply transform all NA-returns into zeros.

R <- returns(data, pad = 0)
R <- na.fill(R, 0)
apply(R, 2, function(x) prod(1+x)-1)
##     Stock1     Stock2     Stock3     Stock4     Stock5 
## -0.4500000  0.0000000  0.0000000 -0.5555556  0.5000000

If you really want to sums of the returns, there is slightly more work to do:

sum_returns <- function(x) {
    x <- c(NA, as.vector(x), NA)
    start <- which(is.finite(x[-1]) & is.na    (x[-length(x)]))
    end   <- which(is.na    (x[-1]) & is.finite(x[-length(x)]))
    sum(x[end]/x[start+1]-1)
}
apply(data, 2, sum_returns)
## Stock1 Stock2 Stock3 Stock4 Stock5 
##   -0.4    0.0    0.0   -0.5    0.5

If you need to differentiate between zero return and never invested, add something like

never.invested <- apply(data, 2, function(x) all(is.na(x)))
## Stock1 Stock2 Stock3 Stock4 Stock5 
##  FALSE   TRUE  FALSE  FALSE  FALSE

How to calculate multiple returns of assets

1 Answers