**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.**

Hi Nitin,

I used the XIRR function explained above for my home loan. Amount disbursed is Rs 14117288 ( entered as positive) and EMI Rs 184609 ( entered as negative) for 120 months. The result is 0.104067.

The projected rate for home loan is 9.5%. I have the entire working from the bank with details of interest and principle component, and the XIRR sheet I made based on your article.

Could you advise where I have gone wrong, and if not, what to interpret out of the result of XIRR.

XIRR function is used for calculation of returns and has some shortcomings as i shared in my post. You may calculate using EMI calculator as available on following link

https://www.nitinbhatia.in/home-loan/emi-calculator/

I tried to use the XIRR for two more cases:

1. I bought a property on 01st June 2006 for 25 lakh and sold it for 56 lakh in 2014. XIRR result shows 0.102594.

2. I bought a property on 01st Sep 2008 for 65 lakh and spent 20 lakh on interiors. I will be selling it for 1.2 Cr on 01st Apr 2016. XIRR result shows 0.05522.

The payments were entered as negative and returns as positive. Kindly guide where I am going wrong or what to interpret from these values.

It seems the function is not applied correctly. It should return % fig as i shared in my screenshots. I suggest you to check examples on web or download excel with XIRR function.

Hi Nitin,

I have seen your post and find they are real truth of life. I searched your blog and others, did not find a consize form of formula to calculate %interest rate(yearly/monthly) from sip value, number of sip (uniformly monthly) and current value so that I can make decision should continue or withdraw/stop etc,because it gives me clearcut idea, where I am and compare with post office/fd rate etc. Can you share such formula(mail id – mahapushpa@yahoo.com).

Currently i don’t have any such excel sheet to share. I will prepare and share it in future.

Hi Nitin,

I am really inspired with your work and great information on your website. However, i am very confused on how to calculate my ROI of an investment i did in under construction property in 2013 with 80% of value of property is linked with Bank Loan and property bought in Construction Linked Plan. now how do i calculate money i have been paying to bank through regular EMI’s, portion of money as booking amount has also born by me during booking. I am confused as overall i know it is in huge loses but real calculations i dont know. Could you help me with some Calculator which can help me to find out this

You can calculate using XIRR function of Excel. You should have details like cost of property, CMP, your payments and interest component of each home loan EMI.

Hi Nitin,

Thanks for a reply, do you have this calculator available where i can put these values and get my results

My apologies but i don’t have customized calculator for this particular scenario.