DIY Investment Calculator

I adhere to the concept of dollar cost averaging by investing in my portfolio every month. The theory is that if you invest the same amount into a stock each month, then you reduce risk by spreading your investment out over time. You gain the advantage of buying more shares when the stock price lowers and less shares when the stock price rises.

I take this concept one step further by adjusting my target investing percentages based on the stock price relative to the 52 week high and 52 week low. A spreadsheet helps me easily calculate my target percentages and investment amounts each month. One of the best free solutions for a spreadsheet is Google Docs. Not only is Google Docs easy to use, but they also have many useful functions for creating an Investment Calculator.

The first thing to do is to create a new spreadsheet in Google Docs.

Next, enter "Monthly Contribution" into cell A2. Followed by "Max %" in A3 and "Min %" in A4. The Monthly Contribution is pretty self explanatory. This is the amount you want to invest in your portfolio. The Min/Max % values are used to determine the minimum target weight and maximum target weight. These values can be adjusted later, but for now I will use the default which is 50% and 150%. Your spreadsheet should now look like the following:

The next step is to setup each stock in our portfolio and all pertinent cells in the row. In order to do this, we will put labels on top of each column of row 6.

Now we will create our first row of calculations. Let's start with cell A7. Enter the stock symbol for Intel, INTC. In cell B7, we want to get the current price for Intel. Lucky for us, Google has a wonderful function called GoogleFinance which will do that work for us. Type in the following into cell B7 and press Enter:

=GoogleFinance(A7,"price")

You should see the current stock price for Intel appear in cell B7. Next, we want to get the 52 week high and 52 week low. This is just as easy. In cell C7 enter =GoogleFinance(A7,"Low52") and in cell D7 enter =GoogleFinance(A7,"High52").

Now that we have the current stock price and the 52 week high/low, it is time to calculate the weighting based on this information. Enter the following formula into cell E7.

=$B$4 + ($B$3-$B$4)*((D7-B7)/(D7-C7))

It is important to note here what we are doing. We weight the stock based on the current price relative to the 52 week high and 52 week low. You can see that there is a maximum weight of 1.5 or a minimum weight of 0.5. It is your prerogative to increase or decrease these weightings using the Max/Min Percentages found in cells B3 and B4.

The next step is to calculate our Target % for the stock. For this we should enter the following into cell F7:

=E7/SUMIF($E$7:$E$100,">0",$E$7:$E$100)

This formula divides the Weight by the sum of all Weights in column F but only if there is a value in a cell in column F. This is important because it allows us to add new stocks with very little work which will become clear soon.

You will notice the value in the Target % is 1. We can format this value as a percentage very easily in Google Docs. Click on cell F7 and then select Edit->Format->Percent from the toolbar. You should now see the Target % as 100.00%.

Now that we have the Target %, the final step is to calculate the amount allocated to each stock. This value can be calculated using the following formula in cell G7:

=$B$2*F7

We now have the building block for our Investment Calculator. Right now it is not very useful. We did all these calculations to find out we are going to invest 100% of our monthly contribution into Intel. Here is the real payoff after all your hard work. We now want to expand our Investment Calculator to include more stocks. Highlight cells A7 through G7 and A8 through G8 on the spreadsheet. Press CTRL-D on your keyboard (Mac users COMMAND-D). You should now have two identical rows.

This still is not very useful since both rows show Intel. Enter the stock symbol AAPL in cell A8. Presto! The calculator just updated all the cells with calculations for technology giant Apple Inc. Notice the Target % and Amount columns now reflect the contribution you should make to each stock.

At this point you can keep expanding your list of stocks using the copy down function (CTRL-D or COMMAND-D) followed by inserting the proper stock symbol in the first cell. You can easily adjust your Monthly Contribution and Max/Min % to suit your situation. I have published this spreadsheet for everyone to view HERE. Good luck.

This article was written by The Dividend Investing Blog . You may email questions or comments to me at jake@dividendinvestingblog.com.