Depreciable capital in NPV calculation

[yalinli2]

@yoelcortes I believe that currently in BioSTEAM, when calculating NPV, the depreciation charge is based on the depreciation schedule multiplied by the total direct cost (TDC).

However, in looking at the Humbird report (and other similar NREL reports), I think that fixed capital investment (FCI) is used instead of TDC, e.g., in Page 104 of the Humbird report:

For Year 1, the depreciation charge for the general plan is $47,822,041 at 14% (should be 14.29% for MACRS 7), for the steam plant is $2,473,484 at 3.75% for the steam plant, this means that the total depreciation basis is:
$47,822,041/14.29% + $2,473,484/3.75% = $400,613,464

And FCI is $400,600,000 as on Page 62

Although to be honest, I'd think TDC makes more sense to be used as the depreciation basis.

[yoelcortes]

@yalinli2,

Thanks for looking into this and posting the issue! I think there was some confusion in the TEA terms in BioSTEAM vs NREL reports so we'll need to fix minor details in the code.

TDC in the NREL report refers to the Total Direct Costs while TDC in BioSTEAM is the Total Depreciable Capital. The TDC in BioSTEAM should include a few indirect costs such as contingency, but not non-depreciable items like land, royalties, and start-up (based on the TEA methodology in Warren Seider's Product and Process Design Principles book).

If the NREL report considers the FCI as the depreciable capital, it is great it excludes land but it is odd that it includes the "Other costs (start-up, permits etc.)" row in the Total Indirect Costs section. I think this might be small mistake on their part.

I'd like to update the CellulosicEthanolTEA object so that the total depreciable capital (TDC) includes all the indirect costs except for "Other costs". But first, please let me know if this sounds right to you or if you have any more thoughts to add.

Thanks!

[yalinli2]

@yoelcortes ah I see. What you said make sense, although I can't say for sure if that were a mistake (my intuition wouldn't think permits, etc. are depreciable, but I'm no tax expert lol), is there anywhere in the textbook that specifically says those are not depreciable? Also would be good if we can clarify with NREL people.

Updating CellulosicEthanolTEA sounds good. Do we want to include some ways to calculate land and salvage values? Since they have related expenses upfront but also have some values at the end of the project. Thanks!

[yalinli2]

Another minor difference is that the loan in the NREL does not include the working capital, i.e., $240,368,078 in the last year of the construction is $400,613,464*60%, but I think BioSTEAM includes the 5% working capital when calculating the loan amount as well.

[yalinli2]

(Hopefully) final issue, I think in NREL's table (N-year project with M years of construction):
sum of loan payment = sum of loan interest (Year 1 to Year N) + loan principal

whereas in BioSTEAM's table:
sum of loan payment = sum of loan interest (Year 1-M to Year N) + loan principal

The Excel here might make it clearer, the "Example DCF" sheet has the cashflow table from BioSTEAM, whereas the "NREL" sheet has (not really cleaned-up) the Humbird cashflow table.

Let me know if I missed anything, thanks!

TEA comparison.xlsx

[yoelcortes]

@yalinli2, the textbook notes that royalties, patents/licensing, and land are not depreciable, but I'm not too sure about permits. It would be great to clarify with NREL.

Adding land and salvage values would be awesome and certainly welcomed, tho it's not a priority for me right now.

We can remove working capital from the loan (good catch!).

Loan payments can start year 1 of operation (another good catch!).

Let's leave this issue open until all these items are resolved.

Thanks a bunch!

[yalinli2]

All sounds good, thanks!

…

On Sun, Jan 7, 2024 at 8:06 PM Yoel ***@***.***> wrote: @yalinli2 <https://github.com/yalinli2>, the textbook notes that royalties, patents/licensing, and land are not depreciable, but I'm not too sure about permits. It would be great to clarify with NREL. Adding land and salvage values would be awesome and certainly welcomed, tho it's not a priority for me right now. We can remove working capital from the loan (good catch!). Loan payments can start year 1 of operation (another good catch!). Let's leave this issue open until all these items are resolved. Thanks a bunch! — Reply to this email directly, view it on GitHub <#180 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALV5VLIYK6VU5XJ7KRWQTOLYNNA7ZAVCNFSM6AAAAABBQTBPR2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOBQGI3DCOBSHA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[yoelcortes]

Done!

[yalinli2]

@yoelcortes thanks for fixing the working capital and loan payment starting time, but I think there are still bugs. I'm trying with a loan of $2.016MM for 10 years with an interest of 8%:
https://github.com/BioSTEAMDevelopmentGroup/biosteam/blob/52de51a23f1e546896b132c9f1fa049b4d1fa43c/biosteam/_tea.py#L752C16-L752C16

1. Now the loan principal array are all negative, I think it's because the loan revenue array also starts at i=1 and misses the initial positive value:
2. [solve_payment](https://github.com/BioSTEAMDevelopmentGroup/biosteam/blob/52de51a23f1e546896b132c9f1fa049b4d1fa43c/biosteam/_tea.py#L89) calculates the payment to be 0.3244789251501, but if I use
   

the payment (A) is 2.016*0.08*1.08^10/(1.08^10-1)=0.300443

Did I miss something? Thanks!

[yoelcortes]

@yalinli2,

Opps, OK! I fixed loan principal array (just had to start the iteration from 0). The solve_payment method erroneously assumed interest at the start of the year, so now it is fixed for the end of the year. Note that the method assumes loans start before operation and the interest should still be accumulated during the construction/start-up phase. I think it would be nice to use the formula and account for the additional interest during the construction phase. I'll try implement later today.

Click me for loan principal array test

>>> from biorefineries import cornstover as cs
>>> table = cs.tea.get_cashflow_table()
>>> table['Loan principal [MM$]']
2004   12.3
2005    106
2006    163
2007    154
2008    144
2009    133
2010    121
2011    108
2012   93.8
2013   78.8
2014   62.6
2015     45
2016   26.1
2017      0
2018      0
2019      0
2020      0
2021      0
2022      0
2023      0
2024      0
2025      0
2026      0
2027      0
2028      0
2029      0
2030      0
2031      0
2032      0
2033      0
2034      0
2035      0
2036      0
Name: Loan principal [MM$], dtype: float64

Click me for loan payment test

def final_loan_principal(payment, principal, interest, years):
    for iter in range(years):
        principal += principal * interest - payment
    return principal

def solve_payment(payment, loan, interest, years):
    principal = initial_loan_principal(loan, interest)
    payment = flx.aitken_secant(final_loan_principal,
                                payment, payment+10., 1., 10.,
                                args=(principal, interest, years),
                                maxiter=200, checkiter=False)
    return payment

import flexsolve as flx

def initial_loan_principal(loan, interest):
    principal = 0
    k = 1. + interest
    for i in loan:
        principal *= k # This line was now comes first
        principal += i
    return principal
    
solve_payment(2.016e6 / 10 * 1.08, [2.016e6], 0.08, 10)

Returns

300443.4492136282

Thanks so much for finding this and noting all the details!

[yalinli2]

Yep the loan principal looks good now. Thanks @yoelcortes !

For the interest, based on NREL's cashflow table I think they assume that the equity will cover the interest during construction, so the principal won't increase in that period.

Then another thing is that I think that
sum of all loan payment from Year 1 to Year N = loan principal + sum of all loan interest payment from Year 1 to Year N

I don't think it's the case with current BioSTEAM's cashflow table?

[yoelcortes]

@yalinli2, OK! I'll make it an option whether or not to assume interest during construction years and default it to assume no interest. This way we can have consistent calculations with NREL.

I'll make sure that sum of all loan payment from Year 1 to Year N = loan principal + sum of all loan interest payment from Year 1 to Year N. I think the issue has to do with the missing interest in construction years in the cashflow table.

Thanks,

[yoelcortes]

@yalinli2.

Looking at the excel file you uploaded, NREL actually accounts for interest during construction starting from the first year of construction. So I made the option for including interest from construction years to be true by default. I believe everything adds up now.

from biorefineries import cornstover as cs
table = cs.tea.get_cashflow_table()
index = ['Loan [MM$]', 'Loan interest payment [MM$]', 'Loan payment [MM$]', 'Loan principal [MM$]']
table[index]
      Loan [MM$]  Loan interest payment [MM$]  Loan payment [MM$]  Loan principal [MM$]
2004        11.4                        0.912                   0                  12.3
2005        85.5                         7.82                   0                   106
2006        45.6                         12.1                   0                   163
2007           0                         13.1                24.3                   152
2008           0                         12.2                24.3                   140
2009           0                         11.2                24.3                   127
2010           0                         10.1                24.3                   112
2011           0                            9                24.3                  97.1
2012           0                         7.77                24.3                  80.6
2013           0                         6.45                24.3                  62.7
2014           0                         5.02                24.3                  43.4
2015           0                         3.47                24.3                  22.5
2016           0                          1.8                24.3              6.15e-14
2017           0                            0                   0                     0
2018           0                            0                   0                     0
2019           0                            0                   0                     0
2020           0                            0                   0                     0
2021           0                            0                   0                     0
2022           0                            0                   0                     0
2023           0                            0                   0                     0
2024           0                            0                   0                     0
2025           0                            0                   0                     0
2026           0                            0                   0                     0
2027           0                            0                   0                     0
2028           0                            0                   0                     0
2029           0                            0                   0                     0
2030           0                            0                   0                     0
2031           0                            0                   0                     0
2032           0                            0                   0                     0
2033           0                            0                   0                     0
2034           0                            0                   0                     0
2035           0                            0                   0                     0
2036           0                            0                   0                     0

[yalinli2]

@yoelcortes hmmm not really?

1. NREL did include interest during construction, but their assumption is that equity will pay off those interest, so the total amount of loan that they need to pay off at the start of the project is still the same amount as if no interest were accumulated. I.e., $19,229,446 for both Year 0 and Year 1
   

2. I think the math still doesn't work when interest_during_construction is False?

from biorefineries import cornstover as cs
table = cs.tea.get_cashflow_table()
index = ['Loan [MM$]', 'Loan interest payment [MM$]', 'Loan payment [MM$]', 'Loan principal [MM$]']
table[index].sum()[0:2].sum()
243.31277833207244

table[index].sum()[2] # this checks out
243.31277833207238

cs.tea.interest_during_construction = False
table2 = cs.tea.get_cashflow_table()

table2[index].sum()[0:2].sum()
257.23823854093564

table2[index].sum()[2] # this doesn't work
212.2746275993616

[yoelcortes]

@yalinli2,

I am not following your assertion... The interest is always 8% of the previous year loan principal regardless. If no interest was accumulated in years -2 and -1, the loan principal would be 240368078 - (1538356 + 13076023) -> 225753699. [EDIT: this is not exact so I removed this] Assuming interest is accumulated, the loan principal is 240368078.

def solve_payment(loan_principal, interest, years):
    f = 1 + interest
    fn = f ** years
    return loan_principal * interest * fn / (fn - 1)

loan_principal_with_interest = 240368078
print('with interest', solve_payment(loan_principal_with_interest , 0.08, 10))

with interest 35821931.76343873 # This is what the loan payment is

Could you help me with where is the misunderstanding?

Thanks for checking the cashflow table. The cashflow table has redundancies and I forgot to correct all column accordingly, but the actual cash flow in the NPV computation is correct.

from biorefineries import cornstover as cs
cs.tea.interest_during_construction = True
table = cs.tea.get_cashflow_table()
index = ['Loan [MM$]', 'Loan interest payment [MM$]', 'Loan payment [MM$]', 'Loan principal [MM$]']
print(table[index].sum()[0:2].sum())
print(table[index].sum()[2]) 
cs.tea.interest_during_construction = False
table2 = cs.tea.get_cashflow_table()
print(table2[index].sum()[0:2].sum())
print(table2[index].sum()[2]) 

Return values:

243.31277833207238
243.31277833207244
212.27462759936103
212.27462759936083

[yalinli2]

@yoelcortes so NREL's total loan amount is 60% of the fixed capital investment = 60%*$400,600,000 = $240,360,000, this is the number in Year 0 of their Loan Principal

the interests of $1,538,356, $13,076,023, and $19,229,446 accumulated in Years -2, -1, and 0 were not added on top of the $240,360,000, this is what I meant when I noted that equity (or whatever cash they have on hand) will pay off those interests.

However, the discrepancy I'm seeing now is that for NREL's cashflow table,
sum of all loan payment from Year 1 to Year N = loan principal + sum of all loan interest payment from Year 1 to Year N

but for your table (note it's Year -2 instead of Year 1 for loan interest payment),
sum of all loan payment from Year 1 to Year N = loan principal + sum of all loan interest payment from Year -2 to Year N

is this clearer? I can jump on a quick call tonight/tomorrow if it helps, thank you!

[yoelcortes]

OK! Thanks for the clarification. It's all clear now. Now it does not include interest during construction by default and made sure to push my commits.

I'll fix tests soon, once I merge some new features for liquid extraction and distillation. If you have the time, you could include some of the code created in this thread as tests (whatever you find important). Otherwise, no worries!

Hopefully this resolves everything, please double check and feel free to close the issue.

Woops, one last thing I need to do: add back the interest for years before the operation (it still need to be payed by equity).

Thanks,

[yoelcortes]

@yalinli2,

Done! The loan interest went down because equity payed for the years of construction.

from biorefineries import cornstover as cs
table = cs.tea.get_cashflow_table()
index = ['Loan [MM$]', 'Loan interest payment [MM$]', 'Loan payment [MM$]', 'Loan principal [MM$]']
table[index]
      Loan [MM$]  Loan interest payment [MM$]  Loan payment [MM$]  Loan principal [MM$]
2004        11.4                        0.912                   0                  11.4
2005        85.5                         6.84                   0                  96.9
2006        45.6                         3.65                   0                   142
2007           0                         11.4                21.2                   133
2008           0                         10.6                21.2                   122
2009           0                         9.76                21.2                   111
2010           0                         8.84                21.2                  98.1
2011           0                         7.85                21.2                  84.8
2012           0                         6.78                21.2                  70.3
2013           0                         5.62                21.2                  54.7
2014           0                         4.38                21.2                  37.9
2015           0                         3.03                21.2                  19.7
2016           0                         1.57                21.2              8.89e-14
2017           0                            0                   0                     0
2018           0                            0                   0                     0
2019           0                            0                   0                     0
2020           0                            0                   0                     0
2021           0                            0                   0                     0
2022           0                            0                   0                     0
2023           0                            0                   0                     0
2024           0                            0                   0                     0
2025           0                            0                   0                     0
2026           0                            0                   0                     0
2027           0                            0                   0                     0
2028           0                            0                   0                     0
2029           0                            0                   0                     0
2030           0                            0                   0                     0
2031           0                            0                   0                     0
2032           0                            0                   0                     0
2033           0                            0                   0                     0
2034           0                            0                   0                     0
2035           0                            0                   0                     0
2036           0                            0                   0                     0

Hopefully this resolves everything, please double check and feel free to close the issue.

Thanks!

[yalinli2]

@yoelcortes loan & interest look good! But unfortunately another inconsistency with NREL's table. There's a line of "Losses Forward" in their table, which basically is min(0, net revenue+losses from previous years). This allows them to include losses in previous years in calculating the net revenue for a certain year, so that their tax can be reduced if they lose money in earlier years.

For NREL's table, net revenue = sales - production cost - loan payment - depreciation charge, which I think is net earnings in BioSTEAM's table plus the tax. But note that the current calculated tax in some years is higher than NREL's calculated tax because they forward the losses.

I updated the Excel I'm using as the comparison (not NREL, but I did the calculation based on NREL's method), this way you can click through the formula, I also calculated the differences between the table I calculated in Excel vs. BioSTEAM's table. Thanks!

TEA comparison_V02.xlsx

[yoelcortes]

@yalinli2, this is an excellent point and can certainly be done. Pushing loses forward will help reduce the total tax paid. I should find some time this week to complete.

Thanks,

[yoelcortes]

@yalinli2,

Done! I added an extra column to show the earnings that are actually taxed. It is possible to add the forwarded losses in the get_cashflow_table method, but it is not necessary for the calculation. If you are interested in adding it, that contribution would be welcomed.

from biorefineries import cornstover as cs
cs.ethanol.price = 0.7198608114634679
table = cs.tea.get_cashflow_table()
index = ['Taxed earnings [MM$]', 'Tax [MM$]', 'Net earnings [MM$]']
table[index]
      Taxed earnings [MM$]  Tax [MM$]  Net earnings [MM$]
2004                     0          0                   0
2005                     0          0                   0
2006                     0          0                   0
2007                     0          0               -29.4
2008                     0          0               -51.3
2009                     0          0                 -28
2010                     0          0               -11.3
2011                     0          0               0.618
2012                     0          0               0.651
2013                     0          0               0.618
2014                     0          0                15.5
2015                     0          0                30.4
2016                     0          0                30.4
2017                  9.96       3.49                48.2
2018                  51.7       18.1                33.6
2019                  51.7       18.1                33.6
2020                  51.7       18.1                33.6
2021                  51.7       18.1                33.6
2022                  51.7       18.1                33.6
2023                  51.7       18.1                33.6
2024                  51.7       18.1                33.6
2025                  51.7       18.1                33.6
2026                  51.7       18.1                33.6
2027                  51.7       18.1                33.6
2028                  51.7       18.1                33.6
2029                  51.7       18.1                33.6
2030                  51.7       18.1                33.6
2031                  51.7       18.1                33.6
2032                  51.7       18.1                33.6
2033                  51.7       18.1                33.6
2034                  51.7       18.1                33.6
2035                  51.7       18.1                33.6
2036                  51.7       18.1                33.6

Thanks!

[yalinli2]

Great thanks @yoelcortes so much! One LAST (🤞 ) thing to note, I think we need to subtract the paid interest from NPV when pay_interest_during_construction is False (i.e., when we use equity/cash to pay the loan interest accumulated construction). I added that in the npv branch and opened a PR: #182

Please help to check if I did it correctly, esp. on if the NPV is calculated correctly considering the discount factor. I will add a test on QSDsan (since I used some units/settings in QSDsan for the test I'm using now) regarding the NPV calculation, thanks!

from biorefineries import cornstover as cs
cs.ethanol.price = 0.7198608114634679
table = cs.tea.get_cashflow_table()
index = ['Taxed earnings [MM$]', 'Forwarded losses [MM$]', 'Tax [MM$]', 'Net earnings [MM$]']
table[index]
      Taxed earnings [MM$]  Forwarded losses [MM$]  Tax [MM$]  Net earnings [MM$]
2004                     0                       0          0                   0
2005                     0                       0          0                   0
2006                     0                       0          0                   0
2007                     0                       0          0               -29.4
2008                     0                   -29.4          0               -51.3
2009                     0                   -80.8          0                 -28
2010                     0                    -109          0               -11.3
2011                     0                    -120          0               0.618
2012                     0                    -119          0               0.651
2013                     0                    -119          0               0.618
2014                     0                    -118          0                15.5
2015                     0                    -103          0                30.4
2016                     0                   -72.1          0                30.4
2017                  9.96                   -41.7       3.49                48.2
2018                  51.7                       0       18.1                33.6
2019                  51.7                       0       18.1                33.6
2020                  51.7                       0       18.1                33.6
2021                  51.7                       0       18.1                33.6
2022                  51.7                       0       18.1                33.6
2023                  51.7                       0       18.1                33.6
2024                  51.7                       0       18.1                33.6
2025                  51.7                       0       18.1                33.6
2026                  51.7                       0       18.1                33.6
2027                  51.7                       0       18.1                33.6
2028                  51.7                       0       18.1                33.6
2029                  51.7                       0       18.1                33.6
2030                  51.7                       0       18.1                33.6
2031                  51.7                       0       18.1                33.6
2032                  51.7                       0       18.1                33.6
2033                  51.7                       0       18.1                33.6
2034                  51.7                       0       18.1                33.6
2035                  51.7                       0       18.1                33.6
2036                  51.7                       0       18.1                33.6

[yalinli2]

Awesome, thanks for merging the changes!

However I was confused by why the last entry of cumulative NPV in the table does not match the NPV attribute, was I using it correctly?

from biorefineries import cornstover as cs
cs.ethanol.price = 0.7198608114634679
table = cs.tea.get_cashflow_table()
print(table['Cumulative NPV [MM$]'].iloc[-1]*1e6)
print(cs.tea.NPV)
31341873.413599778
32723703.907449506

Also a side note that the losses forwarded array is off by one year. Currently for Year N it's showing the loss that will be forwarded into Year N+1, not the loss that has been forwarded from Year N-1 (how NREL tabulates it). That's what I was doing with the following two lines

forwarded_losses[1:] = forwarded_losses[0:-1] # Forwarding to the next year
forwarded_losses[0] = 0

but this does not affect any other results

I'll close this issue when I add the test in QSDsan

[yoelcortes]

My bad, I did not realize the losses forwarded array was off by a year (I'll fix it up). Looks like we still got some work to do:

• Fix NPV property
• Add tests for solve_price, solve_IRR, NPV, and get_cashflow_table to make sure everything is consistent

I made a pull request, but tests will need to be added.

[yalinli2]

Ops I added test in #183 but you might want to modify, also attaching the same Excel sheet here in case it's helpful, feel free to close this issue when you think it's ready!
TEA comparison_V03.xlsx