Mastering Multi-Asset Portfolio Analysis: Standard Deviation & Returns in Excel
Vložit
- čas přidán 30. 07. 2024
- Welcome to the world of multi-asset portfolio analysis! In this comprehensive tutorial, we'll dive deep into the key components of portfolio performance evaluation - standard deviation and returns - using the powerful tool, Microsoft Excel. Whether you're an aspiring financial analyst, a professional portfolio manager, or a passionate individual investor, this video is for you!
What you'll learn in this video:
Calculating individual asset returns and portfolio weighted returns.
Understanding and computing standard deviation for individual assets and the portfolio.
How to use Excel functions and formulas to analyze risk and return.
By the end of this tutorial, you'll be equipped with the knowledge and skills to effectively analyze and manage your investment portfolio, optimize risk, and maximize returns using Microsoft Excel. So, grab your financial data and let's get started with mastering multi-asset portfolio analysis!
Chapters:
0:00 - Introduction to Standard Deviation & Returns in Excel
0:42 - Adjusted Close Prices Description
1:22 - Calculate Log Normal Daily Returns of an Individual Security
2:20 - Calculate Annual Return of an Individual Security
3:32 - Calculate Standard Deviation of an Individual Security
4:50 - Determine the Weight of Each Security
8:39 - Calculate Annual Return of a Multi-Asset Portfolio
10:15 - Create a Covariance Matrix
13:00 - Calculate Standard Deviation of a Multi-Asset Portfolio
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
💾 Download the file created in this video free here: ryanoconnellfinance.com/produ...
*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC
Alternative Titles:
"Mastering Multi-Asset Portfolio Analysis: Standard Deviation & Returns in Excel"
"Excel Magic: Evaluating Portfolio Performance with Standard Deviation & Return"
"Portfolio Management Essentials: Computing Standard Deviation & Return in Excel"
"Excel for Finance: Multi-Asset Portfolio Risk & Return Calculation"
"Unlocking Portfolio Analytics: Standard Deviation & Return Calculation in Excel"
"The Art of Portfolio Evaluation: Standard Deviation & Returns in Excel"
"Portfolio Risk & Return Analysis: A Comprehensive Excel Guide"
"Investment Performance Measurement: Multi-Asset Portfolio Analysis in Excel"
"Excel Power-Tools: Assessing Multi-Asset Portfolio Risk & Return"
"Efficient Portfolio Analysis: Calculating Standard Deviation & Returns with Excel"
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
💾 Download the file created in this video free here: ryanoconnellfinance.com/product/mastering-multi-asset-portfolio-analysis-standard-deviation-returns-in-excel-video-tutorial/
Thank you Ryan!
Hi Ryan. Another instructive and informative video as ever. Now, I' ve learned other way in analyzing a portfolio.
I really appreciate the feedback William! Thank you
Great explanation! Thank you again! Each one of your videos is an IQ boost :) Please keep the awsome job.
Haha I really appreciate it man! And will do 💪
This video was extremely helpful and easy to understand. I was finally able to calculate the standard deviation of the portfolio after searching and watching other videos for 3 hours.
1 question:.
The covariance option in the data analysis toolpack calculates covariance using the population variance formula and not the sample formula. I read elsewhere that we need to multiply the that result by N/(N-1) to get the sample covariance. What are your thoughts on?
Thanks again for the video.
Thank You😎
You're welcome 😊
Top tier stuff ryan!
Thank you Mark!
Dear Ryan, thank you for this clear and helpful video!
I noticed you use a different approach for calculating annual returns in your other video on stock annual returns and std dev.
In this video you use the exponential function, whereas in the other video you use a simple average. I tried both methods on my own dataset and I get different outcomes.
Can you please explain the difference? And when to use the one or the other?
Hope to hear from you!
Should the square of the individual standard deviation of each individual asset equal to the elements on the diagonal of the covariance matrix?
Since I have all my transaction data, how could I calculate the shares I have on any given date? For example I have x amount of shares on February 3, 2022 and purchase more monthly. What formula could I use to streamline this process?
Hi Ryan, thank you for your informative video. I was wondering why would you use exponential formula when calculate annual return? I tried your formula & the results look odd to me (the percentage has >= 2 digits). Thank you!
Hello, my pleasure! The formula should be correct. This formula takes the average of the daily log returns, scales it up to represent an annual period (assuming 252 trading days in a year), then applies the exponential function to reverse the logarithm, and finally subtracts 1 to convert the multiplicative growth factor into an additive rate of return.
It is possible that the stock you were analyzing experienced high growth in the time period you're considering. It is not unusual for a highly performing stock to exceed 10% annual returns
Great video, as always ;) Would love to see a dynamic version, by using live stock data and dynamic arrays. Like how would you calculate adjusted stock prices with =STOCKHISTORY?
Thank you! I never use the =STOCKHISTORY function in Excel's API because it does not support adjusted close prices, only normal close prices which don't account for dividends and stock splits
Hi Ryan. The video looks great! I had a question on the MULT formula. If we look at the portfolio variance formula in detail, Portfolio variance = w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov(₁,2). From the MULT formula, you can see the first part of the portfolio, Portfolio variance = w₁²σ₁² + w₂²σ₂², being calculated, but the second part, 2w₁w₂Cov(₁,2), seems lost, especially the "2" in the formula. Could you clarify why we do not multiply it by 2?
Hello, you are considering only the portfolio variance formula for a 2 asset portfolio. This video includes a lot more assets and if we wrote the formula out like you did it would be like 5 times longer. The Excel covariance matrix simplifies it for us
Great explanation!
What if we have more than 5 securities in portfolio, say 10. How do we go about calculating return in that case?
The methodology shown here should not change no matter how many securities are in the portfolio, you can follow the same steps!
@@RyanOConnellCFA Thanks Ryan
Great video! I'm still need to spend some time with the covariance matrix - but I'll get there. Could you please provide some context on the standard deviation value for the portfolio? I get the basics of std dev - but how do I make a value judgement on a std dev of 13.74% on an annual return of -2.08%? Does this mean that on a return of -2.08% I can expect 1 standard deviation of 13.74% of 2.08%? Sorry I'm butchering the explanation, but I'm trying to understand the qualitative analysis. Thanks again for the video - your channel is terrific.
Hello Dean! Thank you for the feedback.
Standard deviation is a measure of how spread out the numbers are in a dataset. For investments, it is commonly used as a measure of risk or volatility.
In a normal distribution, which is the bell-shaped curve you might be familiar with, approximately:
68% of all data falls within one standard deviation (up or down) from the mean.
95% falls within two standard deviations.
Almost all (99.7%) falls within three standard deviations.
In your case, the annual return is -2.08% and the standard deviation is 13.74%.
This means that in any given year:
About 68% of the time, you can expect the return to be between -15.82% (that is, -2.08% - 13.74%) and 11.66% (-2.08% + 13.74%).
About 95% of the time, it will be between -29.56% (that is, -2.08% - 213.74%) and 25.4% (-2.08% + 213.74%).
@@RyanOConnellCFA Thank you! Makes perfect sense now. Most clear response I've received on this topic. I've learned a lot from your videos. Much appreciated.
@@deanschwartz11 It is my pleasure Dean, glad to be of service!
I am trying to solve this on my own, while entering the standard deviation formula, it is giving me an error MMULT has incompatible matrix sizes. Number of columns in first matrix (20) must equal number of rows in second matrix (1)
That's really strange I have never heard of that so it is hard for me to tell what's wrong. All I can say is, its a convoluted formula so double check to make sure you've got everything type din exactly correct
Hi Ryan, just a small clarification. Why did you consider 2-year data (2021-23) to calculate annual return?
Hello, you can absolutely use a longer period of time to calculate the returns and often increasing the period of time will be more accurate and comprehensive
@@RyanOConnellCFA Thank you so much for the reply. Let me understand this a bit more, so if i wanted to check how a portfolio of stocks fared from 2018, I should multiply the average daily returns for a period of 5 years with 252 or 252*5? Also, like if i had 500K back in 2018 and purchased 5 different stocks (equal weight 100k each and i get 100, 200, 300, 400 and 500 shares of each stock) with no further transactions till date and i still hold them. How can I value such portfolio return scenarios as there might be some corporate actions like stock split increasing my number of shares? Will my number of shares stay same because adj.closing takes the corporate action into account?
Do you know how can I make the same but for Bond and Cash ?
It is hard to say. You would need to get the price history for the bond and calculate the daily returns and then possibly assume returns of 0% everyday for the cash. Then follow a similar methodology as this video
Can you explain by using probability distribution sir
Hello, would you mind clarifying how you are imagining this would work? Are you taking about estimate future returns and standard deviations using a monte carlo method? This video goes over this a bit: czcams.com/video/OFr4JPK12DE/video.html
Can you replicate this example in Python?
This is a good idea for a video that I will look into in the future!
In the meantime, I just put out a video that isnt exactly the same but it includes custom functions to calculate both portfolio return and portfolio standard deviation in Python:
czcams.com/video/9GA2WlYFeBU/video.html
Why to use log normal and not just change in price ??
Log returns provide better mathematical properties, like time additivity, which means we can directly compare returns over different time horizons. They also enable a more accurate representation of compounding effects and provide normality, resulting in a more reliable assessment of a portfolio's performance.
@@RyanOConnellCFA Hi, thank you for your content ! Just a quick question about the log returns : the annual return using log and exp should be the same as calculating the CAGR or annualized return, right ?
@@themarechal83 Hi, you're absolutely correct! The calculation of the compound annual growth rate (CAGR) or the annualized return is conceptually similar to the log return. Log returns allow you to smoothly account for compounding, which provides the same effect that CAGR or annualized returns do. They give you the average rate at which an investment has grown each year over a specified period of time, which can be compared consistently across different time frames.
Hello! Sso, uhm, I'm gonna populate the comments section with my layman's questions: 1) why do we have to use ln function - does it make the result more accurate and why? 2) the purpose of this standard deviation thing is being able to compare riskiness among may portfolios with different combination of the same assets, right?
Hey thanks for the questions, I'm sure many people will have the same ones!
1. Why do we use the ln() or natural logarithm function for calculating daily returns?
Using the natural logarithm (ln) when calculating daily returns on stocks provides a better measure because it allows for continuous compounding, and maintains consistency in results.. This simplifies further analysis and modeling in finance.
Using natural logarithms (ln) simplifies mathematical operations like differentiation and integration because of the unique properties of the exponential function (e^x) and its inverse, the natural logarithm (ln).
In calculus, the derivative of e^x with respect to x is e^x, making it the only function with this property. Likewise, the derivative of ln(x) with respect to x is 1/x. These properties make it easier to manipulate and analyze the functions mathematically, especially when working with complex financial models.
For example, when analyzing stock returns or modeling financial data, the use of natural logarithms can lead to more straightforward calculations when differentiating or integrating functions. Additionally, logarithmic returns (log returns) have a more natural interpretation in the context of compounded growth, as they can be directly summed or averaged over time, unlike simple returns.
In summary, using natural logarithms simplifies mathematical operations in finance by taking advantage of the unique properties of exponential and logarithmic functions, ultimately leading to easier manipulation and analysis of financial data.
2. What is the point of calculating standard deviation on the portfolio?
Calculating the standard deviation for a portfolio of risky assets is crucial for measuring the overall risk or volatility of the portfolio. It provides investors with insights into the potential fluctuations in the portfolio's value over time, which helps in assessing the level of risk they are willing to take, optimizing asset allocation, and making more informed investment decisions. In essence, standard deviation serves as a key risk management tool for investors. Once we understand both the risk and return, we can figure out how well a portfolio returned relative to a benchmark or other portfolios
@@RyanOConnellCFA Thank you! so, am I correct to understand that ln function helps to kind of even out unusual spikes in the data set (daily returns)? I'm trying to understand this concept intuitively)
@@victoricus1 You've given me a video idea! I should definitely make a video explaining this concept in detail. Ln is not used to "even out unusual spikes in the data set". It does nothing to remove outliers. The reason that it is useful is that it takes into the effect of continuous compounding of interest. It also is "additive".
The term "additive" refers to the property of logarithmic returns (log returns) that allows them to be directly summed or averaged over time, unlike simple returns. This property simplifies the analysis of compounded growth in financial data.
Log returns are calculated using natural logarithms (ln), which transform multiplication and division operations on returns into addition and subtraction operations on log returns. This additive property makes it easier to perform various calculations, such as aggregating returns over multiple periods or finding the average return.
@@RyanOConnellCFA oh, the last paragraph makes sense now, thank you! Perhaps you should do courses on intuitive explanation of calculus or something)
@@victoricus1 I've got my hands a bit full working on a course for financial analysis in excel but that is definitely a good one to explore down the road!
What I don't understand is why you're calculating the portfolio st. dev. from the covariances and the weights. Couldn't you just take the st. dev. from the total portfolio daily returns, or would that be incorrect?
Standard deviation for a portfolio of assets is a function of the weighted average of the individual variances PLUS the weighted covariances between all the assets in the portfolio.
In a portfolio with a large number of securities, the formula reduces to the sum of the weighted covariences.
Exactly what @CDDguy said!
In your opinion, what is the best time period we should use for calculating standard deviation to ensure accuracy? What approach do hedge funds typically use in these cases?
Covariance (spy,spy)=variance of spy= SD of spy ^2=(19.6%)^2=0.038416 but in your covariance matrix it showing different value i.e, 0.000152024
Same way it showing wrong variance for different securities
Kindly correct me if my query was wrong😊
Do you believe the excel covariance feature functions incorrectly?