XNPV

Returns the net present value of an investment with irregular cash payments.

Syntax:

XNPV(rate, payments, dates)

rate is the annual interest rate.

payments is a range or array containing payments made, positive if paid to you, negative if you pay.

dates is a range or array containing the dates on which those payments are made.

XNPV calculates a net present value on the first date given ( $d a t e_{0}$ ), using the formula:

$\sum_{i} \frac{p a y _{i}}{( 1 + r a t e ) ^{(d a t a_{i} - d a t e_{0}) /365}}$

where $p a y_{i}$ is the $i^{t h}$ payment and $d a t e_{i}$ the $i^{t h}$ date, i starting at 0.

The calculation assumes a 365 day year, even in a leap year.

Example:

XNPV(5%, B2:B4, C2:C4)

where B2:B4 contains -1000, 500, 550 and C2:C4 contains the dates 2008-01-01, 2009-01-01, 2009-07-01, returns approximately -12.65. If you bought an investment for 1000 on 2008-01-01 which paid 500 on 2009-01-01 and 550 on 2009-07-01 you'd make a small loss, compared to investing at a 5% interest rate.

XNPV(5%, B2:B4, C2:C4)

where B2:B4 contains 0, 500, 550 and C2:C4 contains the dates 2008-01-01, 2009-01-01, 2009-07-01, returns approximately 987.35, the net present value on 2008-01-01 of an investment which paid 500 on 2009-01-01 and 550 on 2009-07-01, using a 5% interest rate.

Application:

An application of using the XNPV function is evaluating a private equity investment with an irregular cash flow schedule. Unlike a bond or a traditional loan with fixed payment dates, private equity investments often have unpredictable capital calls and distributions.

Let's consider a scenario where a private equity firm, "InvestCo," is evaluating an investment in a startup company, "TechGenius." The investment is made over several years with different cash flow dates and amounts. The firm uses a discount rate of 12% to account for the risk associated with this type of investment.

Here is the table of cash flows and their corresponding dates:

	Date	Cash Flow	Description
	A	B	C
1	1/1/2023	-$5,000,000.00	Initial investment (Capital Call)
2	9/15/2023	-$2,500,000.00	Second capital call for product development
3	6/1/2024	$1,200,000.00	First partial distribution from a successful product launch
4	3/10/2025	$3,500,000.00	Second distribution from a Series B funding round
5	12/15/2026	$8,000,000.00	Final distribution from the sale of the company

To calculate the Net Present Value (NPV) of this investment, InvestCo would use the XNPV function.

The XNPV function would be set up as follows:

XNPV(rate, values, dates)

Where:

rate is the discount rate, which is 12% or 0.12.
values is the range of cash flows: (-$5,000,000, -$2,500,000, $1,200,000, $3,500,000, $8,000,000).
dates is the range of corresponding dates: (1-Jan-2023, 15-Sep-2023, 1-Jun-2024, 10-Mar-2025, 15-Dec-2026).

The formula would look like this:

XNPV(0.12, {-$5000000, -$2500000, $1200000, $3500000, $8000000}, {1-Jan-2023, 15-Sep-2023, 1-Jun-2024, 10-Mar-2025, 15-Dec-2026})

The XNPV function would then calculate the present value of each cash flow based on its specific date and sum them up to provide a single NPV figure.

A positive XNPV would indicate that the investment is expected to generate a return greater than the 12% required rate of return, making it a potentially attractive investment. A negative XNPV would suggest the opposite, indicating the investment is not expected to meet the firm's return hurdle. In this example, the XNPV would be a positive value, signaling a profitable investment for InvestCo.

Result of XNPV(0.12, {-$5000000, -$2500000, $1200000, $3500000, $8000000}, {1-Jan-2023, 15-Sep-2023, 1-Jun-2024, 10-Mar-2025, 15-Dec-2026}):

1554278.468188306