Take for example the following scenario of an investment made by you:
Date, Transaction type, Amount
10 Jan 06, Buy stock X, $8000
14 Mar 06, Buy stock X, $6000
28 Aug 06, Receive dividends from stock X, $300
20 Aug 07, Receive dividends from stock X, $400
12 Feb 08, Sell stock X, $23500
Taking the dividends portion into account as well, how do you calculate the IRR or CAGR of your investment like the above example? It is not a straight forward task to calculate IRR or CAGR using calculator. Thanks to Mircosoft, we can make use of XIRR function from excel to do the computation. I will guide through with you on using this XIRR function.
Step 1: Open a new excel worksheet and change the format of column A to an accounting type with $ symbol or else the data is not valid later on
Step 2: Fill all cell values exactly as shown in the image above. A bracket denotes negative, payment or investment amount forked out. A positive value denotes amount received from investment or current invesment holding
Step 3: Select cell D8 then click fx -> select Financial in category box -> select XIRR -> click OK
If you can't find XIRR function, do the following steps:
- On the Tools menu, click Add-Ins
- In the Add-Ins available list, select the Analysis ToolPak box, and then click OK
- If necessary, follow the instructions in the setup program
Step 4: When a function arguments window pops up, type A2:A6 for Values and B2:B6 for Date. Then click OK
Step 5: Computed result of 0.317 will be shown on cell C8. To view the number as a percentage, click on cell D8 -> click Cells on the Format menu -> click the Number tab -> select Percentage in the Category box
The IRR or CAGR for this example turns out to be 31.7%. It is more meaningful than simply computing its absolute returns. You can also follow the same steps if you want to calculate the IRR of your expenses and incomes as long you take note of a negative sign for each cash outflows and a positive sign for each cash inflows. I hope you find this guide useful and if you think that there is any error, please do let me know.
7 comments:
My blog has few related articles:
1) Number of Period (NPER) & Payment Amount (PMT)
2) Future Value
Startwealthy
thanks! yeah .. useful way to track returns for MMF too as the movement in those funds are more frequent
durio yes you can do for funds as well. thanks startwealthy. for future value and present value, i already got an example as well.
Just wondering if most of indices showing annualised returns out there are calculated using CAGR method?
Thanks.
hi Collin sorry for the late reply. i just came back from overseas
AFAIK most of the indices show CAGR without dividends reinvested with S&P500 being an exception. The S&P500 publishes returns with dividends reinvested
so you need to know if the dividends are reinvested or not
Thanks for the well -illustrated superb formula.I could easily convert my absolute returns to CAGR.
absolutely useful.now I got how to use XIRR and now I am able to convert absolute return to annualized return without problem.
Post a Comment