Making An Asset Calculator

I just created a calculator tool for myself to use to track all my future asset allocations.  Therefore, I can determine how much I need to move and where within 5 minutes, such as I did this afternoon before the market closed down 2.15% today.  This calculator is a useful tool, because it simplifies the work that you need to do to maintain your asset allocation.  If you want to create this calculator for your own use but you are unsure exactly what you're doing (either on Microsoft Excel or with investing in general), then follow these simple steps:

The first row is going to establish the following columns: Fund (Name), Current (Balance), Target (Amount), and Change.

The first column is going to list the funds you own (and want to own) in your portfolio.  There may be as few as two funds or as many as 10 funds (keep in mind, sometimes less is more), but list their names down the first column.

In the next column (B), you simply list the balance of each fund.  This cell is going to be where you change information each time you visit the calculator in the future.  Below the last fund, input the following formula into the cell "=SUM(B2:BX)" with X standing for the cell number of your last fund.

In the next column (C), you are going to input a formula to determine what the target amount in each fund should be based on your asset allocation.  If I listed my first fund as the Vanguard Total Stock Index fund in Cell A2, then I would want 25% of my portfolio's balance in this fund.  Therefore, I would input this formula into Cell C2: "=$B$X+1*.25" with X+1 standing for the cell number where your formula is for the "=SUM(B2:BX)" balance.

You may need to read that part a couple times before it makes sense, but here is an example to ensure you have done it correct.  If you have six funds in your portfolio, then Cell B2 through Cell B7 are going to be where your balance in each of the six funds is listed.  Cell B8 is going to be the sum of those cells.  On Cell C2, you would want the formula "=$B$8*.25" listed.

In the formula, the .25 designates 25%.  For each cell in Column C, you want the formula reflecting each respective fund's target percent.  The "=$B$X+1*." portion of the formula will not change.  For example, if Column A were listing Total Stock Market Index Fund, Total Bond Market Index Fund, Total International Stock Market Index Fund, and GNMA Bond Fund, and your asset allocation were 75%, 10%,  5%, and 10%, respectively, then your spreadsheet would have "=$B$6*.75" in C2, "=$B$6*.1" in C3, "=$B$6*.05" in C4, and "=$B$6*.1" in C5.

Finally, the last column (D) will simply determine how much money you need to move.  Column B represents how much money you have in each fund.  Column C represents how much of your current balance you want in each fund.  The formula for the first row is "=C2-B2" inputted to D2.  If the amount in D2 is positive, you want to move money into that fund.  If the amount in D2 is negative, then remove money from that fund.