To calculate XIRR, you need two sets of data: the cash flows and the corresponding dates. The cash flows should include both the initial investment (negative value) and the dividend payments (positive values).
Let’s consider a simple example: (all in Rs)
- Initial investment (Year 0): -1000 (negative because it’s an outgoing cash flow)
- Dividend received at the end of Year 1: 150
- Dividend received at the end of Year 2: 200
- Dividend received at the end of Year 3: 250
Now, assuming you didn’t reinvest the dividends, your cash flows would be: -1000, 150, 200, 250.
If you reinvested the dividends, your cash flows would include the additional investment at the time of reinvestment. Let’s assume you reinvested each dividend immediately:
- Initial investment (Year 0): -1,000
- Dividend reinvested at the end of Year 1: -150 (negative because it’s an outgoing cash flow for reinvestment)
- Total invested at the end of Year 1: 1,150
- Dividend reinvested at the end of Year 2: -200
- Total invested at the end of Year 2: 1,350
- Dividend reinvested at the end of Year 3: -250
- Total invested at the end of Year 3: 1,600
Now your cash flows would be: -1000, -150, -200, -250, 150, 200, 250.
To calculate XIRR in Excel or a similar tool, you can use the XIRR
function. Here’s how you would use it in Excel:
excel
=XIRR(cash_flows, dates)
For the first case (not reinvesting dividends):
excel
=XIRR({-1000, 150, 200, 250}, {0, 1, 2, 3})
For the second case (reinvesting dividends):
excel
=XIRR({-1000, -150, -200, -250, 150, 200, 250}, {0, 1, 2, 3, 1, 2, 3})
These formulas will give you the XIRR for the respective scenarios. The result is the annualized rate of return for your investment, considering both the initial investment and the cash flows from dividends.