Skip to main content
No. of Recommendations: 2
I was reading a thread on Saul's board about calculating CAGR in spreadsheets.

Saul thought it off-topic so requested an end to the postings. So I thought I'd post here, where people are probably more knowledgeable of such things anyway.

On the Saul thread Jim Mueller post a link to his article

I must admit I was not aware this was how it was done, although it certainly makes sense. My background is in math, so my "math blinders" would probably have led me to find this solution unacceptable, since it is not exact. When I sat down to solve the problem I came up with the following solution.

Suppose deposits (say to an IRA) P1, ..., Pn are made on dates D1, ..., Dn. Today the account is worth A and the date is D. Let Ti be the years since deposit Pi: Ti = (D-Di)/365.24. Now I want to solve the equation

P1(1+r)^t1 + ... + Pn(1+r)^tn = A

for r. I don't believe this can be reasonably solved in a closed form, so I wrote a macro that utilizes Newton's method. So in my spreadsheet I have a column for contribution amounts and a column for contribution dates (which can be any day of the year) and then a column for the end-of-month value of the account (and end-of-month date). I don't make a ton of deposits in any of my accounts, but some are 20 years old and hence have about 20 deposits. When I call my macro I need to send it the all those amounts and dates. Not a big deal but over time I suppose the likelihood of a typo increases. Maybe one day I'll see how different a CAGR value I would get with my method vs the "standard" method.

I'm curious what others do.

Print the post  


What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.