Excel Lambda functions which easily calculate portfolio expected return and standard deviation
· 08.09.2022 · 06:14:43 ··· ··· Thursday ·· 4 (4)
David Johnk These Excel Lambda functions calculate various portfolio statistics from a list of prices (P) sorted in ascending order (from old to new), and portfolio weights (w)
Please note:
1) Expected values are not guaranteed values, they are based off of historical data
2) Results will differ with frequency (daily, monthly, etc) and number of observations
3) David W. Johnk assumes no responsibility or liability for any errors or omissions. This is provided in a "as is" basis with no guarantees of completeness, accuracy, usefulness or timeliness
see my YouTube video: https://youtu.be/7CIkj9NIS0s to see how to use these
periodicreturns = LAMBDA(P,OFFSET(P,1,0,ROWS(P)-1)/OFFSET(P,0,0,ROWS(P)-1)-1);
expectedrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array, AVERAGE(array))));
stdevrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,stdev.p(array))));
varrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,var.p(array))));
excessrets = LAMBDA(P,periodicreturns(P)-expectedrets(P));
varcovarmatrix = LAMBDA(P,MMULT(TRANSPOSE(excessrets(P)),excessrets(P))/(ROWS(P) - 1));
correlmatrix = LAMBDA(P,varcovarmatrix(P)/mmult(transpose(stdevrets(P)),stdevrets(P)));
portexpectedret = LAMBDA(w,P,sumproduct(w,expectedrets(P)));
portstddev = LAMBDA(w,P,SQRT(MMULT(MMULT(w,varcovarmatrix(P)),TRANSPOSE(w))));
· 01.01.1970 · 01:00:00 ···
0**##
· 01.01.1970 · 01:00:00 ···
# · 01.01.1970 · 01:00:00 ···
* · 01.01.1970 · 01:00:00 ···
* · 01.01.1970 · 01:00:00 ···
********
