NPV calculation differs from Excel/Google Sheets

[softwareengineerprogrammer]

A user noticed that GEOPHIRES' calculated NPV value differs from calculating NPV from the project net revenue profile in Excel/Google Sheets. For example, Fervo_Project_Cape-3 NPV calculated by GEOPHIRES is $4561.96M while Excel gives $4,115.21M.

NPV calculation is performed in:

GEOPHIRES-X/src/geophires_x/Economics.py

Line 339 in 164f49a

NPV = npf.npv(FixedInternalRate / 100, TotalRevenue)

numpy-financial npv documentation: https://numpy.org/numpy-financial/latest/npv.html

This comment explains the discrepancy and why the numpy-financial NPV calculation is more technically correct even though the Excel version has become a de-facto standard: numpy/numpy#10877 (comment)

Points of discussion:

1. Whether to switch the calculation to the Excel standard (my thought is probably not, but open to discussion)
2. Alternatively, perhaps provide a parameter for the user to indicate how they would like NPV to be calculated
3. Documentation for the existing behavior/any changes associated with points 1 & 2

---

Edit 2025-04-01: Relevant changes were merged in #364. Follow-up issue: #375

[keithtelliott]

@softwareengineerprogrammer, I set out to understand the convention that SAM GETEM uses. The SAM docs refer to: A Manual for the Economic Evaluation of Energy Efficiency and Renewable Energy Technologies, Short W et al, 1995, National Renewable Energy Laboratory, NREL/TP-462-5173, http://www.nrel.gov/docs/legosti/old/5173.pdf

Page 41 of the manual shows an NPV example table, as shown in the following screenshot:

Note that initial investment (at time zero) is NOT discounted. First years of cashflows (years 1+) are discounted at the end of period (Page 2 notes that they consider the annual cashflows to be lumped at the end of the period, not the middle).

My hunch is that SAM follows the conventions noted in the manual. The challenge: SAM does not readily show annual present value numbers that sum to create the NPV. Next up, I will keep digging into a simple SAM to confirm their numbers/math.

My recommendations at this point...

