<jupyter_start><jupyter_text>*January 23, 2019***Financial Model Documentation:**\A Discussion of Model Parameters with Examples  Financial Modeling: Recommendations for mVSO* Ensure that mVSO contains the same financial calculations that up until now only live in an Excel financial model - use the discounted cash flow method    mVSO is already doing most of the work wrt the DCF method it is only missing discount factors**Because we are not exposing mVSO to the financial impacts of the solution we cannot ensure the benefits (or lack thereof) that mVSO is calculating will be congruent with those calculated in the excel financial model. This was not the intended use for mVSO.**Once the mVSO model is discounting cash flows it will automatically be capable of calculating NPV, IRR, payback as in any excel proforma thus allowing for mVSO to be the central source of the full solution and associated financial benefits/metrics.The remaining recommendation stem from the first:* Optimize for highest NPV - this will be the most straight forward, obvious financial Objective function that we can write. We can't write an Objective function for IRR so we cant optimize for IRR * Allow users to provide a hurdle rate/WACC/Cost of Equity and target IRR as a constraint* Do not optimize for least payback it is not a meaningful metric and will produce irrelevant results (poor IRR, low NPV) that will ultimately not be considered for investment  Financial Model Documentation and ProcessThe Discounted Cash Flow method is described in detail in this document with supporting examples and visualizations.The basis of the calculations in this document were gleaned from the following resources:https://corporatefinanceinstitute.com/resources/knowledge/valuation/dcf-formula-guide/https://corporatefinanceinstitute.com/resources/knowledge/valuation/net-present-value-npv/https://corporatefinanceinstitute.com/resources/knowledge/valuation/cash-flow-guide-ebitda-cf-fcf-fcff/https://corporatefinanceinstitute.com/resources/knowledge/valuation/free-cash-flow-to-equity-fcfe/https://corporatefinanceinstitute.com/resources/knowledge/finance/internal-rate-return-irr/https://corporatefinanceinstitute.com/resources/knowledge/finance/what-is-wacc-formula/https://hbr.org/2014/11/a-refresher-on-net-present-valuehttps://hbr.org/2012/07/do-you-know-your-cost-of-capitalThe following resources are from authoritative figures in the energy industry who have discussed the DCF/NPV methodhttps://www.seia.org/research-resources/valuation-solar-generation-assets \https://blog.aurorasolar.com/quantifying-the-value-of-a-solar-installation/ \https://www.nrel.gov/analysis/crest.htmlAll of these resources were used to establish a common framework for calculating cashflows and for understanding the nuances inherent in these calculations and concepts in finance and for common vernacular.Once the mechanics of the cash flow logic were established the python model was tested against the National Renewable Energy Laboratories (NREL) Cost of Renewable Energy Spreadsheet Tool (CREST). The goal here was to test the accuracy of the python DCF model to the CREST model WITHOUT any major alterations and by using the same parameters and definitions, calculations. The test was successful. By only rearranging a couple of terms I was able to reproduce the same results as the CREST model.This model doesnt include the tax credits yet as the main goal of this document is to illustrate the cash flow process for mVSO software. We can easily add incentives or other financial model parameters as we see fit.  Whats Covered NextIn the sections that follow I will defineThe model inputs* Technical Parameters* Economic Parameters* Financial ParametersIn the **Financial Model** Section I define and calculate the following model parameters* Revenue* OPEX* CAPEX/Equity/Debt* Depreciation* Debt Service* TaxesIn the **Cash Flow** section we use the model parameters above and calculate* EBITDA* EBTDA* EBIT* Free Cash Flow* Free Cash Flow to EquityIn the **Discounted Cash Flow** section I calculate the discount factors and use them to discount certain cashflowsIn the **IRR** section I calculate the running IRR for the pre-tax and after-tax cash flow to equityIn the **DataFrame/Table** the inputs and outputs are stored in tables and then displayedFinally a couple of familiar plots are generated showing cashflow and IRR over time.All of the tables and plots are exported to Excel.<jupyter_code>import requests
import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import datetime as dtm<jupyter_output><empty_output><jupyter_text>Technical ParametersThese are the technology specific parameters and/or constraints<jupyter_code>pv_capacity_kw = 1000
capacity_factor = 0.15
pv_degradation_rate = -0.005<jupyter_output><empty_output><jupyter_text>Economic ParametersThese describe the cost associated with a technology<jupyter_code>project_life = 20
pv_unit_cost_kw = 1800
om_rate = 6.5
om_escalation_rate = 0.016
inverter_repl_rate = 0
inverter_repl_year = 0
util_escalation_rate = 0.0
average_energy_rate = 0.1885<jupyter_output><empty_output><jupyter_text>Financial ParametersThese describe the financial modeling inputs<jupyter_code>percent_debt = 0.45
percent_equity = 1-percent_debt
cost_equity = .12
cost_of_debt = 0.07
corporate_tax_rate = 0.21
state_tax_rate = 0.085
inflation_rate = 0.0
bonus_depreciation = 1.0
itc_rate = 0.26
debt_term = 10
payments_per_year = 1
effective_tax_rate = corporate_tax_rate+(state_tax_rate*(1-corporate_tax_rate))
wacc = percent_debt*cost_of_debt*(1-effective_tax_rate) + percent_equity*cost_equity
years = list(range(project_life))<jupyter_output><empty_output><jupyter_text>Financial ModelIf you see `this` type of highlight I am refering to code.  GenerationIn lieu of leveraging a solar modeling tool like PVLib I am estimating solar generation via the capacity factor method.$$kW_{solar}*CF*8760$$$kW_{solar}$ = DC capacity of solar PV\$CF$ = Capacity factor\$8760$ = number hours per yearThe code snippet below produces a Pandas Series that is 21 elements long. The first year of generation is stored in element 1 of the Series. The Series is than populated with the ensuing years generation by degrading the PV generation stored in element 1 of the Series. The process described here is repeated for all other components in the cash flow sections unless otherwise noted.<jupyter_code>yr1_generation = pv_capacity_kw*capacity_factor*8760
pv_generation = pd.Series(index=years)
pv_generation[1] = yr1_generation 
for year in range(1, project_life):
    pv_generation[year+1] = pv_generation[year] * (1 + pv_degradation_rate)
