How to calculate returns on my investments using excel

1) In order to calculate returns from a series of cash-flows in Excel you have to use the XIRR formula. Please see the example below

Please note the following:-

We have used hypothetical dates for purpose of illustration. You should enter the exact dates, when you are required to pay your instalments and also the exact maturity date from your policy or scheme document

The instalment amounts have to be entered as negative numbers because these are cash-outflows for you. The maturity amount will be a positive number

In the example above the annualized return is 9.1%, but you may get a different number based on the actual dates.

2) You can calculate the SIP returns in the same way as shown for query 1 with some modifications. Please note the following to calculate XIRR:-

Enter all the transaction dates under the date column in from oldest to newest. The final date will be today’s date or the day when you doing this calculation. However, you need not enter the dividend re-investment dates because they are not cash-flows for you.

Next enter the SIP or additional purchase amounts in negative (i.e. -5,000) against each transaction date. Please enter the redemption amount (made in May 2013) as a positive amount.

Find out the latest NAV (today’s NAV or the NAV on the day you are doing this calculation). For example today’s NAV of Sundaram Select Focus (D) is 13.869

Note down the balance units, i.e. 19,350.891

Against the final date (today’s date or the day when you doing this calculation), please enter the balance amount (multiply the NAV with the balance units). For example, if you are using today’s NAV the formula will be, = 13.869 * 19,350.891. Please note this amount should be a positive number

Next calculate XIRR as shown in the response to your first query. Please include the entire cash flow range and the entire range in the XIRR formula.

XIRR is calculated assuming you will be redeeming your units on the day you are calculating. Please note that you may not be actually redeeming it on the day when you are doing the calculation. Since mutual funds are market linked instruments, you will not know beforehand what the redemption NAV will be. Hence the latest NAV is assumed to be redemption NAV.

We hope you will be able to calculate the returns easily, by following the above-mentioned steps. If you are facing any difficulty, please feel free to write to us again.