How To Calculate XIRR Using Excel?
1. Put each transaction you make into a single column. All inflows are recorded as positive, such as redemptions, and all outflows, such as investments and purchases, will be indicated as market-negative.
2. Include the relevant transaction date in the following column.
3. Mention the date and the holding’s current value in the final row.
4. Next, using Excel’s XIRR function, which looks like this: XIRR (values, time, estimation).
5. Choose values for a sequence of cash flows that match a date-based payment plan. The date columns represent the dates of the initial investment and the cash flows’ receipt. If you omit the guess argument, it is not required. Excel used 0.1 as the value.
For example:
For this calculation you need is with an example of six-month SIP. Let
SIP amount = ₹ 6000
SIP investment dates = start-01/01/2024, end-01/06/2024
Redemption date = 01/07/2024
Maturity amount = ₹ 39000
Assuming following cash flows:
01-01-2024 | -6000 |
|---|---|
11-02-2024 | -6000 |
01-03-2024 | -6000 |
08-04-2024 | -6000 |
19-05-2024 | -6000 |
01-06-2024 | -6000 |
01-07-2024 | +37000 |
XIRR | 10.43820441 |
The cash flows in the table above happen at irregular times. Here, the return on these cash flows may be calculated by an investor using the XIRR function. Never forget to include the “minus” symbol when an investor makes a financial investment.
Open excel sheet:
In column A enter the dates of transaction of outflows and inflows on left side
In column B enter SIP figures of 6000 (using a negative sign as it is a cash outflow)
Against the redemption date (Column A), enter the redemption amount (Column B) (37000).
In the box below 37000, type in: ” = XIRR (B1: B7, A1: A7)*100″ and hit enter
XIRR at the rate 10.43820441 will be shown.