X Excel Tutorial_Product Function,xIsx . Last Modified: Just now v Search Jesse Bob Davies JB X File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Comments Share AutoSave On Save ) Undo * @ Redo ~ ab Wrap Text E3 : X fx A B C D E F G H M N 0 P R S T U W X Y Z The following tutorial may be useful when calculating cumulative and annualized investment returns in Excel. Cumulative Returns w N 1) The table on the right shows monthly investment returns beginning Jan 2010 through Dec 2011 (24 months of history) 2) The objective is to use Excel to calouate the cumulative return and average annualized return over the 24 month period. [3) Recall cumulative returns are calculated by multpying one plus each monthly return and then subtracting 1. This is shown in Month/Year Returns Cumul Rtns column @ which adds 1to the each monthly return and then multiplies 1+ current monthly return by 1+ the cumulative prior monthly returns Jan-10 1.48% 1.0148 4) Cell Q29 shows the cumulative returns of the series. But there is an easier way to do this as shown in Cell P29 using the =Product function in Excel Feb-10 0.92% 1.0241 5) The formula in Cell P29 is = PRODUCT(1+P5:P28)-1. This formula is first adding 1 to the return in each cell from P5 through P28. then the formula Mar-10 -0.78% 1.0161 calouates the product of each (1+Ri) where Ri is each of the monthly returns. Then the formula subtracts 1 from the product. Apr-10 0.18% 1.0180 10 6) Note that cells P29 and Q29 are identical. But the =Product function in Excel makes calculating returns much quicker and easier. May-10 0.80% 1.0261 11 7) The cumulative return of this investment over the 2-year period is 10.27%. A $100 initial investment would have grown to $100' (1+. 1027) = $110.27 Jun-10 -0.15% 1.0246 Jul-10 0.12% 1.0258 Average Annualized Return Aug-10 -0.19% 1.0238 1) Recall the formula to calculate the average annualized returns - you must take 1/ Nth root of (1 + cumulative return). DO NOT TAKE THE CUMULATIVE Sep-10 1.53% 1.0395 RETURN DIVIDED BY THE NUMBER OF YEARS. THAT WOULD BE INCORRECT! Oct-10 -0.47% 1.0346 2) This problem is a little tricky because the data shows monthly returns but we want to calculate the average annualized returns. The trick is get the Nov-10 0.57% 1.0405 exponent correct. Dec-10 -0.48% 1.0355 3) There are 24 months in the data set that contains monthly data where there are 12 months per year. So the Nth root exponent is 24/12 = 2. Jan-11 0.58% 1.0415 So 1/Nth root is 1/2 = 0.5. Another way to look at the Nth root is there are 24 months / 12 months per year. The "months" cancel and the Feb-11 0.27% 1.0443 remaining units is "years" Mar-11 1.43% 1.0593 4) This calculation is shown in cell P31. Apr-11 0.70% 1.0667 May-11 1.52% 1.0829 Combining the Two Jun-11 -0.10% 1.0818 24 1) However, it still took us two calculation steps to calculate the average annualized return. We can do this more efficiently by incorporating the Jul-11 -0.25% 1.0791 1/Nth root exponent in the =Product formula. This is shown in cell P32 Aug-11 0.73% 1.0870 2) Notice that I used the = count function to determine the total number of monthly returns. In this example, we knew that there were 24 data points Sep-11 -0.21% 1.0847 27 So I could just as easily hard coded in "24" for the number of periods. However, often in finance we're analyzing return data much longer than 24 Oct-11 1.32% 1.0990 28 periods. So it's easier to have Excel count the number of periods in a data set using the = count function rather than wasting time doing it ourselves. Nov-11 -0.23% 1.0965 29 3) In fact, you can copy and paste this formula to different montly data sets and then easily adjust the range in the =Product and = Count functions Dec-11 0.57% 1.1027 30 4) Lastly, you can still use the =Product function if your data set contains daily, weekly, or yearly returns. However, you will have to adjust the 1/Nth Total 10.27% 10.27% 31 root exponent accordingly using principles of Time Value of Money. Average Annualized Rtn 5.01% 32 Average Annualized Rin 5.01% 33 I hope you found this tutorial useful. 34 Sheet + Ready Accessibility: Good to go + 69% 57'F Q Search 8:17 PM Mostly sunny X 5/17/2023Question 1 25 pts 1. KYZ is an activer managed mutual fund that invests in US large capitalization stocks and seeks to outperform the SEEP SUD. The annual returns of KY2 Fund and an SELF 500 Index Fund from 1996 through 219 are shown in the Excel file below: [25 ptsl Module 3 Homework Data.xlsx i Use Excel to calculate the following: a} What is the cumulative return 81 average annualized return of KYZ Fund and the S&P 500 over the period? [15 ptsl (Hint: Students mav nd the following tutorial useful when performing these calculations in Excel. The Excel functions covered in the tutorial will come in handv in later assignments 1. Excel Tutorial Product Function.xlsx i, bl Assuming an initial investment of $10G at the beginning of 199'6, what would be the value at the end of 201'? for XYZ Fund and the SEEP 50D? {10 PE} Excel Tutorial Product Function.xIsx b) Assuming an initial investment of $100 at the beginning of 1996, what would be the value at the end of 2019 for XYZ Fund and the S&P 500? (10 pts) XYZ Fund S&P 500 Cumulative Return Annualized Return FV of $100 in 2019 Edit View Insert Format Tools Table 12pt * Paragraph " B J U A By Ty :A C D E F G H I J K B Module 3 Homework: Questions 1 & 2 Year XYZ Fund S&P 500 1996 22.32% 23.00% 1997 32.46% 33.40%% 1998 28.47% 28.60% 1999 21.12% 21.00% 2000 -8.85% -9.10% -11.90%% LO 2001 -14.06% 10 2002 -22.10% -22.10% 11 2003 29.58% 28.70% 12 2004 10.57% 10.90% 13 2005 2.31% 4.90% 14 2006 11.46% 15.80% 15 2007 6.75% 5.50% 16 2008 36.36% -37.00% 17 2009 26.69% 26.50% 18 2010 13.02% 15.10% 19 2011 3.63%% 2.10% 20 2012 16.00% 16.00% 21 2013 31.49% 32.40% 22 2014 10.96% 13.70% 23 2015 3.71% 1.38% 24 2016 12.43% 11.96% 25 2017 22.18% 21.80% 26 2018 -6.90% -4.38% 27 2019 30.17%% 31.49% 28 29 30 31 Sheet1 + Ready Accessibility: Good to go