Use a formula to set the final value in a string of cash flows for XNPV?

Originally Posted by wahoorah

I am trying to use a formula to calculate the final value in a string of uneven cash flows so that the XNPV would equal 0 at a specific discount rate. So, for example [....]
Discount Rate 10%
Date Value
1/15/2015 ($1,000,000)
4/20/2015 $20,000
8/18/2015 $15,000
9/5/2015 $25,000
11/12/2015 $18,000
12/31/2015 $1,014,778

See XNPV help page for the math formula. Solve for the last term. For example:

	A	B
1	IRR	10.00%
2	1/15/2015	-1,000,000.00
3	4/20/2015	20,000.00
4	8/18/2015	15,000.00
5	9/5/2015	25,000.00
6	11/12/2015	18,000.00
7	12/31/2015	1,014,777.24

The formula in B7 is:

Please Login or Register  to view this content.

[EDIT] Or simpler:

Please Login or Register  to view this content.

Caveat: Excel XNPV does not work when the discount rate (IRR) is negative. Probably not an issue for you.