Syntax. XIRR(Values,Dates,Estimated_Value)
Definition. This function returns the internal interest rate for a series of not-necessarily periodic payments in the intra-annual scope.
Arguments
Values (required) The (actual and/or expected) surpluses from disbursements and deposits, arranged without gaps in a column. Each value stands for a date. Negative surpluses have a minus sign.
Dates (required) The dates for the surpluses. The order must correspond to the payments in the argument Values. The first date specifies the start of the evaluation. All others must be after this date (without necessarily being in a specific order). The error value
#NUMBER!indicates that this requirement might not have been met.Estimated_Value (optional) For mathematical reasons a calculation of the internal interest rate for more than two periods can only be approximate, and therefore this approximation calculation can lead to different and sometimes nonsensical results (such as a negative interest rate). You can influence the calculation with the optional argument Estimated_Value as the basis for the approximation calculation.
Also note the following:
Date specifications must not have a time; decimal places are truncated.
Invalid date specifications return the
#VALUE!error.XIRR() expects at least one positive and one negative payment. Otherwise the function returns the error
#NUMBER!. The first payment itself does not need to be negative.If Values and Dates have a different number of values, XIRR() also returns the error
#NUMBER!.
Background. Even though the classical investment calculation calculates with annual periods, there are several tasks that need to be taken care of intra-annually. Among those is the evaluation of short-term financial transactions
The calculations are based on the cash value formula for payments, which for fractional annual periods doesn’t calculate with a fractional interest rate but rather with a fractional duration. The basis is still an entire year, which is probably because of the fact that a daily interest rate (or even smaller periods) with zeros to the right of the decimal point cannot be imagined well enough during interpretation. The cash value (also called capital value) is calculated as follows:
The annual interest rate, which makes the cash value zero, is called the internal interest rate (yield or effective interest rate).
Note
The value for the calculation cannot be chosen at random. Negative yields suggest that more money has flowed in a direction from which it later does not return. The cause of this problem can be nonapplicable amounts or “false” dates. If an investor invests 100 monetary units and later receives three times 33, there must be a mistake. This is not the case, however, if he invests three times 33 and later receives 100. Number sequences with such effects can be created and nested as desired.
There are only a few special cases in which the equation of the formula shown earlier in this section permits a solution. Therefore, Excel must work with a mathematical approximation. This moves you, step by step, toward a solution (if there is one), assuming that you are working from a good starting position. You can support this iteration by giving it an appropriate estimated value for the internal interest rate, but you cannot force a calculation of a solution.
When you are evaluating financial investments in normal everyday situations, there should be few problems. Evaluating investments in material assets is not recommended in fractional years, because the surpluses are not certain.
Examples. The following examples show how to use the XIRR() function.
Consumer Credit. A dealer advertises a washing machine for $599.00 with a financing option (this is an installment loan or a consumer loan). He asks for a monthly payment of $52.48 for 12 months. How high is the effective interest rate?
To answer this question, enter 13 dates in one-month intervals into a column. Next to the first date, enter the cash price with a plus sign in front, and below this enter the monthly rates with minus signs.
Using XIRR() then returns 9.8 percent.
Depending on regulations in some countries and regions, it might be that the effective interest rate is calculated by some other special methods.
Insurance Premium in Installments. Insurance companies offer their clients the option of paying their premiums in installments with a surcharge. Normally there is a surcharge of 3 percent for biannual, 5 percent for quarterly, and 8 percent for monthly payments.
At which effective interest rate does the client need to pay back the loan?
One option is to use the cash value formula shown earlier for evaluation.
By now there are also offers that use discounts rather than surcharges. Thus, agreements can be evaluated in the same fashion.
For quarterly payments of a $1,000.00 premium, there is the alternative of four payments of $262.50 each. The insured can initially take the “saved” money of $737.50 to the bank and withdraw $262.50 every three months. XIRR() tells you at which (annual) interest rate the bank would have to pay interest on the balance (interest payments every three months) so that the account will not be overdrawn at the end and the final rate will be available. The result is quite high: 14.2 percent. Therefore, the insured can think about whether he wants to pay the entire premium immediately by using an overdraft credit.