MS Excel:Â Calculating, Comparing, and Visualizing Simple Returns and Log Returns
DescriptionHey there, welcome to my MS Excel tutorial! In today's video, we're diving into the world of investment simulations using Microsoft Excel. I'll walk you through the process of comparing simple returns with log returns, helping you gain a better understanding of these concepts.
First off, log returns might sound a bit complex, but they're essentially the logarithmic difference between the price of an asset at two different times. It's a powerful tool in the world of finance.
Let's get started, shall we?
We'll kick things off by opening a blank Microsoft Excel workbook. In the first column, I've named it "day," representing the number of days you've invested in a security. The next column will house the investment values for each day. For instance, on day one, we'll start with an investment of $100.
Now, let's say we encounter a 10% loss on day one. That translates to a negative return on our $100 investment. As we move forward, you'll notice that our returns start improving by 0.1% each day.
To keep things organized, I've formatted the return column as a percentage with two decimal places. As we progress, we calculate the investment value for each day by factoring in the returns. We've also plotted these returns on a line chart for better visualization.
Speaking of which, we've created a line chart using the simple return values. These are the returns we've calculated so far. Now, let's talk about the log return. In Excel, we use the natural logarithm function (LN) to calculate log returns. We add one to the return value and format this column as a percentage too.
With the log return values ready, we add them to our line chart, comparing them with the simple return values. It's interesting to note that as we approach a 0% return, both log return and simple return align closely. However, as returns increase, so does the disparity between the two.
This brings us to a useful insight: for returns under 10%, it's recommended to use log returns. We've highlighted these return columns in red for emphasis.
Now, let's explore accumulated returns. We create a new column, applying a formula to calculate these returns based on simple returns. Additionally, we do the same for accumulated returns using log returns. We format these columns and visualize them with distinct colors for clarity.
As we move forward, you'll notice that the accumulated returns using both methods are remarkably similar. In fact, the accumulated return on the last day remains the same, whether calculated using simple returns or log returns. This holds true when comparing the initial and final investment values.
And that's a wrap for today's tutorial! I hope you enjoyed delving into investment simulations and comparing simple and log returns using MS Excel. If you found this tutorial helpful, don't forget to give it a thumbs up and subscribe for more informative videos. Thanks for watching, and until next time!
Official Page: https://virtual-school.org/tutorial?v=6vh6PgWj62c
Excel File: https://docs.google.com/spreadsheets/d/1hvfa6KRCJOdP2pStUX_mzn64ELs2MNvF/edit?usp=sharing&ouid=110287700425339553017&rtpof=true&sd=true
#ExcelTutorial #InvestmentSimulation #LogReturns #SimpleReturns #FinancialAnalysis #MicrosoftExcel #DataVisualization #FinanceBasics #ExcelFormulas #TutorialVideo #DataAnalysis #FinancialEducation #ExcelCharts #InvestmentStrategy #FinanceTips #SpreadsheetMagic #Investing101 #ExcelSkills #ExcelCharts
00:36 - Comparing Simple Return and Log Return using Sequential Returns from -10% to +10%
06:10 - Comparing Simple Return and Log Return using Realistic Investment Scenario