Introduction

I talk about the CAGR from time to time in my courses, the Compound Annual Growth Rate. This is a finance measure that is used to assess how well an investment has done or is doing over a number of periods or years.

RRI() Function

There is an Excel function to help with the CAGR that is called RRI(). The syntax of the function is RRI(nper, pv, fv) and an example of how to use RRI() is

=RRI(10,70,12154) … 10 years with a present value of 70 and a future value at the end of 10 years of 12,154. The RRI is 0.67.48 or 67.48%. That means the CAGR is 67.48%

CAGR Formula Error

As part of my efforts to provide as much help as possible on the CAGR I did a bit of research and found this formula on the MS web site: FV^1/NPER/PV – 1. It was early in the morning and I couldn’t get it to work: it said -96.34% for the example I have just given. Cut a long story short, that was just wrong and with a bit of thought, I realised the CAGR formula is (FV/PV)^1/NPER – 1: that worked.

Warning: watch out for duff formulas on sites that ought to know better.

Alternative Formula

Wayne Winston is a clever man and a while ago, a few years, I learned from him to use the the correct mathematical approach to the CAGR. Wayne said, make it appropriate and he gave an example of a company enjoying exponential growth: the company in the example I have already used, above. In that case, Wayne plotted the actual results on a graph and used Excel’s trendline function to derive the Exponential regression equation y = 58.553e^(0.5694X) = 58.553EXP(0.5694A5). Wayne’s version of CAGR in that case was 76.72% and not the 67.48% I have already shown.

cagr_example_1

That image comes from the attached spreadsheet which shows the story in full but the message is: know your data before you commit yourself and be very careful when using a formula that is wrong by about 150%! Look at the exponential and the linear graphs … they should help to show you that Wayne is right!

cagr_example_2

Spreadsheet file exp_growth_CAGR_trainer

 

Duncan Williamson

%d bloggers like this: