I am entering all my retirement stock information in Excel. I have five different tables, each in its own worksheet. There's the Contributions table that shows investments into a stock account with every paycheck that I get:
There's a Fees table that displays mandatory payments for a stock:
There's a Dividends table showing dividends:
There is a table that shows inter-fund transfers for when I choose to invest my retirement money in a different fund:
And finally, there is a table that shows the actual stock prices for all the stock funds I've ever had in my 401k:
I want to display a running balance of my entire 401k. In other words, I want to be able to look at any date and know immediately exactly how many shares of each fund I had (adding up the values for that date in the Contributions, the Dividends, the Fees, and the Interfund Transfers tables), and the price-per-share of those funds (from the SharePrices table), so I can get the balance as of that day. How do I do this? Do I need a Pivot Table? Power Pivot?
Also, we can see that the Contributions, Fees, Dividends, and Interfund Transfers tables all have the exact same column structure. Should these all be combined into one table? I only separated them because it just seemed like they were logically separate things.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Date | Fund | Number of Shares | ||
| 2 | 1/5/2011 | VTIVX | 2.414 | ||
| 3 | 1/18/2011 | VTIVX | 3.406 | ||
| 4 | 1/27/2011 | VTIVX | 2.936 | ||
| 5 | 2/14/2011 | VTIVX | 2.843 | ||
| 6 | 2/24/2011 | VTIVX | 3.084 | ||
| 7 | 3/10/2011 | VTIVX | 2.688 | ||
| 8 | 3/23/2011 | VTIVX | 2.785 | ||
| 9 | 4/7/2011 | VTIVX | 2.755 | ||
| 10 | 4/20/2011 | VTIVX | 2.892 | ||
Contributions | |||||
There's a Fees table that displays mandatory payments for a stock:
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Date | Fund | Number of Shares | ||
| 2 | 3/22/2011 | VTIVX | -0.271 | ||
| 3 | 6/3/2011 | VTIVX | -0.266 | ||
| 4 | 9/6/2011 | VTIVX | -0.298 | ||
| 5 | 12/5/2011 | VTIVX | -0.283 | ||
Fees | |||||
There's a Dividends table showing dividends:
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Date | Fund | Number of Shares | ||
| 2 | 12/30/2010 | VTIVX | 22.174 | ||
| 3 | 12/29/2011 | VTIVX | 21.906 | ||
| 4 | 12/28/2012 | VTIVX | 24.134 | ||
| 5 | 12/26/2013 | VTIVX | 22.303 | ||
Dividends | |||||
There is a table that shows inter-fund transfers for when I choose to invest my retirement money in a different fund:
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Date | Fund | Number of Shares | ||
| 2 | 12/17/2010 | VTIVX | 864.529 | ||
| 3 | 6/9/2015 | VTIVX | -1325.053 | ||
| 4 | 6/9/2015 | VIMSX | 728.096 | ||
| 5 | 7/15/2015 | VTIVX | -20.487 | ||
| 6 | 7/15/2015 | VIMSX | 11.269 | ||
InterfundTransfers | |||||
And finally, there is a table that shows the actual stock prices for all the stock funds I've ever had in my 401k:
| Book1 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Date | VGHAX | VIMSX | VMCIX | VTIVX | ||
| 2 | 12/1/2010 | 51.810001 | 19.629999 | 19.709999 | 13.29 | ||
| 3 | 12/2/2010 | 52.189999 | 19.92 | 20 | 13.45 | ||
| 4 | 12/3/2010 | 52.290001 | 20.040001 | 20.120001 | 13.51 | ||
| 5 | 12/6/2010 | 52.009998 | 20.040001 | 20.120001 | 13.5 | ||
| 6 | 12/7/2010 | 51.959999 | 20.030001 | 20.110001 | 13.5 | ||
| 7 | 12/8/2010 | 52.080002 | 20.02 | 20.1 | 13.52 | ||
| 8 | 12/9/2010 | 52.209999 | 20.09 | 20.17 | 13.56 | ||
SharePrices | |||||||
I want to display a running balance of my entire 401k. In other words, I want to be able to look at any date and know immediately exactly how many shares of each fund I had (adding up the values for that date in the Contributions, the Dividends, the Fees, and the Interfund Transfers tables), and the price-per-share of those funds (from the SharePrices table), so I can get the balance as of that day. How do I do this? Do I need a Pivot Table? Power Pivot?
Also, we can see that the Contributions, Fees, Dividends, and Interfund Transfers tables all have the exact same column structure. Should these all be combined into one table? I only separated them because it just seemed like they were logically separate things.
