You invested $10,000 in a portfolio on Jan 1, 2005. Let's say by Jan 1, 2006, your portfolio had grown to $13,000, then $14,000 by 2007, and finally ended up at $19,500 by 2008.What is the CAGR if you liquidate your investment on the first day of the 2008?
Four methods are available for the simple CAGR calculation, Excel IRR, Excel XIRR, Excel TVM and Mathematics. All the calculations is stated inside the photo below.
1) Excel IRR
- define your cashflow (+ve for inflow while -ve for outflow);
- ignore growing that do not contribute to your cashflow - ZERO has a great meaning in cashflow;
- Apply IRR for the selected array with the equalized period said yearly, "=IRR(B4:B7)";
2) Excel XIRR
- define your cashflow (+ve for inflow while -ve for outflow);
- define your cashflow date into your spreadsheet;
- Apply XIRR for the selected arrays cashflow and transaction date, "=XIRR(B12:B13,A12:A13)";
3) Excel TVM
- using function RATE(period,pmt,PV,FV,mode);
4) Mathematic
- (FV/PV)^(1/n)-1, n is the number of year;
Real Life CAGR Calculation
In real life, our investment may have many transactions (e.g. initial investment, top up investment, Bonus issue worth, dividend, partial liquidation, full liquidation etc.) performed in different time line. XIRR is the universal method for the example below.