pv_generation = pv_generation.fillna(value=0)<jupyter_output><empty_output><jupyter_text>Rate EscalationFor simplification I assume a flat rate that is escalated each year.<jupyter_code>rate = pd.Series(index=years)
rate[1] = average_energy_rate
for year in range(1, project_life):
    rate[year+1] = rate[year] * (1 + util_escalation_rate)
rate = rate.fillna(value=0)<jupyter_output><empty_output><jupyter_text>Revenue**Revenue** is all positive inflows of cash to the project from operation of the energy assets. It will contain at least one but possibly all of the following- Utility avoided cost (AVC)- Net energy metering credits- Ancillary service payments- etc.Cash Flow Model Parameter/s:`revenue`<jupyter_code>revenue = pv_generation*rate<jupyter_output><empty_output><jupyter_text>Operating Expenses**Operating Expenses (OPEX)** For a given project there are actually a number of operating expenses that need to be accounted for. This model (in its current state) accounts for fixed cost O&M expense as well a single inverter replacement that occurs at the replacement year, year 16 in this case. The replacement cost (`replacement`) and O&M expenses (`om`) are rolled into a model component called `opex`.As more OPEX line items are required they can be can be rolled up into the `opex` component. For instance if we wanted to add an annual software license expense line item, we can call it `software_license` and define its value however we want over the project lifetime and then add it along with `om` and `replacement` to `opex`.Cash Flow Model Parameter/s:`opex`<jupyter_code>#Replacement Cost
replacement_cost = inverter_repl_rate * pv_capacity_kw
replacement = pd.Series(index=years)
replacement[inverter_repl_year] = replacement_cost
for year in range(1, project_life):
    replacement[year+1] = replacement[year]
replacement = replacement.fillna(value=0)

# O&M Expenses (fixed O&M)
om_cost = om_rate*pv_capacity_kw
om = pd.Series(index=years)
om[1] = om_cost
for year in range(1, project_life):
    om[year+1] = om[year] * (1 + om_escalation_rate)
