Know your XIRR : Excel Calculations

General
1

Profit is one of the most important objectives of any Investor or trader. Calculating your profit can not only help you determine your level of success, it also provides information on how you can better your returns.

Profit being an absolute value, it doesn’t give you the right picture. Profit should always be seen in relative terms to the investment made.

In stock market, we frequently add and withdraw funds. Our investment levels won’t remain same all the time. Hence the most appropriate indicator to know your profitability is XIRR.

What is XIRR?
The Extended Internal Rate of Return (XIRR) is a single rate of return that, when applied to each instalment (and any redemption), yields the current value of the entire investment.
XIRR is a tool for calculating returns on assets where many transactions occur at different times.

Step-by-Step process to calculate XIRR in excel

  1. Make a single column for all of your cash flows. Outflows, such as investments and acquisitions, will be marked as market negative, while all inflows, such as redemptions, will be marked as market positive.

  2. Attach the transaction’s accompanying date to the next column.

  3. Mention the prevailing value of your holding and the date in the last row.

  4. In Excel, use the XIRR function, which looks like this: = XIRR (values, date, Guess).

  5. Choose values for a series of cash flows that correspond to a payment schedule in dates, where the date columns represented the date when the first investment was made and when the cash flows were obtained. The Guess parameter is optional (if you do not put any value, excel use a value of 0.1).

How do we get details on the transactions:

  • Investments and redemptions:
    Download the ledger statement in excel format from the time you have opened Zerodha account. Filter voucher type by bank receipts and bank payments. Bank receipts will be your outflows (Negative)
    Bank payments will be inflows (positive).

  • Dividend, SGB interest Received:
    If you are maintaining your books of accounts then it becomes easier to get this data. I personally do it in tally prime. So I just have to download my dividend ledger. Since these are cash inflows, mark them as positive.

  • IPO
    Value of shares received in IPO will be included in the account value. So make sure you include this too. Figures should be in negative

  • Buyback:
    If you have received any funds from buyback, record this in positive.

  • Mutual funds investment post August 1, 2022:
    Since the amount is directly debited and credited to bank account this is now an additional item post August 1, 2022

  • Account Value:
    This is found in the dashboard of Console. This includes the current value of all your holdings, realized profit, unrealized profit and so on. Basically if you close your account right away after closing all your positions and selling your holdings, this is what you will have in hand.
    Note: Sometimes Zerodha Console shows wrong value. So have a rough idea about your account value. Usually they rectify is within 2 to 3 days.

This is how your excel sheet will looks like.

Demo
Demo704×752 151 KB

Let me know if I have missed anything.

And yes, if you do make use of this, let us know when you opened zerodha and whats your XIRR since then.

4 Likes
2

How do you account for taxes, to calculate post tax returns?

3

I won’t be able to calculate the exact post tax returns for the following reasons.

  1. I have other sources of income
  2. I have both equity and FNO
  3. I have both STCG and LTCG
  4. Account value used in my calculation will have both realised and unrealised profit. And tax is to be paid only after profit is realised.

However if you have no other income other than stock market related, then you can show tax paid as an outflow in excel calculation.

1 Like
4

Thank you, let me see if this helps me.

5

I have a query on this.
My NPS statement states the following:-

XIRR - 10.81
Return for the selected period - 40.12%.
Started NPS Nov 2010.

My query is what does this 10.81% represent. I always thought this was my annual return, but after reading about xirr, confused and lost. Could you please explain what this number represents. How do I interpret this number in relation to my investment in NPS. How do I compare it with a FD rate.
@Jason_Castelino

2 Likes
6

If XIRR when compared with a FD return is more, then it is good, because the investments in product despite not invested all at once gave a better return compared to a FD, which is created one time.

7

I think I got the answer XIRR of 10.18 actually means it is for p.a return since date of inception based on the irregular cash flows.

8

I was late to respond.

To put it in other words, if you had kept separate FDs with interest of 10.18 percent on annualised basis, the current value (Principal value plus accrued interest) of those FDs would have been equal to your current value of your NPS as on today’s date.
I hope I didn’t confuse you more.

So can we compare XIRR with FD coupon rate? the answer shall be in affirmative.

1 Like
9

Yes, XIRR calculation is for irregular cash flows, different amount, different dates, unlike the IRR for regular cash flows, and CAGR as you know is for FDs or other one time investments.

It is said in the above article, which is extensive, that CAGR can also be calculated along with XIRR for irregular cash flow, but I choose not to because if XIRR for a particular amount in a particular time frame without one time investment is more than the CAGR for the same amount in the same time frame, that is enough for me. I did not invest the amount in one go and did not invest on one day, but got more despite investing in chunks and on different dates.

Am I correct? @Jason_Castelino

10

Yes. If you know how IRR is calculated, the concept is same. Same formula that we use for CAGR is used for IRR. The difference is we use CAGR when it’s one lumpsum and IRR when we have multiple cash flows. Further when we have multiple cash flows at irregular intervals, further modification is required to IRR and that’s called XIRR. So all the 3 use same concept of compound interest.

1 Like
11

Edit: got the answer here. Will track account value with my next trade.

12

Can’t post more than one embedded image so hers the follow up image to the previous post

image
image326×510 47.5 KB

13

Does anyone know why there is a difference in XIRR between Zerodha and my formula here?
image

14

You are tracking dividends also.

15

Even, without dividends, the XIRR works out above 35%, have tried both. Also on Zerodha It says they calculate XIRR including splits and dividends

16

Without dividends, it is below 35% only. I believe Zerodha does not include dividends. However, there is still some difference in what Zerodha shows.

With or without dividends, the way you are doing is totally fine.

image

17

2023 wrap-up: 24.5% XIRR

i.e. ~3% underperformance wrt Nifty 500 TRI.

I guess that’s the risk one carries when trading with >50% Gsec margins during years where equities outperform.

PS: lmk what xirr did you guys clocked in 2023

1 Like
18

I didn’t note down my opening account value at the start of the year. So my xirr will show from 2017 only.