XNPV Function in Google Sheets

Calculate the net present value for a series of cash flows using the XNPV function.

XNPV Function

The XNPV function in Google Sheets calculates the net present value (NPV) for a series of cash flows that may not be periodic, based on a discount rate.

Syntax

XNPV(discount, cashflow_amounts, cashflow_dates)
  • discount: The discount rate to apply to the cash flows. This is a required parameter.
  • cashflow_amounts: An array or range of cash flows. This is a required parameter.
  • cashflow_dates: An array or range of dates corresponding to the cash flows. This is a required parameter.

Examples

  1. Basic Calculation

Calculate the net present value for a series of cash flows:

=XNPV(0.1, {-1000, 300, 400, 500}, {DATE(2023,1,1), DATE(2023,6,1), DATE(2023,12,1), DATE(2024,6,1)})

This will output the net present value.

Notes

  • XNPV is particularly useful for cash flows that occur at irregular intervals.
  • Ensure that the dates and cash flow amounts correspond correctly.
  • NPV: Calculate the net present value for periodic cash flows.
  • XIRR: Calculate the internal rate of return for a series of cash flows.