om = om.fillna(value=0)

opex = om + replacement<jupyter_output><empty_output><jupyter_text>Capital Expense, Debt/Equity Share of CAPEX**Capital Expenses (CAPEX)** - Power capacity based pricing, \\$/kW, for energy generating technologies and \\$/kWh of installed storage capacity**Debt/Equity Share of CAPEX**The share of equity and debt is calculated based on the debt to equity gearing ratio which is called `percent_debt` and `percent_equity` in this model. The equity portion of the investment is the amount left over after the debt share and represented in the model as a single dollar value in year 0.The debt portion of the investments shows up as interest and principal payments via the debt service calculations below.Cash Flow Model Parameters/s:`equity`\`debt share`\`capex`<jupyter_code># First calculate the total cost of the project
pv_cost=pv_capacity_kw*pv_unit_cost_kw

# Then split that total cost between the debt and equity according to the percent debt/equity inputs
debt_share = pv_cost*percent_debt
equity_share = pv_cost*percent_equity

# I do use equity in the model

equity = pd.Series(index=years)
equity[0] = equity_share
for year in range(1, project_life):
    equity[year+1] = equity[year] * 0
equity=equity.fillna(value=0)

# I actually do not use capex in the model but leaving it here for now
capex = pd.Series(index=years)
capex[0] = pv_cost
for year in range(1, project_life):
    capex[year+1] = capex[year] * 0
capex=capex.fillna(value=0)<jupyter_output><empty_output><jupyter_text>Depreciation**Depreciation**Federal: Modified Acceleration Cost Recovery System (MACRS) @ Federal corporate tax rate\State: 10 year straight line depreciation @ State tax rateThe 5 year MACRS depreciation schedule is used in this example so we can see it across multiple years, as opposed to 100% depreciation year one. Currently State depreciation is not considered but is a relatively simple addition to the model.The basis for the depreciation expense is the full project cost (`capex`) multiplied by one minus half of the current ITC rate.Cash Flow Model Parameter/s:`depreciation`<jupyter_code>basis = (1-0.5*itc_rate)*pv_cost
macrs_sched = pd.Series(index=years)
for year in range(1, project_life):
    macrs_sched[year+1] = macrs_sched[year] * 0
macrs_sched[1] = 0.2000
macrs_sched[2] = 0.3200
macrs_sched[3] = 0.1920
macrs_sched[4] = 0.1152
macrs_sched[5] = 0.1152
macrs_sched[6] = 0.0576
macrs_sched = macrs_sched*basis

depreciation = macrs_sched.fillna(value=0)<jupyter_output><empty_output><jupyter_text>Debt Service**Debt Service**When funded in whole or in part by debt this section of code will calculate principal and interest payments based on the amount financed (`percent_debt`), `cost_of_debt`, length of debt term (`debt_term`), number of payments per year (`payments_ per_year`).A standard amortization method is used here and is no different from using excel *pmt* and *ipmt* functions.From this we get interest payments and principal payments.Cash Flow Model Parameter/s:`interest`\`principal`<jupyter_code>def loan_calc(principal, rate, payment):
    """
    Generator that yields successive period payment details
    """
    amount = payment
    while principal > 0:
        period_interest = principal * rate
        principal_reduction = min(principal, amount - period_interest)
        principal = principal - principal_reduction
        yield principal_reduction, period_interest, principal
        
