Using Excel to calculate the Compound Annual Growth Rate (CAGR) for an investment.
I am using Office 2011 for Mac on a MacBook Pro. I previously used Lotus 123 on a Windows XP machine and calculating the CAGR for an investment was very simple using the @RATE formula to simply input:
1. Future Value.
2. Present Value.
3. and Term.
I thought using the Excel INTRATE Financial Formula to calculate the the CAGR would work fine because the formula asks you to input similar information which I did as follows:
1. Settlement (purchase) Date9/16/1979
2. Maturity Date.12/31/2011
3. $ Amount Invested.$27,431
4. Redemption $ Amount received at maturity.$211,680
5. Basis (annual day count, ie 360 days or 365 days.).365 day basis
When I used the INTRATE Financial Formula to calculate the CAGR on a 32.3 year investment, the answer returned was 20.79% when the correct answer should of been 6.53%. The higher interest rate was returned by Excel, and the lower correct interest rate
was returned by both Lotus 123 and a 15 year old HP12C calculatopr.
Where in Excel can I find the Financial Formula to accurately calculate the CAGR? It has to be there somewhere because calculating CAGR is a very common financial calculation.
Thank you, Simon Carey *** Email address is removed for privacy ***
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.