Syntax. XNPV(Rate,Values,Dates)
Definition. This function returns the capital value for a series of not-necessarily periodic surpluses (as the difference between deposits and disbursements) in the intra-annual scope.
Arguments
Rate (required) The annual interest rate for the calculation. This can be a balance interest rate, an interest rate on a current account, or a market interest rate—whatever best defines the initial situation.
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 Values argument. 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.
Also take note of the following:
Date specifications must not have a time; decimal places are truncated.
Invalid date specifications return the
#VALUE!error.XNVP() 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, XNPV() also returns the error
#NUMBER!.
Background. For more information about this function, see the background information for the function XIRR(). The cash value determined with the formula used in the background information is the capital value calculated by XNPV() with a given interest rate.
Example. A dealer offers his customers the option of paying invoices later or receiving a discount for immediate payment. Therefore, paying later represents a form of money lending. Let’s assume that there are three invoices with the data from Table 15-8.
Table 15-8. Fictitious Invoice Amounts with Discount Terms
Date | Amount | Discount | Payment Target |
|---|---|---|---|
1/2/2010 | $700.00 | 0.5% | 14 days |
4/3/2010 | $300.00 | 1% | 4 weeks |
7/7/2010 | $250.00 | 2% | 2 months |
Does granting this kind of a loan make sense from a finance mathematical point of view, when there are forms of investment that have a yield of 10 percent per year?
The function XNPV() is used for the finance mathematical solution, as shown in Figure 15-8 (but issues regarding customer relations, liquidity, or solvency are not taken into consideration).
Let’s assume that the customer does not take advantage of the discount option. Then it is as if the dealer took the money to a fictitious bank upon delivery and then withdrew it when the payment was received. The bank’s evaluation according to the cash value principle used here shows a positive capital value. Therefore, the dealer receives more than 10 percent interest per year.
Using XIRR() returns an effective interest of 13.58 percent annually. You also get the same impression by doing a rough calculation in your head, extrapolating the discount rates (which are basically anticipative interest rates): 1 percent for one month is 12 percent for the whole year.