Case $3$: Value of an ETF Investment

You want to calculate the return 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\mathrm{.}25$ and you have $100$ shares, then the dividend is $$25\mathrm{.}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\mathrm{.}99$ and the share price on Dec $31,2011$ is also $$107\mathrm{.}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\mathrm{.}00$ and we have $$175\mathrm{.}00,$ we can purchase $3\mathrm{.}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.

The overall flow of the calculations is as follows:

At the beginning of year, purchase as many shares as possible based on current cash.

At the end of year, calculate the dividend paid $($new cash$).$

Calculate the total value of the account $($cash and shares held$).$

The following is an example of what your spreadsheet might look like:

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 B$15.$

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 B$18$ and use a reference to cell B$15.$

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 $2020.$ Your value should be at least a little more than this because you've purchased additional shares over the last $10$ years.

Rate of return.

Calculate $$100$ with $5\%$ interest for $3$ years.

Please solve the excel spread sheet!