def amortization_table(interest_rate, project_life, loan_years, payments_year, principal):
    """
    Calculate the amortization schedule given the loan details


    :param interest_rate: The annual interest rate for this loan
    :param years: Number of years for the loan
    :param payments_year: Number of payments in a year
    :parma principal: Amount borrowed

    :return: 
        schedule: Amortization schedule as a pandas dataframe
    """
    #Calculate the fixed payment 
    period_rate = interest_rate/payments_year
    payment = -1 * np.pmt(period_rate, loan_years*payments_year, principal)

    #Generate the period details schedule

    df = pd.DataFrame(list(loan_calc(principal, period_rate, payment)), columns=["Principal", "Interest", "Balance"])
    df["Total Payment"] = df["Principal"] + df["Interest"]
    df["Cumulative Principal"] = df["Principal"].cumsum()
    
    index = np.arange(1, (project_life)*payments_year)
    df2 = pd.DataFrame(index=index) 
    df = pd.concat([df,df2], axis=1).fillna(value=0)
    new_row = pd.DataFrame({'Principal':0, 'Interest':0, 'Balance':0, 'Total Payment':0, 'Cumulative Principal':0}, index=[0])
    df = pd.concat([new_row,df]).reset_index(drop=True)
    df=df.round(2)
    return(df)<jupyter_output><empty_output><jupyter_text>In the cell below I call the function that calculates the principal and debt payments via the function `amortization_table`. I then create the `interest` and `principal` parameters which are used in the cash flows.<jupyter_code>debt_service_df = amortization_table(cost_of_debt, project_life, debt_term, payments_per_year, debt_share)

interest = debt_service_df['Interest']
principal = debt_service_df['Principal']<jupyter_output><empty_output><jupyter_text>Tax**Taxes**Taxable income is defined as `revenue - opex - interest - depreciation`.Taxes are calculated according to either the Federal and State taxes separately OR taxes may be calculated by using the Effective tax rate and we would report a single taxes line item. In this model the assumption is that State and Federal taxes will be considered separately.Taxable income for the state is calculated by taking the taxable income and multiplying it by the state tax rate. The state tax amount owed is deducted from the federal taxable income, effectively reducing the federal taxes owed. The federal taxable income is then multiplied by the corporate tax rate to find the federal tax amount owed (or saved).Cash Flow Model Parameter/s`fed_tax`\`state_tax`<jupyter_code># These are actually used in the Cash Flows below
taxable_income = revenue - opex - interest - depreciation

fed_taxable_income = taxable_income

state_taxable_income = taxable_income

state_tax = -state_taxable_income * state_tax_rate

_fed_tax = (fed_taxable_income + state_tax) * corporate_tax_rate

fed_tax = [-i for i in _fed_tax]<jupyter_output><empty_output><jupyter_text>Cash FlowsThis section describes how to arrive at different cash flow **EBITDA: Earnings Before Interest, Taxes, Depreciation and Amortization**aka **Gross Profit**CREST Terminology: **EBITDA (Operating Income)**    EBITDA = Gross Profit = Operating Income = Revenue - OPEX`ebitda = revenue - opex`**EBTDA:**CREST Terminology: **Operating Income After Interest**    EBTDA = Operating Income After Interest = Revenue - OPEX - Interest`ebtda = revenue - opex - interest`**EBIT: Earnings Before Interest and Taxes.**        EBIT = Revenue - OPEX - Depreciation    `ebit = revenue - interest - Depreciation`**EBTE: Earnings before Tax to Equity**    EBTE = Revenue - OPEX - Interest - Depreciation - Principal`ebte = revenue - opex - interest - principal `**Net EBTE: Net Earnings Before Taxes to Equity**CREST Terminology: **Net Pre Tax Cash Flow Equity**    Net EBTE = Revenue - OPEX - Depreciation - Interest - Principal`net_ebte = revenue - opex - interest - principal - equity`**FCF: Free Cash Flow**Note that the sign of taxes are negative so I am adding. The point is we want to subtract taxes at this level in the cash flows.    FCF = Revenue - OPEX - Interest + Fed Tax + State Tax`free_cash_flow = revenue - opex - interest + fed_tax + state_tax`**FCFE: Free Cash Flow to Equity** The so called *Levered Free Cash Flow*    FCFE = Revenue - OPEX - Interest - principal + Fed Tax + State Tax`free_cash_flow_equity = revenue - opex - interest - principal + fed_tax + state_tax`**NET FCFE** At this level we are adding the equity share of the investment to FCFECREST Terminology: **After Tax Cash Flow to Equity**    FCFE = Revenue - OPEX - Interest - principal + Fed Tax + State Tax - Equity`net_free_cash_flow_equity = revenue - opex - interest - principal + fed_tax + state_tax - equity`  Cash Flows<jupyter_code># Operating Income (CREST)
ebitda = revenue - opex

# Operating Income After Interest Expense (CREST)
ebtda = revenue - opex - interest

