XNPV

Formulas / XNPV
Calculate the net present value of an investment using a discount rate and irregular cash flows.
=XNPV(rate,values,dates)
  • rate - the discount rate to use over the length of the period
  • values - an array of numbers that represent the payments and income
  • dates - an array of dates that correspond to an array of payments

Examples

  • =XNPV(F6,B5:B10,C5:C10)

    The XNPV function calculates the net present value of a series of cash flows, given a discount rate. This example provides a demonstration of the XNPV function. To calculate the net present value of a series of cash flows with a discount rate of 6%, the preceding formula is used. This example returns a net present value of 177.6532.

    • The XNPV function can be used to calculate the present value of a series of cash flows with a different discount rate. For example, if the discount rate is changed to 8%, the same series of cash flows can be evaluated with the preceding formula. This example returns a net present value of 123.3485.

  • =XNPV(F6,B15:B20,C15:C20)

    The XNPV function can also be used to compare the present value of two different series of cash flows. For example, if a different set of cash flows are entered into the formula, a different net present value can be calculated. To compare two sets of cash flows using the XNPV function, the formula above is used. This example returns a net present value of 148.7654.

  • =XNPV(-F6,B5:B10,C5:C10)

    The XNPV function can also be used to calculate the present value of cash flows with a negative discount rate. To do this, the formula is changed to the preceding. This example returns a net present value of -177.6532.

Summary

The XNPV function is used to calculate the present value of a series of cash flows that occur at different times. It is an alternative to the NPV function, which calculates the net present value for cash flows that occur periodically.

  • The XNPV function calculates the present value of an investment using a discount rate. It takes three arguments: rate, values, and dates.
  • The rate argument is the discount rate used to calculate the NPV.
  • The values argument is a series of cash flows associated with the investment.
  • The dates argument is a series of dates associated with the cash flows.


Frequently Asked Questions

What is the XNPV function?
The XNPV function is a Sourcetable tool that calculates the present value of a series of cash flows. It is similar to the NPV function, but it takes into account the date on which the cash flows occur.
What is the difference between the XNPV and NPV functions?
The XNPV function takes into account the date on which the cash flows occur, while the NPV function does not. This makes the XNPV function more precise than the NPV function when calculating the present value of cash flows.
What is the syntax of the XNPV function?
The syntax of the XNPV function is: XNPV(rate, values, dates).
How is the rate used in the XNPV function?
The rate is used to calculate the present value of the cash flows. It is the discount rate used to calculate the present value of the cash flows.
What are the values used in the XNPV function?
The values are the cash flows that are being evaluated. They are the amounts of money received or paid out over a period of time.
What are the dates used in the XNPV function?
The dates are the dates on which the cash flows occur. These dates are used to calculate the present value of the cash flows.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV