Decode XNPV Function

[Fowmy]

Hi,

I am trying to calculate the XNPV using the below formula here but facing a problem in matching with the result from the XNPV function. The difference is 23.01. Can someone help me where I am going wrong?

XNPV Calculation.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Rate	10%
3
4	Values	Dates
5	1,000.00	05-Jan-20		1,000.00	110.00%	909.09
6	1,000.00	15-Feb-20		2,000.00	101.08%	1,978.70
7	1,000.00	10-Mar-20		3,000.00	101.71%	2,949.51
8
9
10
11					XNPV:	2,972.52
12
13						(23.01)
14
15
16
17
Sheet1 (3)

 

[joeu2004]

The formulas in column G should be of the form =A5/E5 in G5. The NPV is =SUM(G5:G7) in G8.

The NPV discounts each cash flow (A5:A7), not the cumulative balance (E5:E7).

FYI, an alternative calculation is: =SUMPRODUCT(A5:A7 / (1+C2)^((B5:B7 - B5)/365))

And in fact, that alternative is necessary if C2 might be negative, because Excel XNPV does not permit a negative discount rate, for no good reason at all. (A design flaw, IMHO.)

 

[Fowmy]

The formulas in column G should be of the form =A5/E5 in G5. The NPV is =SUM(G5:G7) in G8.

The NPV discounts each cash flow (A5:A7), not the cumulative balance (E5:E7).

FYI, an alternative calculation is: =SUMPRODUCT(A5:A7 / (1+C2)^((B5:B7 - B5)/365))

And in fact, that alternative is necessary if C2 might be negative, because Excel XNPV does not permit a negative discount rate, for no good reason at all. (A design flaw, IMHO.)

Thanks, got it.