00:00bismillah ar-rahman ar-raheem
00:02assalamu alaikum
00:04today i am making a
00:06mostar video
00:08in which i will tell you
00:10how to develop your portfolio
00:12and
00:14i will try to clear
00:16these small topics
00:18so that you can easily
00:20understand them
00:22and complete your task in less time
00:24so in today's video
00:26i am
00:28starting it
00:30it is a 10 star portfolio
00:32in which i will tell you
00:34two things
00:36i have already downloaded the data
00:38you can watch the previous video
00:40in which i will tell you
00:42how to know monthly return and
00:44standard deviation
00:46and then i will tell you
00:48how to change
00:50monthly return
00:52and how to annualize
00:54monthly return and standard deviation
00:56in this video
00:58we will work on excel
01:00so let's go to the data
01:02i have already prepared the data
01:04this is the data
01:06from 2014 to
01:082023
01:10and
01:12this is the
01:14monthly data
01:16and we have 10
01:18stars
01:20you can see
01:22and
01:24S&P
01:26is also 10 years old
01:28and we can compare it
01:30so this is the final data
01:32i have already downloaded it
01:34now we will work on it
01:36so first of all
01:38we will calculate
01:40monthly expected return
01:42so i will put the heading
01:44monthly expected return
01:46so this is the heading
01:48we have monthly expected return
01:50after this
01:52the signs of the stars
01:54we have to bring here
01:56all the stars
01:58so put the sign of equal to
02:00and select the name of the star
02:02i will click on it
02:04and enter
02:06so we have the name of the star
02:08now we will drag it
02:10so we have total 11 stars
02:1210 stars and S&P
02:14so we will call it
02:16Yara column
02:18and this is Yara
02:20and this is S&P
02:22now i will
02:24bold it
02:26underline it
02:28we will go here
02:30and
02:32i will click on this box
02:34so we have underlined
02:36all the stars
02:38now we have to calculate
02:40the daily return
02:42i will write it here
02:44that we will take
02:46today's return
02:52and we will divide it
02:54with yesterday's return
03:02and we will close the bracket
03:04we will put minus 1 on the end
03:06so we have
03:08the daily return
03:10this is for daily
03:12and if we want to
03:14do monthly
03:16so for that
03:18we will use this formula
03:20we will change it little
03:22instead of today
03:24this month
03:28and here
03:30instead of yesterday
03:32we will take last month return
03:40so
03:42we have
03:44monthly data
03:46so we will use
03:48this formula
03:50again i will tell you
03:52that
03:54first month is
03:56January 2014
03:58and this is February
04:00so what we have to do
04:02first month return
04:04means
04:06first month
04:08and
04:10in second month
04:12how much return we are getting
04:14so first date
04:16has no return
04:18it starts from second
04:20so for that i will click here
04:22and put equal to sign
04:24and second month return
04:26means
04:28this is February 2014
04:30i will select it
04:32and divide it with last month
04:34return
04:36i will divide it with
04:38last month return
04:40and put minus 1
04:42so this is our return
04:44for January
04:46i will change it
04:48in percentage
04:50so you can see
04:52in January
04:54price was 12.57
04:56and in February it will be 12.91
04:58so price has increased
05:00so this is our return
05:022.7%
05:04to calculate return of all stocks
05:06in excel
05:08this box
05:10when it becomes plus sign
05:12i will click it
05:14and drag it
05:16so this is
05:18return of all stocks
05:20our first return
05:22after this
05:24we will select it
05:26and double click
05:28so excel will take
05:30all calculations
05:32i wrote this formula
05:34for you
05:36i will write it
05:38little ahead
05:40and now
05:42i will select all
05:44and double click
05:46so excel will take
05:48all calculations
05:50so this is
05:52our portfolio
05:54all
05:56calculated
05:58on monthly basis
06:00after this
06:02we have 10 years data
06:04so we will calculate
06:06average monthly return
06:20expected return
06:24and standard deviation
06:26we will calculate
06:28both
06:30all calculations in excel
06:32are very easy
06:34once again i will
06:36remove all calculations
06:38so you can understand easily
06:40so we will select all
06:42and then we will put a bottle
06:44so we will know
06:46that we have to select these values
06:48for next calculation
06:50you can see all bottle is put
06:52now
06:54average monthly expected return
06:56and standard deviation
06:58for that we will put
07:00average formula
07:02and after that
07:04we will select all returns
07:06so we have
07:08average return
07:10on monthly basis
07:12and after this
07:14we will calculate standard deviation
07:16so i will select
07:18standard deviation
07:20and again
07:22i will select all values
07:24and again i will drag all values
07:26so we have
07:28all values
07:30and all returns
07:32are on monthly basis
07:34now we will
07:36annualize these values
07:40so to annualize
07:42i will write here
07:44annualize
07:48and for that
07:50i will copy
07:52and paste
07:54and board
07:56so to annualize
07:58what we will do
08:001 plus
08:02expected return
08:04we will select
08:06and after selecting
08:08we will close back and take power
08:10we have to take power
08:12if we are doing on weekly basis
08:14so we will take weekly data
08:16means how many weeks are there in a year
08:18so we are
08:20annualizing it
08:22similarly how many months are there in a year
08:24similarly how many
08:26days are there in a year
08:28so all these things are
08:30monthly, weekly and annualized
08:32so in a year
08:34there are 12 months
08:36so 12 months will come and we will
08:38do minus 1
08:40so we have
08:42annualized
08:44i will change
08:46percentage
08:48so we have
08:50annualized
08:52for standard deviation
08:54what we have to do
08:56first we have to select
08:58monthly, weekly or daily
09:00standard deviation
09:02and we have to multiply it
09:04with square root
09:06we will take square root
09:08in a year
09:10because we have monthly data
09:12so how many months are there in a year
09:14so we will multiply it
09:16so we have square root
09:18i will drag it
09:20and then i will change it
09:22in percentage
09:26so we have
09:28square root of all stocks
09:30so you can see
09:32i have calculated
09:34monthly expected return and standard deviation
09:36and then
09:38annualized expected return and standard deviation
09:40so in next video
09:42we will go to
09:44make portfolio
09:46and i will explain
09:48different ratios in next video
09:50take care
09:52Allah Hafiz
Comments