Return on Investment – Calculate using XIRR function of Excel

How to calculate return on investment?, Recently one of my blog readers asked me. Return on Investment calculation is the biggest mystery of the personal finance space. Some time back one of my readers told me that his Return on Investment is very low. When i checked the details, i found that he is calculating absolute returns instead of annualized returns. Let me first clarify that all the return figures published by financial institutions are annualized returns. These numbers never match with the calculation of investors. Moreover, these no’s are based on lump sum investment. Regular/irregular investments require different calculation. In one of the queries, one of the reader posted a comment that his fund house is reporting a return of 12%. Whereas according to his calculations, returns are approx 6%. The reason is that reader invested through SIP.

Besides mutual funds, reader’s find difficult to calculate Return on Investment of other financial products with variable returns. Typical examples are traditional insurance policies, equity, PPF, Sukanya Samriddhi Account, ULIP, etc. The Return on Investment of all these financial instruments is variable in nature. Secondly, the returns are also linked to the date of investment. For example, in the case of PPF, a common notion is that return is 8.70% (declared every year). It is true that if i invest on 1st April, the return will be 8.70%. Whereas if i invest in Oct then returns will be lower. In traditional insurance policies, the yearly bonus is variable in nature.

The method to be shared by me can also be used to calculate how much interest rate you paid for any loan product. It is more useful in case of private lending. The borrower calculates absolute interest but fails to understand the actual/real rate of interest that can be as high as 100%. You can also avoid mis-selling of loans as i shared my experience in the post, Mis-selling of Personal Loans by Banks.

Difference Between Absolute Returns and Annualized Returns

Typically the investors are confused between absolute returns and annualized returns. Before we proceed to calculate Return on Investment, it is imp to understand the difference between the two.

Absolute Returns: Absolute returns is very easy to calculate. It does not take into account the time factor. Some time back, my father’s small savings investment matured. He was quite happy and told me that his money doubled i.e. Return on Investment is 100%. The simple way to calculate is appreciation over amount invested. He invested Rs 1 lac and maturity amount was Rs 2 lac. The profit or return was 1 Lac. Therefore, absolute Return on Investment was 100% in this case i.e. investment amount doubled.

Annualized Returns: Annualized return takes into account the time taken to deliver the return. Considering the same example, i asked my father how many years it took to double the investment. He told me six years. Now Annualized return or CAGR (Compound Annual Growth Rate) is 10.40%. The returns are decent enough but not so great.

In the example mentioned above, we observed that Return on Investment is 100% in case of absolute returns. On the other hand, in the case of annualized returns, it is 10.40%. Annualized return is the correct way to calculate returns.

Return on Investment – Calculate using XIRR function of Excel

On the web, you may find a lot of returns calculator, but they have limitations. Most of them are for specific schemes or financial products. As an investor, i am keen to know what is my Return on Investment. There are two ways to invest i.e. at regular intervals and irregular intervals. The XIRR function can be used in both the situations to calculate Return on Investment. Let’s first understand XIRR function in MS Excel.

XIRR function calculates the Internal Rate of Return at both regular and irregular interval. In some cases, experts suggest the use of IRR function (Internal Rate of Return) for investments at regular interval. Personally, i don’t agree as it gives the wrong picture. The XIRR function is =XIRR(Values,dates,[guess]). You may ignore guess field or enter a guess as 0.05. You may find some confusing explanations related to cash flow while using XIRR function, but i will keep it simple. All investments should be -ve and maturity amount should be positive. For loan purpose, enter EMI as negative value and loan amount is positive. For example, if investment/EMI is Rs 500 then i will enter -500. Maturity/Loan amount is positive. In short, the amount paid is negative and amount received is positive. To know more about XIRR function Click Here.

(a) Investment at Regular Interval: The best example of investment at regular interval is SIP (Systematic Investment Plan). You invest at regular intervals i.e. fixed date of the month assuming first of every month. Also to add that Return on Investment on SIP will always be different from annualized returns declared by fund houses. As i explained, the fund house assumes lump sum investment whereas SIP is scattered over a period. To calculate SIP returns, let’s take the example of SIP of Rs 500 for 12 months. At the time of maturity, the investor received Rs 6500. The absolute return in this case is 8.33% i.e. ((6500-6000)/6000)*100. Let’s check annualized return using XIRR function.

In the above screenshot, you can check how the XIRR function is used to calculate Return on Investment of SIP. The annualized return or CAGR is 15.67%.

(b) Investment at Irregular Interval: Sometimes due to irregular cash flow, investors invest at irregular intervals. No problem, you can use the same function by changing dates. Please check following screenshot.

In this case, the annualized returns are negative i.e. -5.44%. An investment of Rs 41,500 yielded Rs 39,000 at maturity. Therefore, XIRR function can also calculate negative returns.

Real Rate of Interest

To avoid any mis-selling of the loan, you can use XIRR function to check the actual rate of interest. For example, if you avail a personal loan of Rs 1 lac and pay EMI of Rs 4584 per month for 24 months. The bank employee will explain to you that your repayment will be Rs 1.10 lac in 2 years i.e. 4584 x 24.  Therefore, on a loan amount of 1 lac, interest is Rs 5000 per annum thus personal loan interest rate is 5%. Quite smart, isn’t it. Let’s check annualized interest rate

The annualized interest rate, in this case, is 9.87% against 5% claimed by the bank employee. In layman terms, it is beneficial for banks to quote absolute returns while pitching loan products. Whereas for investment purpose, the Return on Investment based on annualized returns is a more attractive proposition.

Words of Wisdom: Most of the investors fail to notice the lower CAGR or returns. The annual return of a fund can be 10% (reported by fund house), but SIP return might be very low or negative depending on timing. I shared in my post, Why you should not invest in SIP? that timing is important. In the case of an absolute return, the calculation might lead to a wrong investment decision. As we observed that annualized return was 15.67% compared to an absolute return of 8.33% in example (a). An investor cannot rely on others for financial analysis as all stakeholders will share the analysis as per their benefit. The XIRR function has some shortcomings, but it is a useful tool for investors to do fundamental analysis.

It can also be used for financial decision making like whether to invest or not. I also use XIRR function for calculation of Return on Investment on the property. You can also include home loan and rental yield to calculate net returns. The more and more factors you add, the calculations become complex. More complex calculations on return on investment result in right decision-making :). Just to add that it is one of the function/tools used for financial analysis. I also use multiple other tools and functions to verify the outcome of the financial analysis. In my opinion, all financial decisions should be backed by proper calculations and analysis. Hope you liked the post.

Copyright © Nitin Bhatia. All Rights Reserved.