How To Calculate Rate Of Return On Investments Using XIRR Function

Investors consider returns to be a good benchmark while going for investments. It helps them gage how much the fund has lost or gained in a period of time. It further helps them decide whether to withdraw or invest more. XIRR Function is an Excel function that calculates the return rate for a series of cash flows. It helps to judge the profitability of investments which are not periodic. This function is a part of Financial Modelling in Excel. It is a part of the worksheet function (WS). It can be entered as a formula in the worksheet. In this article we will read to calculate Rate Of Return on Investments using XIRR Function.

Benefits Of Calculating Rate Of Return On Investments Using XIRR Function

1. XIRR calculates the returns by assigning specific dates to each cash flow.

2. It helps companies understand the feasibility of a project. It is useful in understanding the rate of return on investment. Thus, it is useful to decide and choose between many investments.

3. When one has to make capital-intensive decisions, an accurate rate of return is vital. It helps you compare and weigh the expected returns for multiple investments. It provides you with a quantifiable backing.

4. Calculating returns helps in many ways. This information helps companies who are looking to-

• Raise capital
• Make business or asset acquisitions.
• Grow their business
• Sell their assets and business units
• Forecast their finances
• Rethink their capital allocation

Features Of Calculating Rate Of Return On Investments Using XIRR Function

i. It is mainly used to determine the rate of return on cash flows, which are not periodic.

ii. Numbers in dates are usually shortened to integers.

iii. Even when calculating monthly or weekly cash flows, XIRR always gives annualized IRR.

iv. There is exists a relation between XIRR and the net present value fraction (XNPV). The return calculated by the XIRR function is the interest rate, which corresponds to XNPV=0.

v. In most cases, the guess option is not used. Then Excel assumes it be 10%

Syntax

The syntax used to calculate returns on the investment is-

XIRR(values, dates, [guess])

Where-

1. Values– This column is mandatory for calculation. This is the value that represents the series the cash flows. The first payment corresponds to the investment made at the beginning. It must include one negative value (payment) and one positive value (income).

2. Dates–This column is mandatory for calculation. This the series of dates that correspond to the respective values of cash flows. The first date will always be negative, indicating the investment made. Dates should always be entered using the date function. For example, write 1rst day of July 2020 as DATE(2020,7,1)

3. Guess – This is not required for calculation and is an optional step. This is a guess that the user makes of what the return might be. If left empty, Excel will take a value of 10%.

This particular function uses a repetitive technique to calculate returns through XIRR.

It uses a changing rate to ensure that the figure obtained is exact (within 0.000001%).

XIRR Calculation in Excel

The XIRR function uses a trial and error approach to find a rate that satisfies this equation –

Where:

Pi- The ith Cash flow or the last payment

di- The ith or last payment date

d1- The 0^th payment date

i-period number

n- total periods

How To Calculate Returns Using XIRR In Excel

1. Enter all your values in one column, under the heading “Values.” Mark, all-cash outflows like investments, purchases negative. Mark all cash inflows like income positive.

2. Enter the corresponding dates in the next column, under the heading “Dates.” Add the defined names you’ve created to the XIRR formula, which will look like XIRR(Values, Dates).

3. Use the XIRR function to select the range of values and dates you want to calculate returns on. After selecting, apply the formula which looks like XIRR(A2: A5, B2: B5)

4. If you want, enter your expectation as a guess. Edit the XIRR formula to enter a guess. This anticipated return rate helps Excel arrive at the result. The formula with the guessed return rate will look like XIRR(A3: A5, B2: B5, -20%)

Example 

Let us understand the working of this Excel function using some real-life examples

Investment 1

Investment 2

Investment 1 example shows an Internal rate of return to be 26.602%—the cash flow values in range A3: A7 with corresponding dates in B3: B7.

It assumes that the company invested an initial cost of ₹7500 on Jan 1, 2019. The table has a record of all later incomes and dates.

=XIRR(A3: A7, B3: B7)

RESULT: 26.602%

Investment 2 example shows an Internal rate of return to be 21.863%—the cash flow values in range D3: D7 with corresponding dates in E3: E7.

It assumes that the company invested an initial cost of ₹5000 on April 31, 2019. The table has a record of all later incomes and dates.

=XIRR(D3: D7, E3: E7)

RESULT: 21.863%

Errors While Calculating Rate Of Return On Investments Using XIRR Function

XIRR function is quite easy to use if one keeps in mind the following common mistakes made-

#NUM! Error occurs

• If they’re not even 1 positive or 1 negative cash flow value
• If the Values and Dates columns have different lengths (different number of columns)
• If any of the subsequent dates precede the first date
• If the calculation fails to provide an accurate value after 100 tries

#VALUE! Error occurs

• If a provided date is not identified in the Excel data cell
• If any of the provided values are not numeric

XIRR In Mutual Funds

Perfect regular investment is ideal but is often found to be unrealistic. In the long run, investments are usually found to be irregular. Early withdrawals or late deposits sometimes occur in a more realistic situation. Depending on a simple interest rate, a return is not advisable in this situation.

In the case of SIP, various investments mean different time periods for each. This makes calculating returns a little difficult with the usual IRR.

One tends to invest and withdraw mutual funds as per their wish. This results in cash inflows and outflows at different periods of time. In some cases, the time and quantity of investment are necessary to calculate the true outcome. All these problems can be usually resolved with the XIRR function. It takes into account all the differences and presents a blended return on mutual funds. XIRR can calculate returns invested through SIP or lump sum, or SWP or lump sum.

Comparing XIRR With Other Excel Functions

Extended Rate on Returns (XIRR) Vs Compounded Annual Growth Rate (CAGR)

• CAGR gives the compounded annual growth rate. At the same time, XIRR is the average rate by each cash flow during the period.
• A calculator calculates CAGR, whereas a formula on an Excel sheet calculates XIRR.
• CAGR does not consider multiple cash flows. It considers initial value, end value, and the number of years invested. XIRR considers multiple cash flows.
• CAGR assumes that we invest the entire amount at the beginning. Since this is not always true, XIRR helps by accounting for periodic investments.
• CAGR is beneficial when investing a lump sum investment. XIRR is beneficial when investing at different times.
• CAGR gives absolute returns, whereas XIRR gives annualized returns

Extended Rate of Return (XIRR) Vs Internal Rate of Return (IRR)

• The most basic difference between these two Excel functions is their interval period. IRR calculates returns on investments occurring at regular intervals. At the same time, XIRR calculates returns on investments occurring at irregular intervals.
• Because of this, the syntax of IRR does not include a date column. But the syntax of XIRR must do.
• In IRR, all payments or incomes are equidistant. For example, 1rst of every month or April 31rst every year. No such thing exists for XIRR.
• All transactions in IRR are monthly, quarterly, or yearly by default. There is no such assumption for XIRR.

To further understand the differences, it is important to look at it through an example. In this table, both values for XIRR and IRR are the same. With IRR, it assumes the fixed date to be Dec 31. At the same time, specific dates have been manually entered for XIRR.

Investment 1

Investment 2

As you can see in the table above, using XIRR gives a return of 16.25%. At the same time, IRR gives a return of 13.45%. This is quite a big difference between the same values.

FAQs

Conclusion

This article has all the knowledge one would need to decide whether the XIRR function is for them. Calculating returns through this will make managing your investments easier. You can also then spend more time growing and expanding your business.