Message Font: Serif | Sans-Serif

No. of Recommendations: 2

https://boards.fool.com/calculating-portfolio-return-my-spre...

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.

John