ebit = revenue - opex - depreciation

# Pre Tax Cash Flow Equity (CREST)
# Earnings Before Taxes to Equity
ebte = revenue - opex - interest - principal 

# Net Pre Tax Cash Flow Equity (CREST)
# Net Earnings Before Taxes to Equity
net_ebte = revenue - opex - interest - principal - equity

free_cash_flow = revenue - opex - interest + fed_tax + state_tax

free_cash_flow_equity = revenue - opex - interest - principal + fed_tax + state_tax

# After Tax Cash Flow to Equity (CREST)
net_free_cash_flow_equity = revenue - opex - interest - principal + fed_tax + state_tax - equity<jupyter_output><empty_output><jupyter_text>**ATTENTION**In the cash flows above we stop at ebitda in mVSO. By adding the remaining cashflows we have more flexibility in our financial optimizations.  Present Value Calculations - Discounted Cash FlowHaving calculated the cash flows in the section above we can now discount them to find the present value. We can then use those discounted cash flows to calculate the Net Present Value, IRR and Payback. Calculate the Discount Factors<jupyter_code>discount_factors = [(1/(1 + cost_equity)) ** i for i in range(0,project_life+1)]

discounted_after_tax_fcfe = discount_factors*net_free_cash_flow_equity

cumulative_after_tax_fcfe = net_free_cash_flow_equity.cumsum()
cumulative_discounted_after_tax_fcfe = discounted_after_tax_fcfe.cumsum()
irrs_net_ebte = [np.irr(net_ebte[0:i+1]) for i in range(0, project_life+1)]
irrs_net_fcfe = [np.irr(net_free_cash_flow_equity[0:i+1]) for i in range(0, project_life+1)]
irrs_discounted_after_tax_fcfe = [np.irr(discounted_after_tax_fcfe[0:i+1]) for i in range(0, project_life+1)]<jupyter_output><empty_output><jupyter_text>DataFrames/Tables<jupyter_code>tech_params = pd.DataFrame(data={'PV Capacity (kWdc)': pv_capacity_kw},
                    index=['Technical Parameters'])


econ_params = pd.DataFrame(data={'Project Lifetime (yrs)': project_life,
                                'PV Unit Cost ($/kW-dc)': pv_unit_cost_kw,
                                'PV O&M Rate ($/kW-yr)': om_rate,
                                'PV O&M Escalation Rate (%)': om_escalation_rate,
                                'Inverter Replacement Cost ($/kW)': inverter_repl_rate,
                                'Inverter Replacement Year': inverter_repl_year,
                                'Utility Escalation Rate (%/yr)': util_escalation_rate},
                    index=['Economic Parameters'])

fin_params = pd.DataFrame(data={'Percent Debt': percent_debt,
                               'Percent Equity': percent_equity,
                               'Cost of Debt': cost_of_debt,
                               'Cost of Equity': cost_equity,
                               'Corporate Tax Rate': corporate_tax_rate,
                               'State Tax Rate': state_tax_rate,
                               'Inflation Rate': inflation_rate,
                               'Investment Tax Credit Rate': itc_rate,
                               'Debt Term': debt_term,
                               'Payments per Year': payments_per_year,
                               'Weighted Average Cost of Capital': wacc,
                               'Effective Tax Rate': effective_tax_rate},
                         index=['Financial Parameters'])

fin_metrics = pd.DataFrame(data={'Pre Tax Equity IRR': irrs_net_ebte[-1] ,
                                 'After Tax Equity IRR': irrs_net_fcfe[-1],
                                ' Net Present Value': discounted_after_tax_fcfe.sum().round(0)},
                           index=['Financial Metrics'] )

