XNPV
Definition of XNPV
Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.
Sample Usage
XNPV(A2,B2:B25,C2:C25)
XNPV(0.08,{200,250,300},{DATE(2012,06,23),DATE(2013,05,12),DATE(2014,02,09)})
Syntax
XNPV(discount, cashflow_amounts, cashflow_dates)
discount
- The discount rate of the investment over one period.cashflow_amounts
- A range of cells containing the income or payments associated with the investment.cashflow_dates
- A range of cells with dates corresponding to the cash flows incashflow_amounts
.
Notes
XNPV
is similar toPV
except thatXNPV
allows variable-value cash flows and cash flow intervals.If the days specified in
cashflow_dates
are at a regular interval, useNPV
instead.Each cell in
cashflow_amounts
should be positive if it represents income from the perspective of the owner of the investment (e.g. coupons) or negative if it represents payments (e.g. loan repayment).XIRR
under the same conditions calculates the internal rate of return for which the net present value is zero.
See Also
XIRR
: Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows.
PV
: Calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate.
NPV
: Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
MIRR
: Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income.
IRR
: Calculates the internal rate of return on an investment based on a series of periodic cash flows.
To use the XNPV Formula, simply begin with your edited Excellentable:
Begin typing the XNPV Formula in the cell you want the result to show:
- rate - The discount rate of the investment over one period.
- values - A range of cells containing the income or payments associated with the investment.
- dates - A range of cells with dates corresponding to the cash flows in values.
Dates should be properly formatted
The result will be displayed as shown below: