The XNPV function returns the net present value (NPV) of an investment based on a discount rate and a series of cash flows that occur at irregular intervals. Values represent cash flows and be correspond to dates. Negative values represent cash paid out; positive...Read more
The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Payments are expressed as negative values and income as positive values. If the first value is a cost or payment, it must be a negative value. Subsequent payments are...Read more
The standard Internal rate of return function (IRR) assumes all cash flows are reinvested at the same rate as the IRR. The modified internal rate of return function (MIRR) accepts both the cost of investment (discount rate) and a reinvestment rate for cash flows received.
In the example...Read more
NPV calculates the net present value (NPV) of an investment using a discount rate and a series of future cash flows. The discount rate is the rate for one period, assumed to be annual. NPV in Excel is a bit tricky, because of how the function is implemented. Although NPV carries the idea of "net...Read more
The internal rate of return (IRR) is the interest rate received for an investment with payments and income occurring at regular intervals (i.e. monthly, annual). Payments are expressed as negative values and income as positive values. Amounts can vary, but intervals need to be the same. The...Read more
NETWORKDAYS is a built in function accepts a start date, end date, and (optionally) a range that contains holiday dates.
In this case, the start date is Jan 10, 2018, provided as cell B5. The end date is calculated using the EOMONTH function with an offset of zero, which returns the...Read more
In this example, the goal is to sum the amounts in column D by week number, using the dates in column C to determine the week number. The week numbers in column G are manually entered. The final results should appear in column H. All data is in an Excel Table named data in...Read more
The AND function is designed to evaluate multiple logical expressions, and returns TRUE only when all expressions are TRUE.
In this case the we simply compare one range with another with a single logical expression:
B5:D12=F5:H12
...Read more
The LN function returns the natural logarithm of a given number. The natural logarithm is equivalent to log base e of a number, where e is Euler's number, a mathematical constant with the approximate value 2...Read more
In this example, the goal is to filter the values in one array by the values in another array. For convenience, data (B4:D11) and filter (F4:F6) are named ranges. The solution is based on the...Read more