proforma = pd.DataFrame(data={'Delivered Energy': pv_generation,
                              'Project Revenue': revenue,
                              'Operating Expense': opex,
                              'EBITDA': ebitda,
                              'Interest': interest,
                              'Cash from Operations After Interest': ebtda,
                              'Principal': principal,
                              'Net Pre-Tax Cash Flow to Equity': net_ebte,
                              'Running IRR Pre Tax': irrs_net_ebte,
                              'Depreciation': depreciation,
                             'Federal Taxable Income': fed_taxable_income,
                             'State Taxable Income': state_taxable_income,
                             'Federal Taxes': fed_tax,
                             'State Taxes': state_tax,
                             'After-Tax Cash Flow to Equity': free_cash_flow_equity,
                             'NetAfterTaxCashFlowtoEquity': net_free_cash_flow_equity,
                             'DiscountedAfterTaxCashFlowtoEquity': discounted_after_tax_fcfe,
                             'CumulativeDiscountedAfterTaxCashFlowtoEquity': cumulative_discounted_after_tax_fcfe,
                             'Running IRR After Tax': irrs_net_fcfe,
                             'CumulativeAfterTaxCashFlowtoEquity': cumulative_after_tax_fcfe})<jupyter_output><empty_output><jupyter_text>Calculate Payback<jupyter_code>final_full_year = proforma[proforma.CumulativeAfterTaxCashFlowtoEquity < 0].index.values.max()
fractional_yr =- proforma.CumulativeAfterTaxCashFlowtoEquity[final_full_year]/proforma.NetAfterTaxCashFlowtoEquity[final_full_year]
payback_period = final_full_year + fractional_yr
payback_period<jupyter_output><empty_output><jupyter_text>Display the Inputs and Outputs<jupyter_code>tech_params.T
econ_params.T
fin_params.T
fin_metrics.T
proforma
fig, ax = plt.subplots(2,1, figsize=(12,16))

ax[0].plot(proforma['CumulativeDiscountedAfterTaxCashFlowtoEquity'], label='Discounted Cumulative After Tax Cash Flow to Equity')
ax[0].plot(proforma['CumulativeAfterTaxCashFlowtoEquity'], label='Cumulative After Tax Cash Flow to Equity')
ax[0].bar(range(len(proforma)) , proforma['NetAfterTaxCashFlowtoEquity'], label='Net After Tax Cash Flow to Equity')
ax[0].bar(range(len(proforma)) , proforma['DiscountedAfterTaxCashFlowtoEquity'], label='Discounted After Tax Cash Flow to Equity')

ax[0].set_ylabel('Cash Flow ($)')
ax[0].set_xlabel('Project Year')
ax[0].grid(True)
ax[0].axhline(0, color='k', linewidth=0.5)
ax[0].set_xticks(np.arange(0, 21, step=1))
ax[0].set_yticks(np.arange(-2000000, 4000000, step=250000))

ax[0].legend()

ax[1].plot(proforma['Running IRR Pre Tax'], '--', label='Running IRR Pre Tax')
ax[1].plot(proforma['Running IRR After Tax'], '--', label='Running IRR After Tax')
ax[1].set_ylabel('Internal Rate of Return')
ax[1].grid(True)
ax[1].axhline(0, color='k', linewidth=0.5)
ax[1].set_yticks(np.arange(-1.0, .25, step=0.025))
ax[1].set_xticks(np.arange(0, 21, step=1))
ax[1].legend()

fig.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>Write Inputs and Outputs to Excel<jupyter_code>with pd.ExcelWriter('financial_model.xlsx') as writer:
    tech_params.T.to_excel(writer, sheet_name='Parameters', startrow=0, startcol=0)
    econ_params.T.to_excel(writer, sheet_name='Parameters', startrow=0, startcol=2)
    fin_params.T.to_excel(writer, sheet_name='Parameters', startrow=0, startcol=4)
    fin_metrics.T.to_excel(writer, sheet_name='Financial Metrics')
    proforma.to_excel(writer, sheet_name='Proforma')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08162009109934754, 'fraction_numerical': 0.011613115023185194, 'mean_word_length': 4.25895554596461, 'pattern_counts': {'":': 0, '<': 78, '<?xml version=': 0, '>': 78, 'https://': 11, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '423456', 'license': 'MIT', 'n_tokens_mistral': 7515, 'n_tokens_neox': 7041, 'n_words': 2386, 'path': 'Energy Project Life Cycle Analysis and Financial Modeling version 4 DRAFT FINAL.ipynb', 'repo_name': 'bgamboa1/energy_project_financial_model', 'size': '24369'}