No. of Recommendations: 0
One thing that has always stymied me is trying to see how well my investment is doing if am also making deposits to or withdrawals from that investment, for example a 401k where you will make periodic deposits.

Ideally I would like to calculate the CAGR (Compound Annual Growth Rate) of the investment, but the formula for calculating CAGR assumes that there has been no deposit or withdrawal.

I believe I have a solution to this problem and I would like to share and discuss my idea with other Fools to see if they agree of if I've missed something. Ultimately if my idea has merit I would like to get it out into the public domain.

I have written an Excel spread sheet to do the calculation so the mathematics are not onerous. I believe the result is accurate out to at least 10 decimal places.

I’ll explain my approach and try to keep it brief. If you know the beginning value and the ending value for the investment and the amount of time between you can calculate the CAGR thusly:

Take the end value and divide by the beginning value
Take this quotient and raise it to the power of 1/y
where y is the time interval in years
Take this result and subtract 1 to get the CAGR.

Great, but that doesn’t account for a deposit. However, deposits and withdrawals are made at specific times, so you can calculate the CAGR from the beginning of the investment to the day of the first deposit or withdrawal transaction. And then using the new balance after the transaction as a new starting point you can calculate the CAGR from the time of the first transaction to the time of the second transaction, and so on.

I call these the interval CAGR or iCAGR. But what to do with them. My first approach was to calculate a time weighted average of the iCAGRs to get what I hoped would be the true CAGR.

The time weighted average is close enough to look right at first, particularly with small positive interest rates, but in the end it doesn’t’ work. Consider this scenario. You invest $10 for one year and it becomes $20. The iCAGR is easy, it’s 100%. The next year you loose money and your $20 becomes $10. The iCAGR is –50%. The time weighted average of 100% for one year and –50% for one year is 25% which is a nonsensical answer. We went from $10 to $10, the true CAGR is 0%.

My next approach and the one that seems to work was to apply the iCAGR to a pretend or virtual investment in which no transactions occur. This gives me a new balance in the virtual investment that is only affected by interest. After any number of transactions in the real account I can then calculate the overall CAGR by using the beginning and end values from the virtual account.

This appears to be quite accurate. There is a problem with leap years, though. When Excel subtracts two dates it gives the answer in days. This result has to be converted to years for the CAGR calculation. If you divide by 365, every leap year you introduce a tiny error. But this is in like the 10th or 12th decimal place; and I feel that this is trivial. Still in the interest of full disclosure I would say the calculation is an estimate of the true CAGR, but more than sufficient to reasonably compare the performance of your portfolio or investment advisor, etc. to the S&P 500.

The only information you need to run the spread sheet is the date of the transaction, the value of the investment before the transaction and the transaction amount. Unfortunately most account statements lack this detail. If you do your own on line investing, you should be able to get this information.

Hopefully you’ve stuck with me so far. I am sorry this is such along post, but I really tried to be succinct. I would like your opinions. I am willing to share the spread sheet. Is there a way to upload it to the board?

Print the post Back To Top
No. of Recommendations: 0
Excel XIRR function.
Print the post Back To Top
No. of Recommendations: 0
Excel XIRR function.


was going to be my suggestion ..but planning a long scrolly post as to why

( otoh, not sure it's accurate to 10 digits, which seems to be OP's goal )
Print the post Back To Top
No. of Recommendations: 0
There are a couple of easier and more accurate ways of finding the IRR of an uneven cash flow:

1. Use the Excel XIRR function, or
2. Use the uneven cash flow function on a financial calculator

Assumng you enter your cash flows accurately, both will give you a time and dollar weighted IRR. Excel's XIRR function annualizes the reported return assuming a 365 day year. The financial calculator (I like the TI BA II Plus), gives you the return per the time period you've used in the calculation. So if this is, say, monthly, you'd multiply the IRR times 12 to annualize it.

One other thing that stumps some using one of these methods. When you enter a cash flow, it must be intered as a positive or negative number. By convention, cash flow going away from you (to, say, a savings account or 401(k) plan) are negative. Cash you remove from the investment (say, a 401(k) rollover or hardship withdrawal), by convention, are positive numbers, as is the account value at the end of the period over which you're measuring your return. Dollars that stay in the account (such as reinvested dividends) are not entered as cash flows (plus or minus) as they did not go into or come out of the account.

Print the post Back To Top
No. of Recommendations: 1
You can use geometric rate of return to find cagr for multiple returns. For your sample data of 10 20 10 it will work as follows
GRR = [(20/10)x(10/20)]^1/2-1
GRR = [2 x 0.5]^1/2-1
GRR = [1]^1/2-1
GRR = 1-1
GRR = 0%
Print the post Back To Top