00:00bismillahirrahmanirrahim
00:02Assalamu Alaikum
00:04In today's video
00:06I will tell you how to
00:08calculate portfolio of 10 stars
00:10in action
00:12and I will tell you
00:14how to calculate
00:16monthly return and
00:18monthly standard deviation
00:20then I will tell you
00:22how to
00:24analyze
00:26monthly return and
00:28monthly standard deviation
00:30we will calculate variance
00:32then we will calculate beta
00:34we will calculate
00:36charge ratio
00:38then we will calculate
00:40Treynor ratio
00:42and we will calculate
00:44alpha of stocks
00:46and at the end
00:48we will calculate
00:50VAR at 95%
00:52and VAR at 99%
00:54we will calculate
00:56all these videos
00:58in Urdu
01:00and I will work in excel
01:02and try to explain
01:04each step
01:06so for this work
01:08I have already
01:10downloaded data
01:12in excel
01:14and rest of the calculations
01:16I do on excel sheet
01:18so let's go to excel sheet
01:20I have already downloaded this data
01:22and you can see
01:24this data is from
01:262014 to
01:282023
01:30so this is 10 years data
01:32and total
01:34120 observations of each stock
01:36these are 10 stocks
01:38and
01:40I will compare this with
01:42S&P
01:44so this is our data
01:46which I have already downloaded
01:48so let's do calculations on this
01:50so first of all
01:52we will calculate monthly return
01:54because we have monthly data
01:56so every month
01:58I will put heading
02:00monthly expected return
02:02after this I will select
02:04stock headings
02:06so I selected it with equal to
02:08and now I will drag it
02:10so all stocks will be shown
02:12I will drag it further
02:14so this is S&P
02:16all stocks are shown
02:18now I will highlight it
02:20select all
02:22control B
02:24and underline it
02:26so it will be underlined
02:28now next
02:30we will calculate return
02:32so first of all we will calculate
02:34first stock
02:36so
02:38this is number 1
02:40so this is first step
02:42so in this
02:44I will click here
02:46and after equal to sign
02:48and second day
02:50or second month return
02:52we will divide it by first month return
02:54and minus
02:561
02:58so now I will drag it
03:02so after this
03:06all will be selected
03:08after this
03:10you can check
03:12this is JP
03:14so this is JP
03:16and after this
03:18I will select all
03:20I will come here
03:22and change it in percentage
03:24and after Sharia I will take 2 values
03:26so this is all
03:28now I will select it
03:30and double click
03:32so all stocks
03:34and S&P
03:36monthly return
03:38you can check it
03:40so
03:42after this
03:44I will group it
03:46so it will be easy to understand
03:48so I will
03:50put border
03:52so we have monthly expected return
03:54now after this
03:56we will calculate average
03:58so first we will calculate monthly average
04:00monthly average
04:02return or monthly return
04:04I have written
04:06and after this
04:08I will copy it here
04:10and write standard deviation
04:12this is monthly
04:14standard deviation
04:18DEVIATIO
04:20this is monthly standard deviation
04:22after this
04:24I will
04:26increase it so all values
04:28will be in front of you
04:30I have copied it and bold it
04:32after this
04:34I will write average formula
04:36after writing equal to
04:38we will take all returns
04:40select it
04:42enter it
04:44so we have it
04:46drag it
04:48so we have all returns
04:50now we will calculate standard deviation
04:52equal to
04:54for standard deviation we will select it
04:56and after this
04:58we will select all returns
05:00and drag it
05:02so we have standard deviation
05:04change it in percentage
05:06so it will be equal to
05:08it will be in percentage
05:10and after that take 2 values
05:12so we have it
05:14we have returns
05:16I will bold it
05:18and bring process in box
05:20so we have it
05:22now we will
05:24annualize it
05:26annualize return
05:30annualize
05:34after this
05:36we will write returns
05:40annualize return
05:42and same way
05:44write standard deviation
05:46this is also annualized
05:48so I will copy it
05:50copy all
05:52and with this
05:54I have written
05:56for standard deviation
05:58I will write annualize
06:00so both are in front of annualized return
06:02after this
06:04formula
06:061 plus
06:08we will take value
06:10and power
06:12we will take
06:1412
06:16because it is monthly
06:18so we have 12 and minus 1
06:20so we have it
06:22we have calculated all returns
06:24in percentage
06:26we will take 2 values
06:28same way we will calculate
06:30standard deviation
06:32monthly standard deviation
06:34we will multiply it
06:36square root
06:38and months
06:4012 months
06:42square root
06:44we have it
06:46annual standard deviation
06:48after this
06:50I will convert it in percentage
06:52and take 2 values
06:54so we have it
06:56again I will select it
06:58and from here
07:00border it and
07:02bold it
07:04so this is
07:06first task
07:08after this
07:10monthly return
07:12annualized
07:14and standard deviation
07:16next
07:18we will calculate covariance
07:20for covariance
07:22we will take
07:24covar
07:26select
07:28and
07:30first
07:32we will take
07:34MSDR
07:36and
07:38compare it
07:40with S&P
07:42select all
07:44and
07:46fix it
07:48with F4
07:50now
07:52calculation will be easy
07:54I will drag it
07:56covar
07:58we don't have to
08:00select it
08:02covar
08:04now
08:06next
08:10calculate beta
08:12beta
08:14formula
08:16equal to
08:18covar
08:20covar
08:22covar
08:24select it
08:26and divide it
08:28with
08:30WAR
08:32WAR
08:34and
08:36select all
08:38so this is
08:40bracket
08:42fix it
08:44so
08:46we have it
08:48beta
08:50drag it
08:52and
08:54after
08:56drag
08:58I
09:00will
09:02select
09:04beta
09:06now
09:08next
09:10we will calculate
09:12S&P
09:14S&P is
09:16professor
09:18so
09:20we
09:22have
09:24S&P
09:26S&P
09:28S&P
09:30S&P
09:32S&P
09:34S&P
09:36S&P
09:38S&P
09:40S&P
09:42S&P
09:44S&P
09:46S&P
09:48S&P
09:50S&P
09:52S&P
09:54S&P
09:56S&P
09:58S&P
10:00S&P
10:02S&P
10:04S&P
10:06S&P
10:08S&P
10:10S&P
10:12F4
10:14F4
10:16F4
10:18F4
10:20F4
10:22F4
10:24F4
10:26F4
10:28F4
10:30F4
10:32F4
10:34F4
10:36F4
10:38F4
10:40F4
10:42F4
10:44F4
10:46F4
10:48F4
10:50F4
10:52F4
10:54F4
10:56F4
10:58F4
11:00F4
11:02F4
11:04F4
11:06F4
11:08F4
11:10F4
11:12F4
11:16F4
11:18F4
11:20F4
11:22F4
11:24F4
11:26F4
11:28F4
11:30F4
11:32F4
11:34F4
11:36F4
11:38F4
11:40F4
11:44F4
11:46F4
11:48F4
11:50F4
11:52F4
11:54F4
11:56F4
11:58F4
12:00F4
12:02F4
12:04F4
12:06F4
12:08F4
12:10F4
12:12F4
12:14F4
12:16F4
12:18F4
12:20F4
12:22F4
12:24F4
12:26F4
12:28F4
12:30F4
12:32F4
12:34F4
12:36F4
12:38F4
12:40F4
12:42F4
12:44F4
12:46F4
12:48F4
12:50F4
12:52F4
12:54F4
12:56F4
12:58F4
13:00F4
13:02F4
13:04F4
13:06F4
13:08F4
13:10F4
13:12F4
13:14F4
13:16F4
13:18F4
13:20F4
13:22F4
13:24F4
13:26F4
13:28F4
13:30F4
13:32risk-free rate
13:34here is risk-free rate
13:36I will fix it with 4
13:38then in formula
13:40we will see
13:42we have to minus
13:44beta from this
13:46so we will minus
13:48beta of stock
13:52then we will multiply it
13:54with market return
13:56so this
13:58this is market return
14:00I will also fix
14:02this
14:04because it will
14:06be same with all
14:08so I fixed it
14:10from this we will
14:12minus risk-free rate
14:14I will mistake it again
14:16I will do it again
14:18we will take annual return of market
14:20we will fix it with 4
14:22and we will minus
14:24from this
14:26risk-free rate
14:28and I will fix it
14:30with 4
14:32so it is fixed
14:34now we have alpha
14:36I will reduce its size
14:38and
14:40here
14:42I will make it 2
14:44and this is number
14:46so we have alpha
14:48I will
14:50calculate all of them
14:52by dragging
14:54so alpha of all stocks
14:56is calculated
14:58after alpha
15:00we will calculate VAR
15:02and
15:042 VAR
15:082 VAR
15:10at 95%
15:12and 2nd VAR
15:14I will copy it
15:1699%
15:18after this
15:20I will
15:22explain it to you
15:24what VAR
15:26tells us
15:28we can see it from sheet
15:30VAR has 3 things
15:32you can see
15:3468 to 99.7%
15:36so
15:38if we minus or plus
15:40standard deviation
15:42average return
15:44so 68.27%
15:46return will fall
15:4868%
15:50chance
15:52return
15:54in this value
15:56will lie
15:58any return
16:00similarly
16:02if we multiply by 2
16:04and minus standard deviation
16:0695% will fall
16:08similarly 99%
16:10will fall
16:12if we multiply it 3 times
16:14now
16:16I will calculate
16:18we have
16:20return
16:22plus
16:262 times
16:28multiply by 2
16:30standard deviation
16:3295%
16:34will fall
16:36value
16:38I will
16:40number it
16:42sorry
16:44I have to change it again
16:46so
16:482
16:50ok
16:52we have
16:54VAR at 95%
16:58sorry
17:00we have this much
17:02variation in return
17:04similarly
17:06VAR at 99%
17:08multiply by 3
17:10standard deviation
17:12we have this value
17:14I will check it
17:18ok
17:20check it
17:22any calculation mistake
17:24so I will
17:26change it
17:28in percentage
17:30so we have
17:32VAR
17:34let's track it
17:36so
17:38we have VAR
17:40after this
17:42you can see
17:44our returns
17:46can vary this much
17:48if we
17:50check 99%
17:52so 246
17:54can vary
17:56similarly
17:5857%
18:00so we have
18:02these calculations
18:04now I will make graphs
18:06so first
18:08we will
18:10calculate return ratio
18:12before that I will add headings
18:14so graphs can be clear
18:16so here
18:18I will copy
18:20stickers
18:22here
18:24after this
18:26we will make chart
18:28select place
18:30after this
18:32I will select all
18:34and select
18:36trainer
18:38after that
18:40we will go to insert
18:42and go to recommended charts
18:44click here
18:46and
18:48ok
18:50so we have
18:52trainer ratio of all stocks
18:54so you can see
18:56variations
18:58can be more
19:00so
19:02we have all
19:04trainer ratio
19:06chart is made
19:08after this
19:10we will make chart
19:12for VAR
19:14again we will select
19:16all stocks
19:18after that
19:20we will select
19:2295% and 99%
19:24again go to insert
19:26and select
19:28this chart
19:30after selecting
19:32this chart
19:34I will click ok
19:36so we have chart
19:38bring it with first chart
19:40and
19:42after this
19:44make size equal
19:46I will
19:50bring it
19:52little down
19:54and
19:58I will
20:00change its name
20:02make it VAR
20:04or value at risk
20:06instead of VAR
20:08so value at
20:10risk
20:18so we have both
20:20charts
20:22and you can see
20:24how much variations
20:26of each stock
Comments