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) Date 9/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 ***     
 

Question Info


Last updated July 9, 2018 Views 2,216 Applies to:
Answer
Answer
One way:
 
A1:      9/16/1979
A2:     12/31/2011
A3:     27431
A4:     211680
A5:     =RATE((A2-A1)/365,0,A3,-A4)   ====> 6.53%

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.