Search code examples
excelfinance

Calculate compound annual growth rate (CAGR)

I am trying to set up a spreadsheet that calculates CAGR when pv, fv, nper are given. I tried using: 

((fv-pv)^(1/nper))-1

but the rate comes out too high. For instance for pv=100, fv=500, nper=10:

pv      100
fv      500
nper    10
cagr    0.820564203

If I manually calculate the end value, I come up with:

0    100.00
1    182.06
2    331.45
3    603.42
4    1,098.56
5    2,000.00
6    3,641.13
7    6,628.91
8    12,068.35
9    21,971.21
10   40,000.00

What am I doing wrong?

Solution

  • Your formula is wrong. It should be

    ((fv / pv) ^ (1 / nper)) - 1

    This gives:

    0   100.00
1   117.46
2   137.97
3   162.07
4   190.37
5   223.61
6   262.65
7   308.52
8   362.39
9   425.67
10  500.00