What is xirr function

You have a cash outflow which is your investment, and a cash inflow redemption. You may consider using CAGR to calculate the return if you invest a lump-sum amount in a mutual fund scheme. However, you may invest in mutual fund schemes through the SIP or redeem units through the systematic withdrawal plan SWP. It involves multiple cash flows, and you cannot use CAGR to determine returns from the mutual fund.

You can use the XIRR formula in excel sheets to calculate return from mutual funds for multiple cash flows. You must enter all mutual fund transactions such as SIP, SWP, additional purchases or redemptions, and the corresponding dates.

You will have to enter SIP transactions, and the corresponding dates from mutual fund statements in the excel sheet. For example, you invest Rs 3, per month in a mutual fund scheme through SIP. Start investing now or. To judge the profitability of this investment, compare the XIRR output with your company's weighted average cost of capital or hurdle rate. If the returned rate is higher than the cost of capital, the project can be considered a good investment. When comparing several investment options, please remember that a projected return rate is just one of the factors that you should estimate before making a decision.

For more information, please see What is the internal rate of return IRR? In case you know what kind of return you are expecting from this or that investment, you can use your expectation as a guess. For starters, please remember this — whatever cash flows you are calculating, the Excel XIRR function produces an annual rate of return. As you can see in the below screenshot, the IRR goes from 7. The difference seems too high to be justified by the time value of money factor alone:.

As an additional check, let's use the IRR function on the same cash flows. Please keep in mind that IRR will also compute an approximate rate because it assumes all the time periods to be equal:.

As the result of these calculations, we get a monthly XIRR of 7. To quickly get the internal rate of return for different projects, you can create a versatile XIRR calculator for Excel. Here's how:. Where Sheet1 is the name of your worksheet, A2 is the first cash flow, and B2 is the first date.

A series of dates, corresponding to the supplied values. An initial guess at what the IRR might be. This is an optional argument, which, if omitted, takes on the default value of 0.

Warning: If you attempt to input dates in text format, there is a risk that Excel may misinterpret them, depending on the date system, or date interpretation settings on your computer. In the spreadsheet below, the cashflow for an investment is shown in cells B2-B7. Cells C2-C7 show the dates for the cashflows. Note that, in the above example, the initial investment is a negative value as this is an outgoing payment , and the income payments are represented by positive values.