Suppose I want to find the annual return on a stock. Thing is I bought two different amounts for different price. Assume I will use CAGR.
Day A, Bought B shares at price C/share
Day I, Bought J shares at price K/share
Today is Day T and the stock is trading at price U
How would I weight this along both the time and shares dimension?
I do this in Excel for my investments. It’s not simple. I’ll describe my setup.
Column A has dates investments were made.
Column B has the number of days between the date on that row and the date above.
Column C has the amount of money invested on the date.
Column D has, in the first row (Row 2, since Row 1 is headers), the investment amount on that first date, and for each subsequent row, the amount there will be at that date given a compounding rate. That is, it’s the size of the row above compounded for the number of days in column B plus the amount in column C for that row.
The last row has the current date (=TODAY()) in Column A and generally no investment amount.
Pick a cell that’s not being used in the above investment amounts and dates. Let’s call that D19 (since that’s what it is right now for me). Once you do that, the formula to use in Column D is, for Cell D3, “=D2*(1+D$19)^B3+C3” - note the $, which means you can fill the rest of Column D with autofill or copy/paste and change the other row numbers automatically but not D19. Then in another cell, suppose D20, you put the formula: “=(1+D19)^365-1”. D19 will be the average implied daily rate of return, and D20 is then the annual return. But I haven’t told you what to put in D19 though, because it’s not that simple.
Now go to the Data tab, in the Data Tools area choose What-If Analysis, then Goal Seek. For “Set Cell”, choose the cell in Column D in the row with today’s date (or whatever day you want to compute the rate of return through), for “To Value” put the current value of the investment (or the value on the day you want to calculate it to), and for “By changing cell” choose the cell D19 (or whatever it actually is for you). Hit OK and Excel will iterate calculations by changing D19 until the value of the end investment, as calculated by compounding the amounts of the investments after each subsequent investment, will appear in the today-dated row of Column D. As D19, the daily rate of compounding, changes, it changes D20, the annual return rate, which is what you’re looking for.
Is there an easier way? If you have an HP12C I think there’s a way to basically do the same thing as above (I’m sure my father knew exactly how to do it) but with a more primitive device with those exact calculations built into the circuitry so long as you input the dates and amounts into the registers. But that’s the calculation you need to do regardless of how you want to get the answer.
If you made the investments on a weekly or other regular schedule, you could forget about doing it by day and instead do it with a different time period as the base compounding period, but since I’m not too regular about when exactly I move my money into my investment account, I just stick to daily compounding.
For the best accuracy if the investment pays dividends, you’ll want to enter another line for the date of the dividend and put the amount of the dividend received as a negative number in Column C.
I figured it out today.
Let’s say I invest an amount p[sub]1[/sub] for time t[sub]1[/sub] then I invest an amount p[sub]2[/sub] for time t[sub]2[/sub] and the value of my investment today is V. I am looking for the value r such that p[sub]1/sub[sup]t1[/sup] + p[sub]2/sub[sup]t2[/sup] = V.
Yes, that’s exactly what you’re looking to do. The question is - how do you do that? As far as I know, you can’t solve for ‘r’ analytically. You have to do it via a numerical method. I described how to do such a calculation in Excel. There may be other tools out there that allow you to directly enter the dates and cash flows, perhaps even functions built into Excel that I don’t know about. But it’s not as simple as writing the equation and solving for one of the variables.
Since r is a percent we can assume it is very small so (1 + r)[sup]t[/sup] is very close to 1 + rt. I wont work through all of the mechanations but let A[sub]i[/sub] = p[sub]i[/sub] * t[sub]i[/sub]. Then r is very close to gain/sum(A[sub]i[/sub])