XIRR

Suppose you have a series of investments and withdrawals with different amounts and on different dates in your portfolio, be it stocks or mutual funds or RD, how would you calculate the actual return you have obtained? The XIRR function in Excel can help you. The Excel XIRR function returns the Internal Rate of Return for a supplied series of cash flows (ie. a set of values, which includes an initial investment value and a series of net income values, which are not necessarily periodic). This calculation uses a schedule of payments starting with an initial investment, along with the net income payments, to calculate the compounded annual rate of return.

The value of the XIRR is calculated as the value of rate that satisfies the following equation:

where Pj is the j'th payment, dj is the j'th payment date and d1 is the 0'th payment date.

The format of the function is : XIRR( values, dates, [guess] ) 

values - A reference to a range of cells containing the series of cash flows

dates - A series of dates, corresponding to the cash flows

[guess] - An initial guess at what you think the IRR might be. This is an optional argument.

Points to remember:

• The first entry must have the earliest date; later entries may be in any order.
• Investments are entered as positive amounts and withdrawals as negative amounts. 
• The current/final portfolio is entered as a negative amount. 
• The result generated by XIRR is Compounded Annual rate of Growth/Return
• Total period shall not be less than one year.