Excel – Mathematical Equivalent of =NPV() Formula

calculatormicrosoft excelspreadsheetworksheet-function

Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures?

The question might be similar to these ones but the formulas are for discounting rather than compounding: What would be the the mathematical equivalent of this excel formula? =PMT(), Need mathematical equivalent of this excel formula PMT() and https://mathematica.stackexchange.com/questions/225485/two-tier-compound-interest-problem/225502#225502

Below I've got two formulas which I was told are supposed to be equivalent:

=NPV(B1,A1:A10)

=SUM(A1*((1-(1+B1)^(-C1))/B1))

However, the answer they produce is not 100% the same (it's probably 99.9999% the same). I used the IF formula to verify this:

=IF(NPV(B1,A1:A10)=SUM(A1*((1-(1+B1)^(-C1))/B1)),1,0)

There seems to be some inaccuracy somewhere — or maybe the NPV and SUM functions round up calculations differently. It appears to be the case when comparing formulas from different software like here: https://stackoverflow.com/questions/62696450/is-there-a-difference-between-numpy-npv-and-excel-npv. However, I've also checked this with Google Sheets and the formulas (as shown in the tables below) produce the same results as in Excel.

Basically I'm looking for a formula that can replace NPV. The problem with NPV is that it requires a range of values. In my case, the periodic cash flows are the same and I don't wish to type out several cells of values when they're the same. A single SUM formula only takes data from 3 cells, whereas an NPV formula will take data from 11 cells in a ten year period (and much more for longer periods).

Table with values:

10000 0.035 10 0 83,166.05 83166.05323 0 0.0000000002182787284255030000
10000
10000
10000
10000
10000
10000
10000
10000

Table with formulas:

10000 0.035 =COUNT(A1:A10) =IF(NPV(B1,A1:A10)=SUM(A1*((1-(1+B1)^(-C1))/B1)),1,0) =NPV(B1,A1:A10) =SUM(A1*((1-(1+B1)^(-C1))/B1)) =IF(E1=F1,1,0) =E1-F1
=$A$1
=$A$1
=$A$1
=$A$1
=$A$1
=$A$1
=$A$1
=$A$1
=$A$1

Best Answer

Seems that you want to use NPV but just don't want to have to create a range of duplicate values. You can continue to use the NPV function but build an array using SEQUENCE instead of using a range if the cash inflows/outflows are identical.

=NPV(B1,SEQUENCE(C1,,A1,0))

In the formula above, SEQUENCE is used to create a 10-row array of values starting with the value in A1 and incrementing the value by 0 for each subsequent row.

You could also use the Name Manager to create a Custom NPV formula, CNPV.

=LAMBDA(rate, num, cash, NPV(rate, SEQUENCE(num,, cash,0)))

Then you could use the CNPV formula by entering

=CNPV(B1, C1, A1)

Related Question