In the previous post, we saw Financial Function | How to use Excel IRR Function? now we will learn about excel XNPV Function.

How to Find Financial Function in excel?

To find financial function in excel open excel and click on the “Blank” to open a blank excel worksheet now click on the ‘Formula Tab’ and under ‘Function Library’ click on the ‘Financial’ option as shown below:

What is XNPV Function?

XNPV function calculates NPV based on the dates of cash flows. It Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.

XNPV excel function can enable us to carry out more meaningful project analysis when the cash flows are not periodic.

NPV functions assume that the cash flows are spaced equally. However, in real life, we may come across many situations where the cashflows occur unevenly

Syntax

XNPV(rate, values, dates)

Arguments

The arguments of XNPV Rate, values, and dates

Rate    Required. The discount rate to apply to the cash flows.

Values    Required. A series of cash flows that corresponds to a schedule of payments in dates.

Dates    Required. A schedule of payment dates that corresponds to the cash flow payments.

Understand With Example

Note: All the arguments are mandatory

In the above example, the initial cash outflow is Rs. 2,50,000. Subsequent cashflows, as expected in the future, are laid down further.

Type ‘=’ and Insert XNPV function and the rate as 10%. Then specify the range of values as C5 to C10, covering the future cash flows and dates as the range B5 to B10 Close the function, hit enter and the NPV is calculated.

For Latest Updates & Learning Join Our YouTube Channel: Join Now