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

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

https://www.fool.com/about/how-to-calculate-investment-retur....

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
Print the post Back To Top
No. of Recommendations: 3
Excel makes this very easy:

use either the function IRR or XIRR to calculate Internal Rate of Return.

XIRR has the flexibility of using irregular/incongruous time periods:

https://corporatefinanceinstitute.com/resources/knowledge/mo...


Alan
Print the post Back To Top
No. of Recommendations: 0
over time I suppose the likelihood of a typo increases

You could run a poor man's sensitivity test by injecting typos of different types (say, one vs two decimal places) and ages (differing Di)--one at a time--and observe their effect on the calculated A and CAGR.

I'd expect some typos to be bigger deals than others.

Eric Hines
Print the post Back To Top
No. of Recommendations: 0
use either the function IRR or XIRR to calculate Internal Rate of Return.

I was aware of IIR, which would not work for me, as the time periods are not regular. However, I was not aware of XIRR, which it seems would work. Thanks.

John
Print the post Back To Top
No. of Recommendations: 3
Depending upon the intention in calculating the return, the FAGR (Fitted Average Growth Rate) may be worth calculating/reviewing also. Here's an article on the FAGR & how to calculate it in Excel ...
http://www.exceluser.com/formulas/how-to-calculate-both-type...

One example where it could be worthwhile to review the FAGR is in MI strategy comparisons. As the FAGR considers all the intermediate points in the calculation along with the start & endpoints it arguably makes for a great comparison between two or more potential choices. In short FAGR not only includes the eventual return, but the journey along the way.
Print the post Back To Top
No. of Recommendations: 0
Like I said in that terminated Saul thread, that was learning to me, the "time-weighted return" method. But, it makes logical sense, and it's what the funds use.

There is a flaw in that method that must be adjusted for. If you have an end of a holding period with a very small amount (>0), and in the next holding period you move a very large amount in, even though you are netting out the cash flow there can be a very high HPR (in my case, 4.x in one month) which affects the total return product calculation significantly.

Comparison - for my US Large Cap asset class, over the 9 years since I started tracking it,
TWR method = 13.68% CAGR
XIRR = 15.8%

(this is the effect of my timing and asset allocation momentum decisions. Vs 10.8% for the Wilshire 5000 cap weighted index for that class).

I suspect the difference comes partly from using monthly periodic values even if they're zero, in XIRR, which theoretically one is supposed to use IRR in that situation.

I've never heard of Newton's method, but then my math skills are basic ;-)

You have all the data to do TWR, XIRR or IRR without relying on a macro.
Print the post Back To Top