The excel headers should be:

Cash

Shares Purchased

Total Shares

Dividend Value

Total Value

5/6 Case 3: Value of an ETF Investment You want to calculate the retum of an exchange traded fund (ETF) over 10 years based on historical data. There is no simple formula for this because the return each year is not consistent. ETF prices and dividends vary each year. Overview An ETF behaves like a stock in the stock market. You can buy shares of an ETF and the shares rise and fall in price. Additionally, ETFs can pay a dividend. A dividend is a distribution of cash. The yearly dividend payments are per share and paid at the end of the year. If the dividend for the year is $0.25 and you have 100 shares, then the dividend is $25.00. The dividend is paid as cash in your account. Note: The dividend field uses five decimal places. However, you can allow Excel to round your calculated dollar values to 2 decimal places. You don't need to do any manual rounding or truncating. The table lists the ETF share price at the beginning of each year. You can assume that this price is for Jan 1. For this case, we'll assume that the price was the same on Dec 31 of the previous year. For example, the share price on Jan 1, 2012 is $107.99 and the share price on Dec 31, 2011 is also $107.99. The number of shares you can purchase each year depends on the amount of cash you have and the price per share. To simplify our calculations, we're going to assume that you can purchase partial shares. So, if a share costs $50.00 and we have $175.00, we can purchase 3.5 shares. Note: Many investment accounts charge a fee for buying and selling ETFs. Assume that your discount broker does not charge a fee for ETF purchases. Calculations To calculate the return of the ETF, you are building a spreadsheet that simulates what you would see in an investment account over time. At the end of each year an investment account statement shows you your holdings in the account and value of those holdings (cash plus value of shares). In addition to the data already listed in the table, your completed table needs to include: The number of shares purchased each year. The total number of shares. The dividend value paid each year. The total value at the end of the year (final column)- In 2011, you put $10,000 in the investment account at the start of the year to purchase ETF shares. You don't add any additional cash of your own to the account. All new cash for purchases comes from dividend payments. Clipboard Font The overall flow of the calculations is as follows: i. At the beginning of year, purchase as many shares as possible based on current cash. 2. At the end of year, calculate the dividend paid' (new cash). 3. Calculate the total value Of the account (cash and shares held). The following is an example of what your spreadsheet might look like: A33 A Share Price 129.15 107.66 131.77 150.46 177.91 200.87 194.62 228.93 264.15 271.55 371.52 Alignment c Dividend 0.65100 0.74975 0.83625 0.94575 1.11300 0.99875 1.17650 1.30725 1.60750 1.47700 1.43175 Number D Cash $ 10,000.00 Sheet3 uonaltlona Format as cell styles Shares Purchased To Year 2012 2013 2014 2015 2018 2019 2020 2021 ShnePrice 17m 15 10/.66 131M 150.46 87 19".62 284.15 271 55 371.52 Dividend cash 06.51M) S 10.non nn 0 83625 0.94575 1.11000 0 89875 1.1'600 1 *0795 1.60750 1.1?700 1.43175 rotal Vatue I 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Year 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 Tntnl nt end nf 2020 Build your table using cell references where appropriate. This allows you copy formulas from one row to the next instead of typing them in on each row. Your table will calculate the total value at the end of 2020. Copy that value from your table and place it in cell B15. Based on the 2020 total value, use the RATE() function to determine the rate of return that you got over the lifetime of the investment. Place your formula in cell B18 and use a reference to cell B15. Checking your work To verify whether your answers are reasonable or not, use known values: Total value. Take the number of shares purchased with the initial $10000 and multiply by the final share price at the end of 2020s Your value should be at least a little more than this because youve purchased additional shares over the last 10 years. Rate of return, o Calculate $100 with interest for 3 years. o Your starting cash investment is $100. o Your final value is $115.762. o Build a rate formula that calculates the rate of return. If your formula returns anything other than 5%, you know it's incorrect. haua thie fnrrnjila 1 ran it tn Reflect in ePortfolio Print Download Open with docReader Total value at end of2020 Rate of return Sheetl Sheet2