• Follow the SAM convention in GEOPHIRES (I'll confirm that convention within a few days). This will capture two concepts:

1. Clarification of when discounting begins (i.e. discount the initial time zero investment or not) and
2. Clarification of when lump-sum cashflow occurs in a period (i.e. at the end of the period or the middle).

• Show the annual present value numbers (AKA discounted values) in readily available cashflow output, so the user can see/confirm/understand the discounting conventions.

[softwareengineerprogrammer]

Thanks for digging in @keithtelliott, love the detailed research!

Your recommendations make sense.

Clarification of when discounting begins (i.e. discount the initial time zero investment or not) and

If I'm understanding correctly, this would be fairly straightforward:

1. Internally extract/define internal variable t (starting year for NPV discount) with value 0 (existing behavior).
2. Parameterize t as a GEOPHIRES input. I'm a little unclear what the parameter name should be - "NPV Discount Start Year"? (I presume you might have a suggestion and/or gain clarity on this in your research)
3. Document behavior/context/meaning of various values for that parameter, in that parameter's tooltip text. e.g. "Starting year from which to calculate cashflow for NPV. The <default> value of 0 includes the initial cash outlay, consistent with <basis cited in comment re: equation in "Principles of Managerial Finance">. A value of 1 ignores the initial cash outlay and only counts future cashflows, mirroring NPV calculation as implemented in Excel, Google Sheets, and other commercial spreadsheet software. A value of 0.5 is used in SAM".

Clarification of when lump-sum cashflow occurs in a period (i.e. at the end of the period or the middle).

Sounds like we can dig into code to find that out and document in appropriate context(s).

Show the annual present value numbers (AKA discounted values) in readily available cashflow output, so the user can see/confirm/understand the discounting conventions.

Might have to work with you to get clarity on whether this is already present in the existing Revenue & Cashflow Profile or if it would need to be tracked as a separate/new feature. We can discuss once you've done further research and/or in weekly sync.

[softwareengineerprogrammer]

Clerical note: #344 (comment) is a continuation of this thread

[keithtelliott]

@softwareengineerprogrammer before digging into the code, let me touch on the two concepts (pardon me if I'm a bit overboard on this).

1. Timing of cashflow within a period

See the following screenshot from Page 2 of the NREL manual referenced above:

The manual author notes that they adopt the convention of assuming the cash flows are lumped at the end of each period (and a period is typically a year).

I believe GEOPHIRES should adhere to the NREL convention, which I believe SAM uses also - which should be: lump cashflow at the end of the period. I still need to study SAM GETEM to confirm this behavior.

From the GEOPHIRES user perspective, this should be clearly stated, a reasonable default should be used, and it may be reasonable to let the user adjust the default.

A long variable name may be: whenInTheCashflowPeriodDoWeLumpTheCashflow :) Since naming is hard, I'll leave the picking of the name to smarter developers. But, I think I'm communicating the concept: Cashflows are assumed to be lumped at the end or middle of the period.

ARIES (a ubiquitous oil & gas economic eval tool) uses middle of the period. The NREL manual says uses end of period. I recommend sticking to the NREL manual.

2. When to start discounting

This is the numpy npv function issue. (it's different than the when-to-lump-the-cashflow-in-each-period noted immediately above).

The numpy npv function accepts an array of cashflow values, and assumes the first value is the first period of future cashflow (call it period 1). (Correction by KTE, 3/7/2025: the numpy npv function assumes the first value is at time zero and NOT discounted. However, the Excel-style function assumes the first value is at time period 1 and IS discounted).

However, as shown in Table 4-2 in the screenshot above, the NREL manual and common financial projection convention assumes that the initial investment is at time zero, and the initial investment is not discounted.

I need to hop off for now, but will circle back tomorrow...

[softwareengineerprogrammer]

@keithtelliott Thanks again for the detailed research and feedback. Here are my findings from today:

1. Timing of cashflow within a period

As demonstrated in this prototype/proof-of-concept PR, I've confirmed that GEOPHIRES uses the same NPV calculation as the NREL reference manual you cited (or at least produces an identical result for the example given in the manual). I haven't gotten around to confirming explicitly, per se, that this means GEOPHIRES lumps cashflows at the ends of periods but this finding of parity seems to almost definitively imply that it does, if my understanding is in the right general ballpark.

2. When to start discounting

The aforementioned PR also adds a "Cashflow Series Start Year" parameter (final name & documentation still pending as of the time of this writing) with default value = 0 (existing NPV calculation behavior) and option to provide value = 1 to use Excel-style NPV calculation. The diff deeplink I referenced is to a unit test that includes demonstration of parity with Excel NPV calculation when the value is set to 1.

---

There was a confounding factor here in that I discovered 2 bugs in how the Revenue & Cashflow Profile was being output, rolled into this tracking issue I created for them: #350

1. 'Years Since Start' is off by 1 if we're going by the Period naming convention in the NREL manual (which I believe we should) - the initial cash outlay is being listed as occurring 1 year after start but should actually be 0 years from start/Period 0.
2. The final year of revenue data is being excluded from the R&C Profile output data, thus making it impossible to independently crosscheck the NPV calculation (as the originally reporting user, and then I, was trying to do).

The fix for this issue is relatively straightforward from a code perspective. I will file a PR that can go in after the current PR I have waiting on review (#347) is merged + a sequence of PRs that I have queued up to immediately follow it (softwareengineerprogrammer#52; softwareengineerprogrammer#53; possibly also softwareengineerprogrammer#51).

[keithtelliott]

@softwareengineerprogrammer I just stepped through your notes and the PR/commits. Looks reasonable - I appreciate your detailed and diligent work!