Learn how to calculate key financial metrics like Beta, Alpha, Sharpe Ratio, Treynor Ratio, and Value at Risk (VaR) for a ten-stock portfolio using Excel. This comprehensive tutorial will guide you step by step, from importing data to performing the calculations and interpreting the results. Whether you're an investor or finance student, this video will help you master portfolio risk management techniques.Ten Stocks Portfolio: Beta, Alpha, Sharpe Ratio, Treynor Ratio & VaR Calculation in Excel"
Description: In this detailed tutorial, we'll walk you through the process of calculating essential financial metrics like Beta, Alpha, Sharpe Ratio, Treynor Ratio, and Value at Risk (VaR) for a ten-stock portfolio using Excel. Designed specifically for viewers in Pakistan, India, and Bangladesh, this video is conducted in Urdu and Hindi to ensure clarity and understanding for our South Asian audience. You'll learn how to import stock data, perform calculations, create graphs, and interpret the results to assess and manage portfolio risk effectively.
Whether you're an investor, a finance student, or someone looking to enhance their Excel skills for financial analysis, this video is the perfect resource. By the end of the tutorial, you'll have a thorough understanding of these key risk management metrics and how to apply them to real-world scenarios.
Tags:
Portfolio Management
Beta Calculation
Alpha Calculation
Sharpe Ratio
Treynor Ratio
Value at Risk
Excel Tutorial
Stock Portfolio Analysis
Investment Risk Management
Finance Tutorial
Urdu Finance Tutorial
Hindi Finance Tutorial
Pakistani Investors
Indian Investors
Bangladeshi Investors
South Asian Finance
Description: In this detailed tutorial, we'll walk you through the process of calculating essential financial metrics like Beta, Alpha, Sharpe Ratio, Treynor Ratio, and Value at Risk (VaR) for a ten-stock portfolio using Excel. Designed specifically for viewers in Pakistan, India, and Bangladesh, this video is conducted in Urdu and Hindi to ensure clarity and understanding for our South Asian audience. You'll learn how to import stock data, perform calculations, create graphs, and interpret the results to assess and manage portfolio risk effectively.
Whether you're an investor, a finance student, or someone looking to enhance their Excel skills for financial analysis, this video is the perfect resource. By the end of the tutorial, you'll have a thorough understanding of these key risk management metrics and how to apply them to real-world scenarios.
Tags:
Portfolio Management
Beta Calculation
Alpha Calculation
Sharpe Ratio
Treynor Ratio
Value at Risk
Excel Tutorial
Stock Portfolio Analysis
Investment Risk Management
Finance Tutorial
Urdu Finance Tutorial
Hindi Finance Tutorial
Pakistani Investors
Indian Investors
Bangladeshi Investors
South Asian Finance
Category
📚
LearningTranscript
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