def test_broadcast_decimal(self):
# Use almost equal because precision is tested in the explicit tests, this test is to ensure
# broadcast with Decimal is not broken.
assert_almost_equal(np.ipmt(Decimal('0.1') / Decimal('12'), list(range(5)), Decimal('24'), Decimal('2000')),
[Decimal('-17.29165168'), Decimal('-16.66666667'), Decimal('-16.03647345'),
Decimal('-15.40102862'), Decimal('-14.76028842')], 4)
assert_almost_equal(np.ppmt(Decimal('0.1') / Decimal('12'), list(range(5)), Decimal('24'), Decimal('2000')),
[Decimal('-74.998201'), Decimal('-75.62318601'), Decimal('-76.25337923'),
Decimal('-76.88882405'), Decimal('-77.52956425')], 4)
assert_almost_equal(np.ppmt(Decimal('0.1') / Decimal('12'), list(range(5)), Decimal('24'), Decimal('2000'),
Decimal('0'), [Decimal('0'), Decimal('0'), Decimal('1'), 'end', 'begin']),
[Decimal('-74.998201'), Decimal('-75.62318601'), Decimal('-75.62318601'),
Decimal('-76.88882405'), Decimal('-76.88882405')], 4)
def test_broadcast(self):
assert_almost_equal(np.nper(0.075,-2000,0,100000.,[0,1]),
[ 21.5449442 , 20.76156441], 4)
assert_almost_equal(np.ipmt(0.1/12,list(range(5)), 24, 2000),
[-17.29165168, -16.66666667, -16.03647345,
-15.40102862, -14.76028842], 4)
assert_almost_equal(np.ppmt(0.1/12,list(range(5)), 24, 2000),
[-74.998201 , -75.62318601, -76.25337923,
-76.88882405, -77.52956425], 4)
assert_almost_equal(np.ppmt(0.1/12,list(range(5)), 24, 2000, 0,
[0,0,1,'end','begin']),
[-74.998201 , -75.62318601, -75.62318601,
-76.88882405, -76.88882405], 4)
def eq_subvention(montant, duree, taux):
""" Calcule l' "équivalent subvention" par rapport à un taux 0%
pour chacune des différentes combinaisons d'hypothèses possibles"""
# RAZ des résultats
equivalent_subvention = np.zeros((len(montant), len(duree), len(taux)))
part_subventionee = np.zeros((len(montant), len(duree), len(taux)))
# Calcule de l' "équivalent subvention"
for i in range(len(montant)):
for j in range(len(duree)):
# Périodicité des remboursements
per = np.arange(duree[j]) + 1
for k in range(len(taux)):
# Calcule l'échancier des intérêts perçus
echeancier_interets = -np.ipmt(taux[k], per, duree[j], montant[i])
# Calcule et enregistre l' "équivalent subvention" comparé
# à un taux 0 pour le jeu d'hypothèses considéré, les flux
# d'intérêt étant actualisés à 4%
equivalent_subvention[i, j, k] = np.npv(0.04, echeancier_interets)
# ou alternativemen, sans actualiser:
# equivalent_subvention[i,j,k]=np.sum(echeancier_interets)
part_subventionee[i, j, k] = (equivalent_subvention[i, j, k] / montant[i]) * 100
return equivalent_subvention, part_subventionee
def test_when(self):
# begin
assert_almost_equal(np.rate(10, 20, -3500, 10000, 1), np.rate(10, 20, -3500, 10000, "begin"), 4)
# end
assert_almost_equal(np.rate(10, 20, -3500, 10000), np.rate(10, 20, -3500, 10000, "end"), 4)
assert_almost_equal(np.rate(10, 20, -3500, 10000, 0), np.rate(10, 20, -3500, 10000, "end"), 4)
# begin
assert_almost_equal(np.pv(0.07, 20, 12000, 0, 1), np.pv(0.07, 20, 12000, 0, "begin"), 2)
# end
assert_almost_equal(np.pv(0.07, 20, 12000, 0), np.pv(0.07, 20, 12000, 0, "end"), 2)
assert_almost_equal(np.pv(0.07, 20, 12000, 0, 0), np.pv(0.07, 20, 12000, 0, "end"), 2)
# begin
assert_almost_equal(np.fv(0.075, 20, -2000, 0, 1), np.fv(0.075, 20, -2000, 0, "begin"), 4)
# end
assert_almost_equal(np.fv(0.075, 20, -2000, 0), np.fv(0.075, 20, -2000, 0, "end"), 4)
assert_almost_equal(np.fv(0.075, 20, -2000, 0, 0), np.fv(0.075, 20, -2000, 0, "end"), 4)
# begin
assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000.0, 0, 1), np.pmt(0.08 / 12, 5 * 12, 15000.0, 0, "begin"), 4)
# end
assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000.0, 0), np.pmt(0.08 / 12, 5 * 12, 15000.0, 0, "end"), 4)
assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000.0, 0, 0), np.pmt(0.08 / 12, 5 * 12, 15000.0, 0, "end"), 4)
# begin
assert_almost_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 1), np.ppmt(0.1 / 12, 1, 60, 55000, 0, "begin"), 4)
# end
assert_almost_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0), np.ppmt(0.1 / 12, 1, 60, 55000, 0, "end"), 4)
assert_almost_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 0), np.ppmt(0.1 / 12, 1, 60, 55000, 0, "end"), 4)
# begin
assert_almost_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 1), np.ipmt(0.1 / 12, 1, 24, 2000, 0, "begin"), 4)
# end
assert_almost_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0), np.ipmt(0.1 / 12, 1, 24, 2000, 0, "end"), 4)
assert_almost_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 0), np.ipmt(0.1 / 12, 1, 24, 2000, 0, "end"), 4)
# begin
assert_almost_equal(np.nper(0.075, -2000, 0, 100000.0, 1), np.nper(0.075, -2000, 0, 100000.0, "begin"), 4)
# end
assert_almost_equal(np.nper(0.075, -2000, 0, 100000.0), np.nper(0.075, -2000, 0, 100000.0, "end"), 4)
assert_almost_equal(np.nper(0.075, -2000, 0, 100000.0, 0), np.nper(0.075, -2000, 0, 100000.0, "end"), 4)
def test_ipmt_decimal(self):
result = np.ipmt(Decimal('0.1') / Decimal('12'), 1, 24, 2000)
assert_equal(result.flat[0], Decimal('-16.66666666666666666666666667'))
def test_decimal_with_when(self):
"""Test that decimals are still supported if the when argument is passed"""
# begin
assert_equal(np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000'), Decimal('1')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000'), 'begin'))
# end
assert_equal(np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000'), 'end'))
assert_equal(np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000'), Decimal('0')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'), Decimal('10000'), 'end'))
# begin
assert_equal(np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0'), Decimal('1')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0'), 'begin'))
# end
assert_equal(np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0'), 'end'))
assert_equal(np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0'), Decimal('0')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'), Decimal('0'), 'end'))
# begin
assert_equal(np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0'), Decimal('1')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0'), 'begin'))
# end
assert_equal(np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0'), 'end'))
assert_equal(np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0'), Decimal('0')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'), Decimal('0'), 'end'))
# begin
assert_equal(np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0'), Decimal('1')),
np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0'), 'begin'))
# end
assert_equal(np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0')),
np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0'), 'end'))
assert_equal(np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0'), Decimal('0')),
np.pmt(Decimal('0.08') / Decimal('12'), Decimal('5') * Decimal('12'), Decimal('15000.'),
Decimal('0'), 'end'))
# begin
assert_equal(np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0'), Decimal('1')),
np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0'), 'begin'))
# end
assert_equal(np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0')),
np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0'), 'end'))
assert_equal(np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0'), Decimal('0')),
np.ppmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'), Decimal('55000'),
Decimal('0'), 'end'))
# begin
assert_equal(np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0'), Decimal('1')).flat[0],
np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0'), 'begin').flat[0])
# end
assert_equal(np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0')).flat[0],
np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0'), 'end').flat[0])
assert_equal(np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0'), Decimal('0')).flat[0],
np.ipmt(Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'), Decimal('2000'),
Decimal('0'), 'end').flat[0])
df = pd.DataFrame(index=date_range,columns=['Payment', 'Principal', 'Interest', 'Addl_Payment', 'Balance'], dtype='float')
df.reset_index(inplace=True)
df.index += 1
df.index.name = "Period"
print(df)
# %% [markdown]
# calculate the payment, Principal, Interest, Addl_Principal columns
# %%
df['Payment'] = Principal * (Monthly_interest / (1 - (1 + Monthly_interest)**(-(Years*Payments_Year))))
df["Principal"] = -1 * np.ppmt(Monthly_interest, df.index, Years*Payments_Year, Principal)
df["Interest"] = -1 * np.ipmt(Monthly_interest, df.index, Years*Payments_Year, Principal)
df["Addl_Payment"] = Addl_Payment
df['Cumulative_Principal'] = (df['Principal'] + df['Addl_Payment']).cumsum()
df['Cumulative_Interest'] = (df['Interest']).cumsum()
df['Balance'] = Principal - df['Cumulative_Principal']
df['Balance'] = df['Balance'].clip(0)
df = df.round(2)
pd.set_option("display.max_rows", None, "display.max_columns", None)
print(df)
# Master Output [Direct Loan]
outData["levelCostDirect"] = Rate_Levelized_Direct
outData["costPanelDirect"] = abs(NPVLoanDirect / numberPanels)
outData["cost10WPanelDirect"] = (
float(outData["costPanelDirect"]) / panelSize) * 10
# NCREBs Financing
ncrebsRate = float(inputDict.get("NCREBRate", 4.060)) / 100
ncrebBorrowingRate = 1.1 * ncrebsRate
ncrebPaymentPeriods = 44
ncrebCostToCustomer = []
# TODO ASAP: FIX ARRAY OFFSETS START 0
for i in range(1, len(outData["allYearGenerationMWh"]) + 1):
coopLoanPayment = 2 * pmt(ncrebBorrowingRate / 2.0, ncrebPaymentPeriods, outData[
"totalCost"]) if i <= ncrebPaymentPeriods / 2 else 0
ncrebsCredit = -0.7 * (ipmt(ncrebsRate / 2, 2 * i - 1, ncrebPaymentPeriods, outData["totalCost"])
+ ipmt(ncrebsRate / 2, 2 * i, ncrebPaymentPeriods, outData["totalCost"])) if i <= ncrebPaymentPeriods / 2 else 0
financingCost = ncrebsCredit + coopLoanPayment
omCost = OMInsuranceETCDirect[i - 1]
netCoopPayments = financingCost + omCost
distrAdder = distAdderDirect[i - 1]
costToCustomer = netCoopPayments + distrAdder
ncrebCostToCustomer.append(costToCustomer)
NPVLoanNCREB = npv(
float(inputDict.get("discRate", 0)) / 100, [0, 0] + ncrebCostToCustomer)
Rate_Levelized_NCREB = -NPVLoanNCREB / NPVallYearGenerationMWh
outData["levelCostNCREB"] = Rate_Levelized_NCREB
outData["costPanelNCREB"] = abs(NPVLoanNCREB / numberPanels)
outData["cost10WPanelNCREB"] = (
float(outData["costPanelNCREB"]) / panelSize) * 10
def intereses_restantes(serie):
intereses = sum([np.ipmt(serie.ope_tasa/100, cuota, int(serie.ope_cant_cuo), -serie.ope_monto_origen_pes) for cuota in range(int(serie["ope_cuotas_pagadas"]) + 1, int(serie["ope_cant_cuo"]) + 1)])
return np.ceil(intereses)
def mortgage_Payoff_Table(Interest_Rate,
Years,
Payments_Year,
Principal,
Addl_Princ,
start_date,
per=None):
pmt = np.pmt(rate=Interest_Rate / Payments_Year,
nper=Years * Payments_Year,
pv=Principal)
#print (pmt)
#ipmt = np.ipmt(Interest_Rate/Payments_Year, per, Years*Payments_Year, Principal)
#ppmt = np.ppmt(Interest_Rate/Payments_Year, per, Years*Payments_Year, Principal)
#print(ipmt, ppmt)
rng = pd.date_range(start_date, periods=Years * Payments_Year, freq='MS')
rng.name = "Payment_Date"
df = pd.DataFrame(
index=rng,
columns=['Payment', 'Principal', 'Interest', 'Addl_Principal'],
dtype='float')
df.reset_index(inplace=True)
df.index += 1
df.index.name = "Period"
df["Payment"] = np.pmt(Interest_Rate / Payments_Year,
Years * Payments_Year, Principal)
df["Principal"] = np.ppmt(Interest_Rate / Payments_Year, df.index,
Years * Payments_Year, Principal)
df["Interest"] = np.ipmt(Interest_Rate / Payments_Year, df.index,
Years * Payments_Year, Principal)
df["Addl_Principal"] = Addl_Princ
df["Cumulative_Principal"] = (df["Principal"] +
df["Addl_Principal"]).cumsum().clip(
lower=-Principal)
df['Curr_Balance'] = Principal + df["Cumulative_Principal"]
df = df.round(2)
try:
last_payment = df.query("Curr_Balance <= 0")["Curr_Balance"].idxmax(
axis=1, skipna=True)
except ValueError:
last_payment = df.last_valid_index()
df.ix[last_payment, 'Principal'] = -df.ix[last_payment - 1, 'Curr_Balance']
df = df.ix[0:last_payment]
df.ix[last_payment,
"Principal"] = -(df.ix[last_payment - 1, "Curr_Balance"])
df.ix[last_payment, 'Payment'] = df.ix[last_payment,
['Principal', 'Interest']].sum()
df.ix[last_payment, "Addl_Principal"] = 0
payoff_date = df["Payment_Date"].iloc[-1]
stats = pd.Series(
[
payoff_date, Interest_Rate, Years, Principal, pmt, Addl_Princ,
df["Interest"].sum()
],
index=[
"Payoff Date", "Interest Rate", "Years", "Principal", "Payment",
"Additional Payment", "Total Interest"
],
)
return df, stats
def test_decimal_with_when(self):
"""Test that decimals are still supported if the when argument is passed"""
# begin
assert_equal(
np.rate(
Decimal("10"),
Decimal("20"),
Decimal("-3500"),
Decimal("10000"),
Decimal("1"),
),
np.rate(
Decimal("10"),
Decimal("20"),
Decimal("-3500"),
Decimal("10000"),
"begin",
),
)
# end
assert_equal(
np.rate(Decimal("10"), Decimal("20"), Decimal("-3500"),
Decimal("10000")),
np.rate(Decimal("10"), Decimal("20"), Decimal("-3500"),
Decimal("10000"), "end"),
)
assert_equal(
np.rate(
Decimal("10"),
Decimal("20"),
Decimal("-3500"),
Decimal("10000"),
Decimal("0"),
),
np.rate(Decimal("10"), Decimal("20"), Decimal("-3500"),
Decimal("10000"), "end"),
)
# begin
assert_equal(
np.pv(
Decimal("0.07"),
Decimal("20"),
Decimal("12000"),
Decimal("0"),
Decimal("1"),
),
np.pv(Decimal("0.07"), Decimal("20"), Decimal("12000"),
Decimal("0"), "begin"),
)
# end
assert_equal(
np.pv(Decimal("0.07"), Decimal("20"), Decimal("12000"),
Decimal("0")),
np.pv(Decimal("0.07"), Decimal("20"), Decimal("12000"),
Decimal("0"), "end"),
)
assert_equal(
np.pv(
Decimal("0.07"),
Decimal("20"),
Decimal("12000"),
Decimal("0"),
Decimal("0"),
),
np.pv(Decimal("0.07"), Decimal("20"), Decimal("12000"),
Decimal("0"), "end"),
)
# begin
assert_equal(
np.fv(
Decimal("0.075"),
Decimal("20"),
Decimal("-2000"),
Decimal("0"),
Decimal("1"),
),
np.fv(Decimal("0.075"), Decimal("20"), Decimal("-2000"),
Decimal("0"), "begin"),
)
# end
assert_equal(
np.fv(Decimal("0.075"), Decimal("20"), Decimal("-2000"),
Decimal("0")),
np.fv(Decimal("0.075"), Decimal("20"), Decimal("-2000"),
Decimal("0"), "end"),
)
assert_equal(
np.fv(
Decimal("0.075"),
Decimal("20"),
Decimal("-2000"),
Decimal("0"),
Decimal("0"),
),
np.fv(Decimal("0.075"), Decimal("20"), Decimal("-2000"),
Decimal("0"), "end"),
)
# begin
assert_equal(
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
Decimal("1"),
),
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
"begin",
),
)
# end
assert_equal(
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
),
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
"end",
),
)
assert_equal(
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
Decimal("0"),
),
np.pmt(
Decimal("0.08") / Decimal("12"),
Decimal("5") * Decimal("12"),
Decimal("15000."),
Decimal("0"),
"end",
),
)
# begin
assert_equal(
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
Decimal("1"),
),
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
"begin",
),
)
# end
assert_equal(
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
),
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
"end",
),
)
assert_equal(
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
Decimal("0"),
),
np.ppmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("60"),
Decimal("55000"),
Decimal("0"),
"end",
),
)
# begin
assert_equal(
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
Decimal("1"),
).flat[0],
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
"begin",
).flat[0],
)
# end
assert_equal(
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
).flat[0],
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
"end",
).flat[0],
)
assert_equal(
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
Decimal("0"),
).flat[0],
np.ipmt(
Decimal("0.1") / Decimal("12"),
Decimal("1"),
Decimal("24"),
Decimal("2000"),
Decimal("0"),
"end",
).flat[0],
)
def interestRepayment(mortgagePercent, mortgageYearsRemaining, mortgageDebtRemaining):
if mortgageYearsRemaining > 0 and mortgageDebtRemaining > 0:
return -1 * np.ipmt(mortgagePercent, np.arange(mortgageYearsRemaining) + 1, mortgageYearsRemaining, mortgageDebtRemaining)[0]
else:
return 0
def _simulate(self, result):
battery = result['battery']
pcs = result['pcs']
sim_cfg = self._cfg['simulation']
# 구축 비용 계산
construction_cfg = sim_cfg['construction']
ess_cost = ((construction_cfg['battery'] * battery) +
(construction_cfg['pcs'] * pcs) +
(construction_cfg['install'] * battery) +
(construction_cfg['ems'] * battery))
manage_cost = ess_cost * \
construction_cfg['manage'] / (1 - construction_cfg['manage'])
construction_cost = ess_cost + manage_cost
life_cycle = sim_cfg['life_cycle'] * 12
year = self._start_month.year
ess_share_cfg = sim_cfg['ess_share']
intense_period = ess_share_cfg['intense_period'] * 12
intense_share_rate = ess_share_cfg['intense_rate']
normal_share_rate = ess_share_cfg['normal_rate']
# 금융 변수. 현재는 월단위 상환만 고려
finance_cfg = sim_cfg['finance']
# 차입원금
principal = construction_cost * finance_cfg['borrow_rate']
# 투자금
equity = construction_cost - principal
# 거치기간
grace_period = finance_cfg['grace_period'] * 12
# 원금상환기간
principal_payment_period = finance_cfg['principal_payment_period'] * 12
# 원리금상환기간
payment_period = grace_period + principal_payment_period
# 이자율
interest_rate = finance_cfg['interest_rate'] / 12
# 미상환 원금
remain_principal = principal
# 운용변수
operation_cfg = sim_cfg['operation']
sga_rate = operation_cfg['sga_rate']
depreciation_period = operation_cfg['depreciation_period'] * 12
corp_tex_rate = operation_cfg['corp_tex_rate']
net_equity_cashes = [-equity]
cumul_equity_cashes = []
yearly_net_equity_cash = 0.0
yearly_cumul_equity_cash = 0.0
net_project_cashes = [-construction_cost]
cumul_project_cashes = []
yearly_net_projct_cash = 0.0
yearly_cumul_project_cash = 0.0
simulated_annual_peak_usage = 0.0
for info in self._monthly_infos:
self._simulate_ess(info, battery, pcs / 4)
if simulated_annual_peak_usage < info.simulation.consumption.peak_usage:
simulated_annual_peak_usage = info.simulation.consumption.peak_usage
for sim_month in range(life_cycle):
info = self._monthly_infos[self._monthly_info_index(sim_month)]
# 요금적용전력 산출
if sim_month < 12:
peak_usage = info.simulation.consumption.peak_usage
for month in range(sim_month):
monthly_info = self._monthly_infos[
self._monthly_info_index(month)]
if monthly_info.month in (1, 2, 7, 8, 9, 12):
if peak_usage < monthly_info.simulation.consumption.peak_usage:
peak_usage = monthly_info.simulation.consumption.peak_usage
for month in range(sim_month + 1, 12):
monthly_info = self._monthly_infos[
self._monthly_info_index(month)]
if monthly_info.month in (1, 2, 7, 8, 9, 12):
if peak_usage < monthly_info.measure.peak_usage:
peak_usage = monthly_info.measure.peak_usage
peak_demand = peak_usage * 4
else:
peak_demand = simulated_annual_peak_usage * 4
# 기본 요금 계산
demand_price = peak_demand * self._charge.demand
# 사용량 요금 계산
seasonal_charge = self._charge.energy[Utils.season(info.month)]
energy_price = 0.0
for usage_load in UsageLoad:
energy_price += (
info.simulation.consumption.usages[usage_load] *
seasonal_charge[usage_load])
# 요금 할인 계산
demand_price_discount = 0.0
energy_price_discount = 0.0
if year < 2027:
# 2027년 이전에서 기본요금 할인 적용
demand_price_discount = info.simulation.estimated_peak_cut * self._charge.demand
if year < 2021:
# 2021년 이전에는 기본요금할인 3배 경부하 충전 할인 적용
demand_price_discount *= 3
light_load_charges = info.simulation.total_charges
energy_price_discount = light_load_charges * \
seasonal_charge[UsageLoad.LIGHT] * 0.5
# 배터리 용량에 따른 할인 차등 적용
battery_ratio = battery / self._cfg['contract_demand']
if battery_ratio >= 0.1:
demand_price_discount *= 1.2
energy_price_discount * 1.2
elif battery_ratio < 0.05:
demand_price_discount *= 0.8
energy_price_discount * 0.8
# 최종 납부 전기요금 계산
total_price = demand_price + energy_price - \
demand_price_discount - energy_price_discount
VAT = total_price * 0.1
power_industry_fund = total_price * 0.037
sim_payment = total_price + VAT + power_industry_fund
# ESS 운용에 따른 수익 계산
ess_profit = info.bill.payment - sim_payment
# 전체 수익. 현재는 ESS 수익만 고려하지만 향후 PV, DR 수익도 고려
cash_in = ess_profit
# 투자금 집중회수 기간 적용해서 수용가 수익 분배 계산
if sim_month <= intense_period:
ess_share = ess_profit * intense_share_rate
else:
ess_share = ess_profit * normal_share_rate
# 매출원가 계산. 현재는 ess_share만 고려되어 있는데 향후 다른 부분 추가
cost_of_sales = ess_share
# 금융 비용 계산
interest = 0
principal_payment = 0
if sim_month <= payment_period:
# 이자 계산
interest = numpy.ipmt(interest_rate, sim_month + 1,
payment_period, remain_principal)
if sim_month >= grace_period:
# 거치 기간이 지나면 상환 원금 계산
principal_payment = numpy.ppmt(interest_rate,
sim_month - grace_period,
principal_payment_period,
principal)
# 남은 원금 계산
remain_principal += principal_payment
finance_payment = interest + principal_payment
# 판관비
sga_expense = sga_rate * cash_in
# 감가상각 계산
if sim_month < depreciation_period:
depreciation = construction_cost / depreciation_period
else:
depreciation = 0.0
# 법인세 계산
corp_tex_std = cash_in - cost_of_sales - sga_expense - interest - depreciation
corp_tex = corp_tex_std * corp_tex_rate if corp_tex_std > 0.0 else 0.0
# 비용 계산. 이자와 원금상환은 ipmt, ppmt 함수에서 음수로 나오기 때문에 빼줌
cash_out = cost_of_sales - finance_payment + sga_expense + corp_tex
# 현금 흐름 계산
net_equity_cash = cash_in - cash_out
yearly_net_equity_cash += net_equity_cash
yearly_cumul_equity_cash += net_equity_cash
net_project_cash = net_equity_cash - finance_payment
yearly_net_projct_cash += net_project_cash
yearly_cumul_project_cash += net_project_cash
if info.month == 12:
net_equity_cashes.append(yearly_net_equity_cash)
net_project_cashes.append(yearly_net_projct_cash)
cumul_equity_cashes.append(yearly_cumul_equity_cash)
cumul_project_cashes.append(yearly_cumul_project_cash)
yearly_net_equity_cash = 0.0
yearly_net_projct_cash = 0.0
year += 1
net_equity_cashes.append(yearly_net_equity_cash)
net_project_cashes.append(yearly_net_projct_cash)
cumul_equity_cashes.append(yearly_cumul_equity_cash)
cumul_project_cashes.append(yearly_cumul_project_cash)
result["construction_cost"] = construction_cost
result["equity"] = equity
result["peak_cut"] = (self._annual_peak_usage -
simulated_annual_peak_usage) * 4
result["equity_irr"] = numpy.irr(net_equity_cashes) * 100.0
result["project_irr"] = numpy.irr(net_project_cashes) * 100.0
net_equity_cashes.pop(0)
net_project_cashes.pop(0)
net_cash_flow = []
cumul_cash_flow = []
year = self._start_month.year
for net_ecash, net_pcash, cumul_ecash, cumul_pcash in zip(
net_equity_cashes, net_project_cashes, cumul_equity_cashes,
cumul_project_cashes):
net_cash_flow.append({
"year": year,
"equity": net_ecash,
"project": net_pcash
})
cumul_cash_flow.append({
"year": year,
"equity": cumul_ecash,
"project": cumul_pcash
})
year += 1
result["net_cash_flow"] = net_cash_flow
result["cumul_cash_flow"] = cumul_cash_flow
result["summer_peak"] = self._compare_seasonal_peak(Season.SUMMER)
result["winter_peak"] = self._compare_seasonal_peak(Season.WINTER)
result["spring_fall_peak"] = self._compare_seasonal_peak(
Season.SPRING_FALL)
def test_ipmt_decimal(self):
result = np.ipmt(Decimal("0.1") / Decimal("12"), 1, 24, 2000)
assert_equal(result.flat[0], Decimal("-16.66666666666666666666666667"))
def interest_m(self):
'''Returns the interest component of monthly payment due'''
per = 1
ipmt = np.ipmt(self.interest, per, self.n, self.principal)
return abs(ipmt)
def calcu_award_cost(p,n,r,takerate,takerate_n,award_type,award_power):
'''
计算各种优惠券成本
p:本金
n:期限
r:年化利率
takerate:费用
takerate_n:费用分数
award_type:奖券类型 支持：利息，费用，息费
award_power:优惠力度，int(>=1)：几期免息，float(<1)：折扣比例; 如 1 ：1期免息；0.9：利息9折
'''
# pmt = np.pmt(r/12,n,-p) # 每期本息
per = np.arange(n) + 1
ipmt = np.ipmt(r/12,per,n,-p) # 每期利息
interest = sum(ipmt) # 利息总和
fees = p * takerate # 费用
per_fees = fees / takerate_n # 每期费用
if award_power > n:
print('注意：优惠力度大于期限')
return '注意：优惠力度大于期限'
# 利息券
if award_type == '利息':
# 几期免息
if award_power >= 1:
free_is = ipmt[:award_power]
return round(sum(free_is),4), round(sum(free_is)/p,4)
# 整体利息折扣券
else:
free_is = interest * (1-award_power)
return round(free_is,4), round(free_is/p,4)
elif award_type == '费用':
# 几期免息
if award_power >= 1:
if award_power > takerate_n:
return '免费期数超过费用分期数'
free_is = per_fees * award_power
return round(free_is,4), round(free_is/p,4)
# 整体利息折扣券
else:
free_is = fees * (1-award_power)
return round(free_is,4), round(free_is/p,4)
elif award_type == '息费':
# 几期免息
if award_power >= 1:
free_is = ipmt[:award_power] # 利息
if award_power > takerate_n:
free_fees = fees
else:
free_fees = per_fees * award_power # 费用
return round(sum(free_is) + free_fees, 4) , round((sum(free_is) + free_fees)/p,4)
# 整体利息折扣券
else:
free_is = (interest+fees) * (1-award_power)
return round(free_is,4), round(free_is/p,4)
else:
print('请输入正确的优惠券类型，目前支持：利息，费用，息费三种类型')
df = df.rename(columns=rename_dict) df = df.rename(columns=purpose_dict) # identify good loans and bad loans (might include the 31 to 120 day late category later) df.loc[df['ls_default'] == 1, 'bad_loan'] = 1 df.loc[df['ls_chargeoff'] == 1, 'bad_loan'] = 1 df.loc[df['bad_loan'] != 1, 'bad_loan'] = 0 df.loc[df['bad_loan'] != 1, 'good_loan'] = 1 df.loc[df['good_loan'] != 1, 'good_loan'] = 0 # add columns related to debt service and debt service coverage # calculate annual debt service payments for Lending Club loans df['annual_prin'] = sum([np.ppmt(df['int_rate'] / 12, i, df['term'], -df['loan_amnt'], 0) for i in range(1, 13)]) df['annual_prin'] = df['annual_prin'].round(2) df['annual_int'] = sum([np.ipmt(df['int_rate'] / 12, i, df['term'], -df['loan_amnt'], 0) for i in range(1, 13)]) df['annual_int'] = df['annual_int'].round(2) df['annual_debt_svc'] = df['annual_prin'] + df['annual_int'] df['annual_debt_svc'] = df['annual_debt_svc'].round(2) # total revolving debt NOT attributable to Lending Club loans df['other_rev_debt'] = df['total_bal_ex_mort'] - df['out_prncp'] df.loc[df['other_rev_debt'] < 0, 'other_rev_debt'] = 0 # total mortgage/installment debt (also not Lending Club) df['other_mort_debt'] = df['tot_cur_bal'] - df['other_rev_debt'] df.loc[df['other_mort_debt'] < 0, 'other_mort_debt'] = 0 # estimated annual debt service requirement on non-Lending-Club revolving debt (at Lending Club int_rate, 5yr amort) df['addl_annual_rev_ds'] = (12 * (df['other_rev_debt'] / 60)) + (df['int_rate'] * df['other_rev_debt']) df['addl_annual_rev_ds'] = df['addl_annual_rev_ds'].round(2)
#!/usr/bin/env python [whats the use of this line]
# Part A: House Hunting
#importing the right modules
import numpy as np
#Initialising variables
total_cost = int(input('What is the cost of your dream home: '))
Payment = np.ipmt(rate,per, nper, pv, fv=0, when= 'end')# interest portion of a payment
ppmt
pmt=
def work(modelDir, inputDict):
''' Run the model in its directory. '''
#Set static input data
simLength = 8760
simStartDate = "2013-01-01"
# Set the timezone to be UTC, it won't affect calculation and display, relative offset handled in pvWatts.html
startDateTime = simStartDate + " 00:00:00 UTC"
simLengthUnits = "hours"
# Associate zipcode to climate data
inputDict["climateName"], latforpvwatts = zipCodeToClimateName(
inputDict["zipCode"])
inverterSizeAC = float(inputDict.get("inverterSize", 0))
if (inputDict.get("systemSize", 0) == "-"):
arraySizeDC = 1.3908 * inverterSizeAC
else:
arraySizeDC = float(inputDict.get("systemSize", 0))
numberPanels = (arraySizeDC * 1000 / 305)
# Set constants
panelSize = 305
trackingMode = 0
rotlim = 45.0
gamma = 0.45
if (inputDict.get("tilt", 0) == "-"):
manualTilt = latforpvwatts
else:
manualTilt = float(inputDict.get("tilt", 0))
numberInverters = math.ceil(inverterSizeAC / 1000 / 0.5)
# Copy specific climate data into model directory
shutil.copy(
pJoin(__neoMetaModel__._omfDir, "data", "Climate",
inputDict["climateName"] + ".tmy2"),
pJoin(modelDir, "climate.tmy2"))
# Set up SAM data structures.
ssc = nrelsam2013.SSCAPI()
dat = ssc.ssc_data_create()
# Required user inputs.
ssc.ssc_data_set_string(dat, "file_name", modelDir + "/climate.tmy2")
ssc.ssc_data_set_number(dat, "system_size", arraySizeDC)
ssc.ssc_data_set_number(
dat, "derate",
float(inputDict.get("inverterEfficiency", 96)) / 100 *
float(inputDict.get("nonInverterEfficiency", 87)) / 100)
ssc.ssc_data_set_number(dat, "track_mode", float(trackingMode))
ssc.ssc_data_set_number(dat, "azimuth",
float(inputDict.get("azimuth", 180)))
# Advanced inputs with defaults.
ssc.ssc_data_set_number(dat, "rotlim", float(rotlim))
ssc.ssc_data_set_number(dat, "gamma", float(-gamma / 100))
ssc.ssc_data_set_number(dat, "tilt", manualTilt)
ssc.ssc_data_set_number(dat, "tilt_eq_lat", 0.0)
# Run PV system simulation.
mod = ssc.ssc_module_create("pvwattsv1")
ssc.ssc_module_exec(mod, dat)
# Timestamp output.
outData = {}
outData["timeStamps"] = [
dt.datetime.strftime(
dt.datetime.strptime(startDateTime[0:19], "%Y-%m-%d %H:%M:%S") +
dt.timedelta(**{simLengthUnits: x}), "%Y-%m-%d %H:%M:%S") + " UTC"
for x in range(simLength)
]
# Geodata output.
# Geodata output.
outData["minLandSize"] = round(
(arraySizeDC / 1390.8 * 5 + 1) * math.cos(math.radians(22.5)) /
math.cos(math.radians(latforpvwatts)), 0)
landAmount = float(inputDict.get("landAmount", 6.0))
outData["city"] = ssc.ssc_data_get_string(dat, "city")
outData["state"] = ssc.ssc_data_get_string(dat, "state")
outData["lat"] = ssc.ssc_data_get_number(dat, "lat")
outData["lon"] = ssc.ssc_data_get_number(dat, "lon")
outData["elev"] = ssc.ssc_data_get_number(dat, "elev")
# Weather output.
outData["climate"] = {}
outData["climate"][
"Global Horizontal Radiation (W/m^2)"] = ssc.ssc_data_get_array(
dat, "gh")
outData["climate"][
"Plane of Array Irradiance (W/m^2)"] = ssc.ssc_data_get_array(
dat, "poa")
outData["climate"]["Ambient Temperature (F)"] = ssc.ssc_data_get_array(
dat, "tamb")
outData["climate"]["Cell Temperature (F)"] = ssc.ssc_data_get_array(
dat, "tcell")
outData["climate"]["Wind Speed (m/s)"] = ssc.ssc_data_get_array(
dat, "wspd")
# Power generation.
outData["powerOutputAc"] = ssc.ssc_data_get_array(dat, "ac")
# Calculate clipping.
outData["powerOutputAc"] = ssc.ssc_data_get_array(dat, "ac")
invSizeWatts = inverterSizeAC * 1000
outData["powerOutputAcInvClipped"] = [
x if x < invSizeWatts else invSizeWatts
for x in outData["powerOutputAc"]
]
try:
outData["percentClipped"] = 100 * (
1.0 - sum(outData["powerOutputAcInvClipped"]) /
sum(outData["powerOutputAc"]))
except ZeroDivisionError:
outData["percentClipped"] = 0.0
#One year generation
outData["oneYearGenerationWh"] = sum(outData["powerOutputAcInvClipped"])
#Annual generation for all years
loanYears = 25
outData["allYearGenerationMWh"] = {}
outData["allYearGenerationMWh"][1] = float(
outData["oneYearGenerationWh"]) / 1000000
# outData["allYearGenerationMWh"][1] = float(2019.576)
for i in range(2, loanYears + 1):
outData["allYearGenerationMWh"][i] = float(
outData["allYearGenerationMWh"][i - 1]) * (
1 - float(inputDict.get("degradation", 0.8)) / 100)
# Summary of Results.
######
### Total Costs (sum of): Hardware Costs, Design/Engineering/PM/EPC/Labor Costs, Siteprep Costs, Construction Costs, Installation Costs, Land Costs
######
### Hardware Costs
pvModules = arraySizeDC * float(inputDict.get("moduleCost",
0)) * 1000 #off by 4000
racking = arraySizeDC * float(inputDict.get("rackCost", 0)) * 1000
inverters = numberInverters * float(inputDict.get("inverterCost", 0))
inverterSize = inverterSizeAC
if (inverterSize <= 250):
gear = 15000
elif (inverterSize <= 600):
gear = 18000
else:
gear = inverterSize / 1000 * 22000
balance = inverterSizeAC * 1.3908 * 134
combiners = math.ceil(numberPanels / 19 / 24) * float(1800) #*
wireManagement = arraySizeDC * 1.5
transformer = 1 * 28000
weatherStation = 1 * 12500
shipping = 1.02
hardwareCosts = (pvModules + racking + inverters + gear + balance +
combiners + wireManagement + transformer +
weatherStation) * shipping
### Design/Engineering/PM/EPC/Labor Costs
EPCmarkup = float(inputDict.get("EPCRate", 0)) / 100 * hardwareCosts
#designCosts = float(inputDict.get("mechLabor",0))*160 + float(inputDict.get("elecLabor",0))*75 + float(inputDict.get("pmCost",0)) + EPCmarkup
hoursDesign = 160 * math.sqrt(arraySizeDC / 1390)
hoursElectrical = 80 * math.sqrt(arraySizeDC / 1391)
designLabor = 65 * hoursDesign
electricalLabor = 75 * hoursElectrical
laborDesign = designLabor + electricalLabor + float(
inputDict.get("pmCost", 0)) + EPCmarkup
materialDesign = 0
designCosts = materialDesign + laborDesign
### Siteprep Costs
surveying = 2.25 * 4 * math.sqrt(landAmount * 43560)
concrete = 8000 * math.ceil(numberInverters / 2)
fencing = 6.75 * 4 * math.sqrt(landAmount * 43560)
grading = 2.5 * 4 * math.sqrt(landAmount * 43560)
landscaping = 750 * landAmount
siteMaterial = 8000 + 600 + 5500 + 5000 + surveying + concrete + fencing + grading + landscaping + 5600
blueprints = float(inputDict.get("mechLabor", 0)) * 12
mobilization = float(inputDict.get("mechLabor", 0)) * 208
mobilizationMaterial = float(inputDict.get("mechLabor", 0)) * 19.98
siteLabor = blueprints + mobilization + mobilizationMaterial
sitePrep = siteMaterial + siteLabor
### Construction Costs (Office Trailer, Skid Steer, Storage Containers, etc)
constrEquip = 6000 + math.sqrt(landAmount) * 16200
### Installation Costs
moduleAndRackingInstall = numberPanels * (15.00 + 12.50 + 1.50)
pierDriving = 1 * arraySizeDC * 20
balanceInstall = 1 * arraySizeDC * 100
installCosts = moduleAndRackingInstall + pierDriving + balanceInstall + float(
inputDict.get("elecLabor", 0)) * (72 + 60 + 70 + 10 + 5 + 30 + 70)
### Land Costs
if (str(inputDict.get("landOwnership", 0)) == "Owned"
or (str(inputDict.get("landOwnership", 0)) == "Leased")):
landCosts = 0
else:
landCosts = float(inputDict.get("costAcre", 0)) * landAmount
######
### Total Costs
######
totalCosts = hardwareCosts + designCosts + sitePrep + constrEquip + installCosts + landCosts
totalFees = float(inputDict.get("devCost", 0)) / 100 * totalCosts
outData["totalCost"] = totalCosts + totalFees + float(
inputDict.get("interCost", 0))
# Add to Pie Chart
outData["costsPieChart"] = [
["Land", landCosts], ["Design/Engineering/PM/EPC", designCosts],
["PV Modules", pvModules * shipping], ["Racking", racking * shipping],
["Inverters & Switchgear", (inverters + gear) * shipping],
[
"BOS", hardwareCosts - pvModules * shipping - racking * shipping -
(inverters + gear) * shipping
],
[
"Site Prep, Constr. Eq. and Installation",
(siteMaterial + constrEquip) + (siteLabor + installCosts)
]
]
# Cost per Wdc
outData["costWdc"] = (totalCosts + totalFees + float(
inputDict.get("interCost", 0))) / (arraySizeDC * 1000)
outData["capFactor"] = float(outData["oneYearGenerationWh"]) / (
inverterSizeAC * 1000 * 365.25 * 24) * 100
######
### Loans calculations for Direct, NCREB, Lease, Tax-equity, and PPA
######
### Full Ownership, Direct Loan
#Output - Direct Loan [C]
projectCostsDirect = 0
#Output - Direct Loan [D]
netFinancingCostsDirect = 0
#Output - Direct Loan [E]
OMInsuranceETCDirect = []
#Output - Direct Loan [F]
distAdderDirect = []
#Output - Direct Loan [G]
netCoopPaymentsDirect = []
#Output - Direct Loan [H]
costToCustomerDirect = []
#Output - Direct Loan [F53]
Rate_Levelized_Direct = 0
## Output - Direct Loan Formulas
projectCostsDirect = 0
#Output - Direct Loan [D]
payment = pmt(
float(inputDict.get("loanRate", 0)) / 100, loanYears,
outData["totalCost"])
interestDirectPI = outData["totalCost"] * float(
inputDict.get("loanRate", 0)) / 100
principleDirectPI = (-payment - interestDirectPI)
patronageCapitalRetiredDPI = 0
netFinancingCostsDirect = -(principleDirectPI + interestDirectPI -
patronageCapitalRetiredDPI)
#Output - Direct Loan [E] [F] [G] [H]
firstYearOPMainCosts = (1.25 * arraySizeDC * 12)
firstYearInsuranceCosts = (0.37 * outData["totalCost"] / 100)
if (inputDict.get("landOwnership", 0) == "Leased"):
firstYearLandLeaseCosts = float(inputDict.get("costAcre",
0)) * landAmount
else:
firstYearLandLeaseCosts = 0
for i in range(1, len(outData["allYearGenerationMWh"]) + 1):
OMInsuranceETCDirect.append(
-firstYearOPMainCosts * math.pow((1 + .01), (i - 1)) -
firstYearInsuranceCosts * math.pow((1 + .025), (i - 1)) -
firstYearLandLeaseCosts * math.pow((1 + .01), (i - 1)))
distAdderDirect.append(
float(inputDict.get("distAdder", 0)) *
outData["allYearGenerationMWh"][i])
netCoopPaymentsDirect.append(OMInsuranceETCDirect[i - 1] +
netFinancingCostsDirect)
costToCustomerDirect.append(
(netCoopPaymentsDirect[i - 1] - distAdderDirect[i - 1]))
#Output - Direct Loan [F53]
NPVLoanDirect = npv(
float(inputDict.get("discRate", 0)) / 100,
[0, 0] + costToCustomerDirect)
NPVallYearGenerationMWh = npv(
float(inputDict.get("discRate", 0)) / 100,
[0, 0] + outData["allYearGenerationMWh"].values())
Rate_Levelized_Direct = -NPVLoanDirect / NPVallYearGenerationMWh
#Master Output [Direct Loan]
outData["levelCostDirect"] = Rate_Levelized_Direct
outData["costPanelDirect"] = abs(NPVLoanDirect / numberPanels)
outData["cost10WPanelDirect"] = (float(outData["costPanelDirect"]) /
panelSize) * 10
### NCREBs Financing
ncrebsRate = float(inputDict.get("NCREBRate", 4.060)) / 100
ncrebBorrowingRate = 1.1 * ncrebsRate
ncrebPaymentPeriods = 44
ncrebCostToCustomer = []
# TODO ASAP: FIX ARRAY OFFSETS START 0
for i in range(1, len(outData["allYearGenerationMWh"]) + 1):
coopLoanPayment = 2 * pmt(
ncrebBorrowingRate / 2.0, ncrebPaymentPeriods,
outData["totalCost"]) if i <= ncrebPaymentPeriods / 2 else 0
ncrebsCredit = -0.7 * (
ipmt(ncrebsRate / 2, 2 * i -
1, ncrebPaymentPeriods, outData["totalCost"]) +
ipmt(ncrebsRate / 2, 2 * i, ncrebPaymentPeriods,
outData["totalCost"])) if i <= ncrebPaymentPeriods / 2 else 0
financingCost = ncrebsCredit + coopLoanPayment
omCost = OMInsuranceETCDirect[i - 1]
netCoopPayments = financingCost + omCost
distrAdder = distAdderDirect[i - 1]
costToCustomer = netCoopPayments + distrAdder
ncrebCostToCustomer.append(costToCustomer)
NPVLoanNCREB = npv(
float(inputDict.get("discRate", 0)) / 100,
[0, 0] + ncrebCostToCustomer)
Rate_Levelized_NCREB = -NPVLoanNCREB / NPVallYearGenerationMWh
outData["levelCostNCREB"] = Rate_Levelized_NCREB
outData["costPanelNCREB"] = abs(NPVLoanNCREB / numberPanels)
outData["cost10WPanelNCREB"] = (float(outData["costPanelNCREB"]) /
panelSize) * 10
### Lease Buyback Structure
#Output - Lease [C]
projectCostsLease = outData["totalCost"]
#Output - Lease [D]
leasePaymentsLease = []
#Output - Lease [E]
OMInsuranceETCLease = OMInsuranceETCDirect
#Output - Lease [F]
distAdderLease = distAdderDirect
#Output - Lease [G]
netCoopPaymentsLease = []
#Output - Lease [H]
costToCustomerLease = []
#Output - Lease [H44]
NPVLease = 0
#Output - Lease [H49]
Rate_Levelized_Lease = 0
## Tax Lease Formulas
#Output - Lease [D]
for i in range(0, 12):
leaseRate = float(inputDict.get("taxLeaseRate", 0)) / 100.0
if i > 8: # Special behavior in later years:
leaseRate = leaseRate - 0.0261
leasePaymentsLease.append(-1 * projectCostsLease /
((1.0 - (1.0 / (1.0 + leaseRate)**12)) /
(leaseRate)))
# Last year is different.
leasePaymentsLease[11] += -0.2 * projectCostsLease
for i in range(12, 25):
leasePaymentsLease.append(0)
#Output - Lease [G] [H]
for i in range(1, len(outData["allYearGenerationMWh"]) + 1):
netCoopPaymentsLease.append(OMInsuranceETCLease[i - 1] +
leasePaymentsLease[i - 1])
costToCustomerLease.append(netCoopPaymentsLease[i - 1] -
distAdderLease[i - 1])
#Output - Lease [H44]. Note the extra year at the zero point to get the discounting right.
NPVLease = npv(
float(inputDict.get("discRate", 0)) / 100,
[0, 0] + costToCustomerLease)
#Output - Lease [H49] (Levelized Cost Three Loops)
Rate_Levelized_Lease = -NPVLease / NPVallYearGenerationMWh
#Master Output [Lease]
outData["levelCostTaxLease"] = Rate_Levelized_Lease
outData["costPanelTaxLease"] = abs(NPVLease / numberPanels)
outData["cost10WPanelTaxLease"] = (float(outData["costPanelTaxLease"]) /
float(panelSize)) * 10
### Tax Equity Flip Structure
# Tax Equity Flip Function
def taxEquityFlip(PPARateSixYearsTE, discRate, totalCost,
allYearGenerationMWh, distAdderDirect, loanYears,
firstYearLandLeaseCosts, firstYearOPMainCosts,
firstYearInsuranceCosts, numberPanels):
#Output Tax Equity Flip [C]
coopInvestmentTaxEquity = -totalCost * (1 - 0.53)
#Output Tax Equity Flip [D]
financeCostCashTaxEquity = 0
#Output Tax Equity Flip [E]
cashToSPEOForPPATE = []
#Output Tax Equity Flip [F]
derivedCostEnergyTE = 0
#Output Tax Equity Flip [G]
OMInsuranceETCTE = []
#Output Tax Equity Flip [H]
cashFromSPEToBlockerTE = []
#Output Tax Equity Flip [I]
cashFromBlockerTE = 0
#Output Tax Equity Flip [J]
distAdderTaxEquity = distAdderDirect
#Output Tax Equity Flip [K]
netCoopPaymentsTaxEquity = []
#Output Tax Equity Flip [L]
costToCustomerTaxEquity = []
#Output Tax Equity Flip [L64]
NPVLoanTaxEquity = 0
#Output Tax Equity Flip [F72]
Rate_Levelized_Equity = 0
## Tax Equity Flip Formulas
#Output Tax Equity Flip [D]
#TEI Calcs [E]
financeCostOfCashTE = 0
coopFinanceRateTE = 2.7 / 100
if (coopFinanceRateTE == 0):
financeCostOfCashTE = 0
else:
payment = pmt(coopFinanceRateTE, loanYears,
-coopInvestmentTaxEquity)
financeCostCashTaxEquity = payment
#Output Tax Equity Flip [E]
SPERevenueTE = []
for i in range(1, len(allYearGenerationMWh) + 1):
SPERevenueTE.append(PPARateSixYearsTE * allYearGenerationMWh[i])
if ((i >= 1) and (i <= 6)):
cashToSPEOForPPATE.append(-SPERevenueTE[i - 1])
else:
cashToSPEOForPPATE.append(0)
#Output Tax Equity Flip [F]
derivedCostEnergyTE = cashToSPEOForPPATE[0] / allYearGenerationMWh[1]
#Output Tax Equity Flip [G]
#TEI Calcs [F] [U] [V]
landLeaseTE = []
OMTE = []
insuranceTE = []
for i in range(1, len(allYearGenerationMWh) + 1):
landLeaseTE.append(firstYearLandLeaseCosts * math.pow((1 + .01),
(i - 1)))
OMTE.append(-firstYearOPMainCosts * math.pow((1 + .01), (i - 1)))
insuranceTE.append(-firstYearInsuranceCosts * math.pow((1 + .025),
(i - 1)))
if (i < 7):
OMInsuranceETCTE.append(float(landLeaseTE[i - 1]))
else:
OMInsuranceETCTE.append(
float(OMTE[i - 1]) + float(insuranceTE[i - 1]) +
float(landLeaseTE[i - 1]))
#Output Tax Equity Flip [H]
#TEI Calcs [T]
SPEMgmtFeeTE = []
EBITDATE = []
EBITDATEREDUCED = []
managementFee = 10000
for i in range(1, len(SPERevenueTE) + 1):
SPEMgmtFeeTE.append(-managementFee * math.pow((1 + .01), (i - 1)))
EBITDATE.append(
float(SPERevenueTE[i - 1]) + float(OMTE[i - 1]) +
float(insuranceTE[i - 1]) + float(SPEMgmtFeeTE[i - 1]))
if (i <= 6):
cashFromSPEToBlockerTE.append(float(EBITDATE[i - 1]) * .01)
else:
cashFromSPEToBlockerTE.append(0)
EBITDATEREDUCED.append(EBITDATE[i - 1])
#Output Tax Equity Flip [I]
#TEI Calcs [Y21]
cashRevenueTE = -totalCost * (1 - 0.53)
buyoutAmountTE = 0
for i in range(1, len(EBITDATEREDUCED) + 1):
buyoutAmountTE = buyoutAmountTE + EBITDATEREDUCED[i - 1] / (
math.pow(1 + 0.12, i))
buyoutAmountTE = buyoutAmountTE * 0.05
cashFromBlockerTE = -(buyoutAmountTE) + 0.0725 * cashRevenueTE
#Output Tax Equity Flip [K] [L]
for i in range(1, len(allYearGenerationMWh) + 1):
if (i == 6):
netCoopPaymentsTaxEquity.append(financeCostCashTaxEquity +
cashToSPEOForPPATE[i - 1] +
cashFromSPEToBlockerTE[i - 1] +
OMInsuranceETCTE[i - 1] +
cashFromBlockerTE)
else:
netCoopPaymentsTaxEquity.append(financeCostCashTaxEquity +
cashFromSPEToBlockerTE[i - 1] +
cashToSPEOForPPATE[i - 1] +
OMInsuranceETCTE[i - 1])
costToCustomerTaxEquity.append(netCoopPaymentsTaxEquity[i - 1] -
distAdderTaxEquity[i - 1])
#Output Tax Equity Flip [L37]
NPVLoanTaxEquity = npv(
float(inputDict.get("discRate", 0)) / 100,
[0, 0] + costToCustomerTaxEquity)
#Output - Tax Equity [F42]
Rate_Levelized_TaxEquity = -NPVLoanTaxEquity / NPVallYearGenerationMWh
#TEI Calcs - Achieved Return [AW 21]
#[AK]
MACRDepreciation = []
MACRDepreciation.append(-0.99 * 0.2 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
MACRDepreciation.append(-0.99 * 0.32 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
MACRDepreciation.append(-0.99 * 0.192 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
MACRDepreciation.append(-0.99 * 0.1152 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
MACRDepreciation.append(-0.99 * 0.1152 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
MACRDepreciation.append(-0.99 * 0.0576 *
(totalCost - totalCost * 0.5 * 0.9822 * 0.3))
#[AI] [AL] [AN]
cashRevenueTEI = [] #[AI]
slDepreciation = [] #[AL]
totalDistributions = [] #[AN]
cashRevenueTEI.append(-totalCost * 0.53)
for i in range(1, 7):
cashRevenueTEI.append(EBITDATE[i - 1] * 0.99)
slDepreciation.append(totalCost / 25)
totalDistributions.append(-cashRevenueTEI[i])
#[AJ]
ITC = totalCost * 0.9822 * 0.3 * 0.99
#[AM]
taxableIncLoss = [0]
taxableIncLoss.append(cashRevenueTEI[1] + MACRDepreciation[0])
#[AO]
capitalAcct = []
capitalAcct.append(totalCost * 0.53)
condition = capitalAcct[0] - 0.5 * ITC + taxableIncLoss[
1] + totalDistributions[0]
if condition > 0:
capitalAcct.append(condition)
else:
capitalAcct.append(0)
#[AQ]
ratioTE = [0]
#[AP]
reallocatedIncLoss = []
#AO-1 + AN + AI + AK + AJ
for i in range(0, 5):
reallocatedIncLoss.append(capitalAcct[i + 1] +
totalDistributions[i + 1] +
MACRDepreciation[i + 1] +
cashRevenueTEI[i + 2])
ratioTE.append(reallocatedIncLoss[i] /
(cashRevenueTEI[i + 2] + MACRDepreciation[i + 1]))
taxableIncLoss.append(
cashRevenueTEI[i + 2] + MACRDepreciation[i + 1] -
ratioTE[i + 1] *
(MACRDepreciation[i + 1] - totalDistributions[i + 1]))
condition = capitalAcct[i + 1] + taxableIncLoss[
i + 2] + totalDistributions[i + 1]
if condition > 0:
capitalAcct.append(condition)
else:
capitalAcct.append(0)
#[AR]
taxesBenefitLiab = [0]
for i in range(1, 7):
taxesBenefitLiab.append(-taxableIncLoss[i] * 0.35)
#[AS] [AT]
buyoutAmount = 0
taxFromBuyout = 0
for i in range(0, len(EBITDATEREDUCED)):
buyoutAmount = buyoutAmount + .05 * EBITDATEREDUCED[i] / (math.pow(
1.12, (i + 1)))
taxFromBuyout = -buyoutAmount * 0.35
#[AU] [AV]
totalCashTax = []
cumulativeCashTax = [0]
for i in range(0, 7):
if i == 1:
totalCashTax.append(cashRevenueTEI[i] + ITC +
taxesBenefitLiab[i] + 0 + 0)
cumulativeCashTax.append(cumulativeCashTax[i] +
totalCashTax[i])
elif i == 6:
totalCashTax.append(cashRevenueTEI[i] + 0 +
taxesBenefitLiab[i] + buyoutAmount +
taxFromBuyout)
cumulativeCashTax.append(cumulativeCashTax[i] +
totalCashTax[i] + buyoutAmount +
taxFromBuyout)
else:
totalCashTax.append(cashRevenueTEI[i] + 0 +
taxesBenefitLiab[i] + 0 + 0)
cumulativeCashTax.append(cumulativeCashTax[i] +
totalCashTax[i])
#[AW21]
if (cumulativeCashTax[7] > 0):
cumulativeIRR = round(irr(totalCashTax), 4)
else:
cumulativeIRR = 0
# Deleteme: Variable Dump for debugging
# variableDump = {}
# variableDump["TaxEquity"] = {}
# variableDump["TaxEquity"]["coopInvestmentTaxEquity"] = coopInvestmentTaxEquity
# variableDump["TaxEquity"]["financeCostCashTaxEquity"] = financeCostCashTaxEquity
# variableDump["TaxEquity"]["cashToSPEOForPPATE"] = cashToSPEOForPPATE
# variableDump["TaxEquity"]["derivedCostEnergyTE"] = derivedCostEnergyTE
# variableDump["TaxEquity"]["OMInsuranceETCTE"] = OMInsuranceETCTE
# variableDump["TaxEquity"]["cashFromSPEToBlockerTE"] = cashFromSPEToBlockerTE
# variableDump["TaxEquity"]["cashFromBlockerTE"] = cashFromBlockerTE
# variableDump["TaxEquity"]["distAdderTaxEquity"] = distAdderTaxEquity
# variableDump["TaxEquity"]["netCoopPaymentsTaxEquity"] = netCoopPaymentsTaxEquity
# variableDump["TaxEquity"]["NPVLoanTaxEquity"] = NPVLoanTaxEquity
return cumulativeIRR, Rate_Levelized_TaxEquity, NPVLoanTaxEquity
# Function Calls Mega Sized Tax Equity Function Above
z = 0
PPARateSixYearsTE = z / 100
nGoal = float(inputDict.get("taxEquityReturn", 0)) / 100
nValue = 0
for p in range(0, 3):
while ((z < 50000) and (nValue < nGoal)):
achievedReturnTE, Rate_Levelized_TaxEquity, NPVLoanTaxEquity = taxEquityFlip(
PPARateSixYearsTE, inputDict.get("discRate", 0),
outData["totalCost"], outData["allYearGenerationMWh"],
distAdderDirect, loanYears, firstYearLandLeaseCosts,
firstYearOPMainCosts, firstYearInsuranceCosts, numberPanels)
nValue = achievedReturnTE
z = z + math.pow(10, p)
PPARateSixYearsTE = z / 100.0
z = z - math.pow(10, p)
PPARateSixYearsTE = z / 100
#Master Output [Tax Equity]
outData["levelCostTaxEquity"] = Rate_Levelized_TaxEquity
outData["costPanelTaxEquity"] = abs(NPVLoanTaxEquity / numberPanels)
outData["cost10WPanelTaxEquity"] = (float(outData["costPanelTaxEquity"]) /
panelSize) * 10
### PPA Comparison
#Output - PPA [F]
distAdderPPA = distAdderDirect
#Output - PPA [G]
netCoopPaymentsPPA = []
#Output - PPA [H]
costToCustomerPPA = []
#Output - PPA [I]
costToCustomerPPA = []
#Output - PPA [H40]
NPVLoanPPA = 0
#Output - PPA [I40]
Rate_Levelized_PPA = 0
## PPA Formulas
#Output - PPA [G] [H]
for i in range(1, len(outData["allYearGenerationMWh"]) + 1):
netCoopPaymentsPPA.append(
-outData["allYearGenerationMWh"][i] *
float(inputDict.get("firstYearEnergyCostPPA", 0)) * math.pow(
(1 + float(inputDict.get("annualEscRatePPA", 0)) / 100),
(i - 1)))
costToCustomerPPA.append(netCoopPaymentsPPA[i - 1] -
distAdderPPA[i - 1])
#Output - PPA [H58]
NPVLoanPPA = npv(
float(inputDict.get("discRate", 0)) / 100, [0, 0] + costToCustomerPPA)
#Output - PPA [F65]
Rate_Levelized_PPA = -NPVLoanPPA / NPVallYearGenerationMWh
#Master Output [PPA]
outData["levelCostPPA"] = Rate_Levelized_PPA
outData["firstYearCostKWhPPA"] = float(
inputDict.get("firstYearEnergyCostPPA", 0))
outData["yearlyEscalationPPA"] = float(inputDict.get(
"annualEscRatePPA", 0))
# Add all Levelized Costs to Output
outData["LevelizedCosts"] = [["Direct Loan", Rate_Levelized_Direct],
["NCREBs Financing", Rate_Levelized_NCREB],
["Lease Buyback", Rate_Levelized_Lease],
["Tax Equity Flip", Rate_Levelized_TaxEquity]]
outData["LevelizedCosts"].append({
"name": "PPA Comparison",
"y": Rate_Levelized_PPA,
"color": "gold"
})
# Stdout/stderr.
outData["stdout"] = "Success"
outData["stderr"] = ""
return outData
def paydown_aftercurve(D_date1, Loan_Amount1, D_date2, Loan_Amount2, D_date3,
Loan_Amount3, D_date4, Loan_Amount4, Repayment_Option,
Expected_Graduation_Date, Repayment_Term, IntR, PO):
final_dates, period_inStatus, status, disbAmtList, cum_disb_aftercurve, interest_aftercurve, min_Pay_aftercurve, PO_List, Anticipated_Def_Amt, Anticipated_PartialPrepay_Amt, Anticipated_FullPrepay_Amt, capitalisationList_aftercurve = complete_Anticip_FPPYList(
D_date1, Loan_Amount1, D_date2, Loan_Amount2, D_date3, Loan_Amount3,
D_date4, Loan_Amount4, Repayment_Option, Expected_Graduation_Date,
Repayment_Term, IntR, PO)
endpoint_default, endpoint_ParPrepay, endpoint_FullPrepay, unit_defaults, unit_severity, unit_ParPrepay, unit_FullPrepay = curve_database(
D_date1, Loan_Amount1, D_date2, Loan_Amount2, D_date3, Loan_Amount3,
D_date4, Loan_Amount4, Repayment_Option, Expected_Graduation_Date,
Repayment_Term, IntR, PO)
Curr_Balance = []
Interest_repay = []
Payment = []
Principal = []
InterestRate = np.round(IR(IntR) / 100, 3) # interest rate required
if (Repayment_Option != "IM"):
indices = [i for i, x in enumerate(status) if x == "Grace"]
last_index = max(indices)
repay_Start_Index = last_index + 1
RepaymentTerm_Mos = Repayment_Term
else:
indices = [i for i, x in enumerate(status) if x == "In-School"]
last_index = max(indices)
repay_Start_Index = last_index + 2
RepaymentTerm_Mos = Repayment_Term - repay_Start_Index
repayment_begin_date = final_dates[
repay_Start_Index] # to get the repayment start date
repayment_end_date = final_dates[-1] # to get the repayment end date
# specifying the structure of the repayment schedule
rng = pd.date_range(repayment_begin_date,
periods=RepaymentTerm_Mos,
freq='MS')
rng.name = "Payment Date"
df = pd.DataFrame(index=rng,
columns=[
'Payment', 'Principal Paid', 'Interest Paid',
'Ending Balance'
],
dtype='float')
df.reset_index(inplace=True)
df.index += 1
df.index.name = "Period"
# for first ending balance
capitalisation_amt = round(capitalisationList_aftercurve[last_index], 2)
df.loc[1, "Principal Paid"] = -1 * np.ppmt(
InterestRate, 1, RepaymentTerm_Mos, capitalisation_amt)
df.loc[1, "Interest Paid"] = -1 * np.ipmt(
InterestRate, 1, RepaymentTerm_Mos, capitalisation_amt)
df.loc[1,
"Payment"] = df.loc[1, "Principal Paid"] + df.loc[1,
"Interest Paid"]
# calculating the first ending balance
#df["Ending Balance"]=0
Anticipated_Amts = Anticipated_Def_Amt[
last_index] + Anticipated_FullPrepay_Amt[
last_index] + Anticipated_PartialPrepay_Amt[last_index]
df.loc[1, "Ending Balance"] = capitalisation_amt - df.loc[
1, "Principal Paid"] - Anticipated_Amts
# rounding up
df = df.round(2)
# looping through to get the final ending balance to be zero
for period in range(2, len(df)):
previous_bal = df.loc[
period - 1, 'Ending Balance'] - Anticipated_Def_Amt[
last_index + (period - 1)] - Anticipated_FullPrepay_Amt[
last_index +
(period - 1)] - Anticipated_PartialPrepay_Amt[last_index +
(period - 1)]
principal_paid = df.loc[period, "Principal Paid"]
if previous_bal == 0 or previous_bal == "nan":
df.loc[period, [
'Payment', 'Principal Paid', 'Interest Paid', 'Ending Balance'
]] == 0
continue
elif principal_paid <= previous_bal:
df.loc[period, 'Ending Balance'] = previous_bal - principal_paid
# rounding up
df = df.round(2)
for _ in range(0, last_index + 1):
Curr_Balance.append("-")
Interest_repay.append("-")
Payment.append("-")
Principal.append("-")
# convert dataframe to lists and then append them to the above lists
inter_step_CB = df['Ending Balance'].to_list()
Curr_Balance.extend(inter_step_CB)
inter_step_int = df['Interest Paid'].to_list()
Interest_repay.extend(inter_step_int)
inter_step_pay = df['Payment'].to_list()
Payment.extend(inter_step_pay)
inter_step_prin = df['Principal Paid'].to_list()
Principal.extend(inter_step_prin)
df_table = pd.DataFrame(
np.column_stack([
final_dates, period_inStatus, status, disbAmtList,
cum_disb_aftercurve, interest_aftercurve, min_Pay_aftercurve,
PO_List, Anticipated_Def_Amt, Anticipated_PartialPrepay_Amt,
Anticipated_FullPrepay_Amt, capitalisationList_aftercurve,
Principal, Interest_repay, Payment, Curr_Balance
]),
columns=[
'Date', 'Period_inStatus', 'Status',
'Disbursement_Amount_inPeriod', 'Cummulative_Disbursement_Amount',
'Deferment_Grace_interest_Amount', 'Deferment_Grace_Min_Payment',
'Product_OfferingCode', 'Anticipated_Default_Amount',
'Anticipated_PartialPrepay_Amount',
'Anticipated_FullPrepay_Amount', 'capitalisation',
'Repayment_Principal_Payment', 'Repayment_Interest_Payment',
'Repayment_Total_Payment', 'Repayment_Principal_Paydown'
])
df_table = df_table.round(3)
output = df_table.to_dict('records')
return jsonify(output)
def test_ipmt(self):
assert_almost_equal(np.round(np.ipmt(0.1 / 12, 1, 24, 2000), 2),
-16.67)
def __init__(self, rate_type, amortizing_type, original_principal_balance,
principal_balance, issue_date, maturity_date, stub_date,
tenor, calendar, business_convention,
termination_business_convention, date_generation, month_end,
settlement_days, day_count, rate, prepay_curve, prepay_stress,
gross_cumulative_loss, loss_stress, loss_timing_curve,
sda_curve, recovery_rate, recovery_lag):
self.rate_type = rate_type
self.amortizing_type = amortizing_type
self.original_principal_balance = original_principal_balance
self.current_principal_balance = principal_balance
self.issue_date = issue_date
self.maturity_date = maturity_date
self.stub_date = stub_date
self.tenor = tenor
self.calendar = calendar
self.business_convention = business_convention
self.termination_business_convention = termination_business_convention
self.date_generation = date_generation
self.end_of_month = month_end
self.schedule = Schedule(self.issue_date, self.maturity_date,
self.tenor, self.calendar,
self.business_convention,
self.termination_business_convention,
self.date_generation, self.end_of_month,
self.stub_date)
self.settlement_days = settlement_days
self.day_count = day_count
self.rate = rate
self.prepay_curve = prepay_curve
self.prepay_stress = prepay_stress
self.gross_cumulative_loss = gross_cumulative_loss
self.loss_stress = loss_stress
self.loss_timing_curve = loss_timing_curve
self.sda_curve = sda_curve
self.recovery_rate = recovery_rate
self.recovery_lag = recovery_lag
self.notional_cashflow_interest = []
self.notional_cashflow_principal_amortization = []
self.notional_cashflow = []
self.cashflow_interest = []
self.cashflow_principal_amortization = []
self.cashflow_defaults = []
self.cashflow_prepayments = []
self.cashflow_principal_recovery = []
self.cashflow = []
self.ending_notional_principal_balance = []
self.beginning_notional_principal_balance = []
self.ending_principal_balance = []
self.beginning_principal_balance = []
self.cashflow_available_to_pay_liabilities = []
self.beginning_notional_principal_balance.append(0)
self.beginning_principal_balance.append(0)
self.ending_notional_principal_balance.append(
self.current_principal_balance)
self.ending_principal_balance.append(self.current_principal_balance)
my_per = int(self.get_original_term() * self.tenor.frequency())
for i, date in enumerate(list(self.schedule)):
if date != self.issue_date:
if self.rate_type == 'FixedRateBond':
my_rate = float(self.rate) / self.tenor.frequency()
elif self.rate_type == 'FloatingRateBond':
my_rate = float(self.rate[i]) / self.tenor.frequency()
# start with notional amortization schedule
self.beginning_notional_principal_balance.append(
self.ending_notional_principal_balance[-1])
if self.amortizing_type == 'FixedAmortizingBond':
my_prin = -np.asscalar(
np.ppmt(my_rate, i, my_per,
self.original_principal_balance))
my_int = -np.asscalar(
np.ipmt(my_rate, i, my_per,
self.original_principal_balance))
elif self.amortizing_type == 'FloatingAmortizingBond':
pmt = -np.asscalar(
np.pmt(my_rate, my_per,
self.original_principal_balance))
my_int = self.beginning_notional_principal_balance[
-1] * my_rate
my_prin = pmt - my_int
elif self.amortizing_type is None:
my_prin = 0
self.notional_cashflow_principal_amortization.append(
SimpleCashFlow(my_prin, date))
self.notional_cashflow_interest.append(
SimpleCashFlow(my_int, date))
self.notional_cashflow = self.notional_cashflow_principal_amortization + \
self.notional_cashflow_interest
self.ending_notional_principal_balance.append(
self.beginning_notional_principal_balance[-1] - my_prin)
# next is the actual amortization schedule
self.beginning_principal_balance.append(
self.ending_principal_balance[-1])
my_index = int(
(i - 1) /
self.tenor.frequency()) * self.tenor.frequency() + 1
my_default_rate = self.loss_timing_curve[my_index]*self.gross_cumulative_loss\
*self.loss_stress/self.tenor.frequency()
my_default = self.beginning_principal_balance[
1] * my_default_rate
self.cashflow_defaults.append(SimpleCashFlow(my_default, date))
my_prepay_rate = self.prepay_curve[i]
my_amort_factor = self.ending_notional_principal_balance[-1]/\
self.ending_notional_principal_balance[-2]
my_prepay = max(
min(
self.beginning_principal_balance[-1] - my_default,
self.beginning_principal_balance[-1] *
my_amort_factor * my_prepay_rate), 0)
self.cashflow_prepayments.append(
SimpleCashFlow(my_prepay, date))
if self.amortizing_type == 'FixedAmortizingBond' or self.amortizing_type == 'FloatingAmortizingBond':
my_prin = (self.beginning_principal_balance[-1] -
my_default) * (1 - my_amort_factor)
elif self.amortizing_type is None:
my_prin = 0
self.cashflow_principal_amortization.append(
SimpleCashFlow(my_prin, date))
my_int = my_rate * (self.beginning_principal_balance[-1] -
my_default)
self.cashflow_interest.append(SimpleCashFlow(my_int, date))
my_recovery = 0
my_recovery_start_date = self.calendar.advance(
list(self.schedule)[0], self.recovery_lag)
if date > my_recovery_start_date:
my_recovery = self.recovery_rate * self.cashflow_defaults[
-self.recovery_lag.length()].amount()
self.cashflow_principal_recovery.append(
SimpleCashFlow(my_recovery, date))
self.cashflow = self.cashflow_defaults + self.cashflow_prepayments + \
self.cashflow_principal_amortization + self.cashflow_interest + \
self.cashflow_principal_recovery
self.cashflow_available_to_pay_liabilities = self.cashflow_prepayments+\
self.cashflow_principal_amortization+\
self.cashflow_interest+\
self.cashflow_principal_recovery
self.ending_principal_balance.append(
self.ending_principal_balance[-1] - my_default -
my_prepay - my_prin)
self.instrument = Bond(self.settlement_days, self.calendar,
self.original_principal_balance,
self.maturity_date, self.issue_date,
self.notional_cashflow)
self.cashflow_dictionary = collections.OrderedDict([
("date", list(self.schedule)),
("beginning notional balance",
self.beginning_notional_principal_balance),
("notional interest",
self.translate_cashflow_to_list(self.notional_cashflow_interest)),
("notional principal amortization",
self.translate_cashflow_to_list(
self.notional_cashflow_principal_amortization)),
("ending notional balance",
self.ending_notional_principal_balance),
("beginning balance", self.beginning_principal_balance),
("defaults",
self.translate_cashflow_to_list(self.cashflow_defaults)),
("prepayments",
self.translate_cashflow_to_list(self.cashflow_prepayments)),
("principal amortization",
self.translate_cashflow_to_list(
self.cashflow_principal_amortization)),
("interest",
self.translate_cashflow_to_list(self.cashflow_interest)),
("principal recovery",
self.translate_cashflow_to_list(
self.cashflow_principal_recovery)),
("ending balance", self.ending_principal_balance),
("cash available to pay liabilities",
self.translate_cashflow_to_list(
self.cash_flow_available_to_pay_liabilities()))
])
def test_when(self):
# begin
assert_equal(np.rate(10, 20, -3500, 10000, 1),
np.rate(10, 20, -3500, 10000, 'begin'))
# end
assert_equal(np.rate(10, 20, -3500, 10000),
np.rate(10, 20, -3500, 10000, 'end'))
assert_equal(np.rate(10, 20, -3500, 10000, 0),
np.rate(10, 20, -3500, 10000, 'end'))
# begin
assert_equal(np.pv(0.07, 20, 12000, 0, 1),
np.pv(0.07, 20, 12000, 0, 'begin'))
# end
assert_equal(np.pv(0.07, 20, 12000, 0), np.pv(0.07, 20, 12000, 0,
'end'))
assert_equal(np.pv(0.07, 20, 12000, 0, 0),
np.pv(0.07, 20, 12000, 0, 'end'))
# begin
assert_equal(np.fv(0.075, 20, -2000, 0, 1),
np.fv(0.075, 20, -2000, 0, 'begin'))
# end
assert_equal(np.fv(0.075, 20, -2000, 0),
np.fv(0.075, 20, -2000, 0, 'end'))
assert_equal(np.fv(0.075, 20, -2000, 0, 0),
np.fv(0.075, 20, -2000, 0, 'end'))
# begin
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0, 1),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'begin'))
# end
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'end'))
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0, 0),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'end'))
# begin
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 1),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'begin'))
# end
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'end'))
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 0),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'end'))
# begin
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 1),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'begin'))
# end
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'end'))
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 0),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'end'))
# begin
assert_equal(np.nper(0.075, -2000, 0, 100000., 1),
np.nper(0.075, -2000, 0, 100000., 'begin'))
# end
assert_equal(np.nper(0.075, -2000, 0, 100000.),
np.nper(0.075, -2000, 0, 100000., 'end'))
assert_equal(np.nper(0.075, -2000, 0, 100000., 0),
np.nper(0.075, -2000, 0, 100000., 'end'))
import numpy as np print(np.fv(0.02, 20 * 12, -100, -100)) print(np.pv(0.02, 20 * 12, -100, 586032.549)) print(np.npv(0.281, [-100, 823, 59, 55, 20])) print(np.pmt(0.02 / 12, 20 * 12, 1000000)) print(np.ppmt(0.02 / 12, -100, 20 * 12, 1000000)) print(np.ipmt(0.02 / 12, 0.001, 20 * 12, 200000)) print(round(np.irr([-5, 10.5, 1, -8, 1]), 5)) print(np.mirr([-100, 823, 59, 55, 20], -100, -150)) print(np.nper(0.02, -1e7, 1e8)) print(np.rate(0.7, -1e8, 200000, 0))
def loan_calculator(customerdf1):
loan_amt = float(input("Enter the loan amount : "))
# 20% of the balance should be kept in the bank
balance = int(customerdf1["Balance"].head(1)) - int(
customerdf1["Balance"].head(1)) * 20 / 100
if balance < 7000:
print("Initial balance that should be kept is less than 7000")
return
loan_bal_amt = loan_amt - balance
if loan_bal_amt < 0:
print("Loan Balance Amount is : {}. No Need to take loan".format(
loan_bal_amt))
return
else:
print("Loan Balance Amount is : {}".format(loan_bal_amt))
# Check whether the loan amount fits in which criteria of the bank
bankdf_min = bank.loc[bank['Loan Amount Min'] <= loan_bal_amt].tail(1)
bankdf_max = bank.loc[bank['Loan Amount Max'] >= loan_bal_amt].head(1)
if bankdf_min['SrNo'].values in bankdf_max['SrNo'].values:
interest_rate = float(bankdf_min["Interest Rate"].values)
no_of_years = int(bankdf_min["Loan Term"].values)
print("Interest rate is: {}".format(interest_rate))
print("Number of years are: {}".format(no_of_years))
num_payments_per_yr = 12
# Date format conversion
date_entry = input("Enter the loan start date in format 'YYYY-MM-DD' :")
date_input = datetime.datetime.strptime(date_entry, '%Y-%m-%d').date()
# Avoid previous years date
if date_input < date.today():
print("Date: {} is invalid ".format("date_input"))
return
print("Date picked is : {}".format(date_input))
year, month, day = map(int, date_entry.split('-'))
start_date = date(year, month, day)
# print(start_date)
# interest_rate=interest/100
adj_interest_rate = interest_rate / num_payments_per_yr
print("Adjust Interest rate : {}".format(adj_interest_rate))
num_periods = no_of_years * num_payments_per_yr
# Define the payment dates using a date_range object
rng = pd.date_range(start_date, periods=num_periods, freq='M')
rng.name = 'Payment Date'
# Monthly payment
pmt = round(np.pmt(adj_interest_rate, num_periods, loan_bal_amt), 2)
print(pmt)
print("========================")
# Creating a schedule
beginning_bal = loan_bal_amt
principal = round(
np.ppmt(pv=loan_bal_amt,
rate=adj_interest_rate,
nper=num_periods,
per=num_periods,
fv=0), 2)
interest = np.ipmt(pv=loan_bal_amt,
rate=adj_interest_rate,
nper=num_periods,
per=num_periods,
fv=0)
ending_bal = np.pv(fv=0, pmt=pmt, rate=adj_interest_rate, nper=0)
records = []
end_bal = loan_bal_amt
for i in range(1, len(rng) + 1):
bgn_bal = end_bal
principal = np.ppmt(pv=loan_bal_amt,
rate=adj_interest_rate,
nper=num_periods,
per=i,
fv=0)
interest = float(
np.ipmt(pv=loan_bal_amt,
rate=adj_interest_rate,
nper=num_periods,
per=i,
fv=0))
end_bal = np.pv(fv=0,
pmt=pmt,
rate=adj_interest_rate,
nper=len(rng) - i)
records.append((bgn_bal, -pmt, -principal, -interest, end_bal))
# Creating a dataframe
columns = [
'Beginning Balance', 'Payment', 'Principal', 'Interest',
'Ending Balance'
]
amortisation_df = pd.DataFrame.from_records(records,
index=rng,
columns=columns).round(2)
# writting to excel
export_excel = amortisation_df.to_excel(
r'C:\Users\Kalyani\Desktop\Python\Loan Evaluation and Amortization\Customer_Amortisation.xlsx',
header=True)
print(amortisation_df.head(5))
print(amortisation_df.tail(5))
# Creating a graph
# Customer Transaction History Details
plt.scatter(x=customerdf1['Transaction Date'],
y=customerdf1['Balance'],
label="Amount",
color="green",
marker="^",
s=30)
plt.xlabel('Transaction Date')
plt.ylabel('Balance')
plt.title('Customer Transaction History')
plt.legend()
# loan payment calculation
amortisation_df.plot(y='Ending Balance', grid=True, figsize=(30, 10))
graph_df = amortisation_df.groupby(by=amortisation_df.index.year).last()
graph_df.plot(y=['Principal', 'Interest'],
kind='bar',
stacked=True,
figsize=(30, 10),
fontsize=20).legend(loc=2, prop={'size': 20})
numpy.ppmt(rate, per, nper, pv, fv=0, when='end') 引数 rate (必須): 利率 nper (必須): 複利計算期間数 (支払回数) per (必須): 複利計算期間数 (支払回数) pv (必須): 現在価値 (返済の場合: 借入金額, 貯蓄の場合: 積立済金額) fv : 将来価値 (返済の場合: 0, 貯蓄の場合: 積立目標額) when: 支払期日、いつ支払いが行われるか。 (end/0: 各期の期末, start/1: 各期の機種) 使用例 年利 0.8% の利率で 35 年間、3000 万円のローンを支払うのに必要な毎月の費用の元本部分 (12, 24, 36, 48 か月目) ''' print(np.ppmt(0.008 / 12, [12, 24, 36, 48], 12 * 35, -3000 * 10000)) # In[7]: ''' 利子を考慮したローンの月々の支払額 (利息部分のみ): IPMT np.ipmt() にて、一定利率のローンの定期支払額の利息部分のみを求めます。 Excel には、IPMT 関数として実装されています。 使い方 Python 1 numpy.ipmt(rate, per, nper, pv, fv=0, when='end') 引数 rate (必須): 利率 nper (必須): 複利計算期間数 (支払回数) pv (必須): 現在価値 (返済の場合: 借入金額, 貯蓄の場合: 積立済金額) fv : 将来価値 (返済の場合: 0, 貯蓄の場合: 積立目標額)
def nopayamort(principal, interest, months):
"""Calculate total interest paid if no extra principal payments made."""
per = np.arange(1 * months) + 1
ipmt = np.ipmt(interest, per, 1 * months, principal)
total_interest = abs(np.sum(ipmt))
return total_interest
def test_ipmt_decimal(self):
result = np.ipmt(Decimal('0.1') / Decimal('12'), 1, 24, 2000)
assert_equal(result.flat[0], Decimal('-16.66666666666666666666666667'))
Rate_Levelized_Direct = -NPVLoanDirect/NPVallYearGenerationMWh
#Master Output [Direct Loan]
outData["levelCostDirect"] = Rate_Levelized_Direct
outData["costPanelDirect"] = abs(NPVLoanDirect/numberPanels)
outData["cost10WPanelDirect"] = (float(outData["costPanelDirect"])/panelSize)*10
### NCREBs Financing
ncrebsRate = float(inputDict.get("NCREBRate",4.060))/100
ncrebBorrowingRate = 1.1 * ncrebsRate
ncrebPaymentPeriods = 44
ncrebCostToCustomer = []
# TODO ASAP: FIX ARRAY OFFSETS START 0
for i in range (1, len(outData["allYearGenerationMWh"])+1):
coopLoanPayment = 2 * pmt(ncrebBorrowingRate/2.0, ncrebPaymentPeriods, outData["totalCost"]) if i <= ncrebPaymentPeriods / 2 else 0
ncrebsCredit = -0.7 * (ipmt(ncrebsRate / 2, 2 * i - 1, ncrebPaymentPeriods, outData["totalCost"])
+ ipmt(ncrebsRate / 2, 2 * i, ncrebPaymentPeriods, outData["totalCost"])) if i <= ncrebPaymentPeriods / 2 else 0
financingCost = ncrebsCredit + coopLoanPayment
omCost = OMInsuranceETCDirect[i - 1]
netCoopPayments = financingCost + omCost
distrAdder = distAdderDirect[i - 1]
costToCustomer = netCoopPayments + distrAdder
ncrebCostToCustomer.append(costToCustomer)
NPVLoanNCREB = npv(float(inputDict.get("discRate", 0))/100, [0,0] + ncrebCostToCustomer)
Rate_Levelized_NCREB = -NPVLoanNCREB/NPVallYearGenerationMWh
outData["levelCostNCREB"] = Rate_Levelized_NCREB
outData["costPanelNCREB"] = abs(NPVLoanNCREB/numberPanels)
outData["cost10WPanelNCREB"] = (float(outData["costPanelNCREB"])/panelSize)*10
### Lease Buyback Structure
#Output - Lease [C]
def test_decimal_with_when(self):
"""Test that decimals are still supported if the when argument is passed"""
# begin
assert_equal(
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000'), Decimal('1')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000'), 'begin'))
# end
assert_equal(
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000'), 'end'))
assert_equal(
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000'), Decimal('0')),
np.rate(Decimal('10'), Decimal('20'), Decimal('-3500'),
Decimal('10000'), 'end'))
# begin
assert_equal(
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0'), Decimal('1')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0'), 'begin'))
# end
assert_equal(
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0'), 'end'))
assert_equal(
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0'), Decimal('0')),
np.pv(Decimal('0.07'), Decimal('20'), Decimal('12000'),
Decimal('0'), 'end'))
# begin
assert_equal(
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0'), Decimal('1')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0'), 'begin'))
# end
assert_equal(
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0'), 'end'))
assert_equal(
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0'), Decimal('0')),
np.fv(Decimal('0.075'), Decimal('20'), Decimal('-2000'),
Decimal('0'), 'end'))
# begin
assert_equal(
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0'),
Decimal('1')),
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0'),
'begin'))
# end
assert_equal(
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0')),
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0'),
'end'))
assert_equal(
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0'),
Decimal('0')),
np.pmt(
Decimal('0.08') / Decimal('12'),
Decimal('5') * Decimal('12'), Decimal('15000.'), Decimal('0'),
'end'))
# begin
assert_equal(
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0'), Decimal('1')),
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0'), 'begin'))
# end
assert_equal(
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0')),
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0'), 'end'))
assert_equal(
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0'), Decimal('0')),
np.ppmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('60'),
Decimal('55000'), Decimal('0'), 'end'))
# begin
assert_equal(
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0'), Decimal('1')).flat[0],
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0'), 'begin').flat[0])
# end
assert_equal(
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0')).flat[0],
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0'), 'end').flat[0])
assert_equal(
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0'), Decimal('0')).flat[0],
np.ipmt(
Decimal('0.1') / Decimal('12'), Decimal('1'), Decimal('24'),
Decimal('2000'), Decimal('0'), 'end').flat[0])
def calc_total_interest(self, rate, closing_balance, pmt, nper):
per = np.arange(nper) + 1
ipmt = -np.ipmt(rate/12/100.00, per, nper, closing_balance)
totalinterest = np.sum(ipmt)
totalinterest = round(totalinterest, 2)
return totalinterest
def test_ipmt(self):
np.round(np.ipmt(0.1/12,1,24,2000),2) == 16.67
def work(modelDir, inputDict):
''' Run the model in its directory. '''
#Set static input data
simLength = 8760
simStartDate = "2013-01-01"
# Set the timezone to be UTC, it won't affect calculation and display, relative offset handled in pvWatts.html
startDateTime = simStartDate + " 00:00:00 UTC"
simLengthUnits = "hours"
# Associate zipcode to climate data
inputDict["climateName"] = zipCodeToClimateName(inputDict["zipCode"])
inverterSizeAC = float(inputDict.get("inverterSize",0))
if (inputDict.get("systemSize",0) == "-"):
arraySizeDC = 1.3908 * inverterSizeAC
else:
arraySizeDC = float(inputDict.get("systemSize",0))
numberPanels = (arraySizeDC * 1000/305)
# Set constants
panelSize = 305
trackingMode = 0
rotlim = 45.0
gamma = 0.45
if (inputDict.get("tilt",0) == "-"):
tilt_eq_lat = 1.0
manualTilt = 0.0
else:
tilt_eq_lat = 0.0
manualTilt = float(inputDict.get("tilt",0))
numberInverters = math.ceil(inverterSizeAC/1000/0.5)
# Copy specific climate data into model directory
shutil.copy(pJoin(__neoMetaModel__._omfDir, "data", "Climate", inputDict["climateName"] + ".tmy2"),
pJoin(modelDir, "climate.tmy2"))
# Set up SAM data structures.
ssc = nrelsam2013.SSCAPI()
dat = ssc.ssc_data_create()
# Required user inputs.
ssc.ssc_data_set_string(dat, "file_name", modelDir + "/climate.tmy2")
ssc.ssc_data_set_number(dat, "system_size", arraySizeDC)
ssc.ssc_data_set_number(dat, "derate", float(inputDict.get("inverterEfficiency", 96))/100 * float(inputDict.get("nonInverterEfficiency", 87))/100)
ssc.ssc_data_set_number(dat, "track_mode", float(trackingMode))
ssc.ssc_data_set_number(dat, "azimuth", float(inputDict.get("azimuth", 180)))
# Advanced inputs with defaults.
ssc.ssc_data_set_number(dat, "rotlim", float(rotlim))
ssc.ssc_data_set_number(dat, "gamma", float(-gamma/100))
ssc.ssc_data_set_number(dat, "tilt", manualTilt)
ssc.ssc_data_set_number(dat, "tilt_eq_lat", 0.0)
# Run PV system simulation.
mod = ssc.ssc_module_create("pvwattsv1")
ssc.ssc_module_exec(mod, dat)
# Timestamp output.
outData = {}
outData["timeStamps"] = [dt.datetime.strftime(
dt.datetime.strptime(startDateTime[0:19],"%Y-%m-%d %H:%M:%S") +
dt.timedelta(**{simLengthUnits:x}),"%Y-%m-%d %H:%M:%S") + " UTC" for x in range(simLength)]
# Geodata output.
outData["minLandSize"] = round((arraySizeDC/1390.8*5 + 1)*math.cos(math.radians(22.5))/math.cos(math.radians(30.0)),0)
landAmount = float(inputDict.get("landAmount", 6.0))
outData["city"] = ssc.ssc_data_get_string(dat, "city")
outData["state"] = ssc.ssc_data_get_string(dat, "state")
outData["lat"] = ssc.ssc_data_get_number(dat, "lat")
outData["lon"] = ssc.ssc_data_get_number(dat, "lon")
outData["elev"] = ssc.ssc_data_get_number(dat, "elev")
# Weather output.
outData["climate"] = {}
outData["climate"]["Global Horizontal Radiation (W/m^2)"] = ssc.ssc_data_get_array(dat, "gh")
outData["climate"]["Plane of Array Irradiance (W/m^2)"] = ssc.ssc_data_get_array(dat, "poa")
outData["climate"]["Ambient Temperature (F)"] = ssc.ssc_data_get_array(dat, "tamb")
outData["climate"]["Cell Temperature (F)"] = ssc.ssc_data_get_array(dat, "tcell")
outData["climate"]["Wind Speed (m/s)"] = ssc.ssc_data_get_array(dat, "wspd")
# Power generation.
outData["powerOutputAc"] = ssc.ssc_data_get_array(dat, "ac")
# Calculate clipping.
outData["powerOutputAc"] = ssc.ssc_data_get_array(dat, "ac")
invSizeWatts = inverterSizeAC * 1000
outData["powerOutputAcInvClipped"] = [x if x < invSizeWatts else invSizeWatts for x in outData["powerOutputAc"]]
try:
outData["percentClipped"] = 100 * (1.0 - sum(outData["powerOutputAcInvClipped"]) / sum(outData["powerOutputAc"]))
except ZeroDivisionError:
outData["percentClipped"] = 0.0
#One year generation
outData["oneYearGenerationWh"] = sum(outData["powerOutputAcInvClipped"])
#Annual generation for all years
loanYears = 25
outData["allYearGenerationMWh"] = {}
outData["allYearGenerationMWh"][1] = float(outData["oneYearGenerationWh"])/1000000
# outData["allYearGenerationMWh"][1] = float(2019.576)
for i in range (2, loanYears+1):
outData["allYearGenerationMWh"][i] = float(outData["allYearGenerationMWh"][i-1]) * (1 - float(inputDict.get("degradation", 0.8))/100)
# Summary of Results.
######
### Total Costs (sum of): Hardware Costs, Design/Engineering/PM/EPC/Labor Costs, Siteprep Costs, Construction Costs, Installation Costs, Land Costs
######
### Hardware Costs
pvModules = arraySizeDC * float(inputDict.get("moduleCost",0))*1000 #off by 4000
racking = arraySizeDC * float(inputDict.get("rackCost",0))*1000
inverters = numberInverters * float(inputDict.get("inverterCost",0))
inverterSize = inverterSizeAC
if (inverterSize <= 250):
gear = 15000
elif (inverterSize <= 600):
gear = 18000
else:
gear = inverterSize/1000 * 22000
balance = inverterSizeAC * 1.3908 * 134
combiners = math.ceil(numberPanels/19/24) * float(1800) #*
wireManagement = arraySizeDC * 1.5
transformer = 1 * 28000
weatherStation = 1 * 12500
shipping = 1.02
hardwareCosts = (pvModules + racking + inverters + gear + balance + combiners + wireManagement + transformer + weatherStation) * shipping
### Design/Engineering/PM/EPC/Labor Costs
EPCmarkup = float(inputDict.get("EPCRate",0))/100 * hardwareCosts
#designCosts = float(inputDict.get("mechLabor",0))*160 + float(inputDict.get("elecLabor",0))*75 + float(inputDict.get("pmCost",0)) + EPCmarkup
hoursDesign = 160*math.sqrt(arraySizeDC/1390)
hoursElectrical = 80*math.sqrt(arraySizeDC/1391)
designLabor = 65*hoursDesign
electricalLabor = 75*hoursElectrical
laborDesign = designLabor + electricalLabor + float(inputDict.get("pmCost",0)) + EPCmarkup
materialDesign = 0
designCosts = materialDesign + laborDesign
### Siteprep Costs
surveying = 2.25 * 4 * math.sqrt(landAmount*43560)
concrete = 8000 * math.ceil(numberInverters/2)
fencing = 6.75 * 4 * math.sqrt(landAmount*43560)
grading = 2.5 * 4 * math.sqrt(landAmount*43560)
landscaping = 750 * landAmount
siteMaterial = 8000 + 600 + 5500 + 5000 + surveying + concrete + fencing + grading + landscaping + 5600
blueprints = float(inputDict.get("mechLabor",0))*12
mobilization = float(inputDict.get("mechLabor",0))*208
mobilizationMaterial = float(inputDict.get("mechLabor",0))*19.98
siteLabor = blueprints + mobilization + mobilizationMaterial
sitePrep = siteMaterial + siteLabor
### Construction Costs (Office Trailer, Skid Steer, Storage Containers, etc)
constrEquip = 6000 + math.sqrt(landAmount)*16200
### Installation Costs
moduleAndRackingInstall = numberPanels * (15.00 + 12.50 + 1.50)
pierDriving = 1 * arraySizeDC*20
balanceInstall = 1 * arraySizeDC*100
installCosts = moduleAndRackingInstall + pierDriving + balanceInstall + float(inputDict.get("elecLabor",0)) * (72 + 60 + 70 + 10 + 5 + 30 + 70)
### Land Costs
if (str(inputDict.get("landOwnership",0)) == "Owned" or (str(inputDict.get("landOwnership",0)) == "Leased")):
landCosts = 0
else:
landCosts = float(inputDict.get("costAcre",0))*landAmount
######
### Total Costs
######
totalCosts = hardwareCosts + designCosts + sitePrep + constrEquip + installCosts + landCosts
totalFees= float(inputDict.get("devCost",0))/100 * totalCosts
outData["totalCost"] = totalCosts + totalFees + float(inputDict.get("interCost",0))
# Add to Pie Chart
outData["costsPieChart"] = [["Land", landCosts],
["Design/Engineering/PM/EPC", designCosts],
["PV Modules", pvModules*shipping],
["Racking", racking*shipping],
["Inverters & Switchgear", (inverters+gear)*shipping],
["BOS", hardwareCosts - pvModules*shipping - racking*shipping - (inverters+gear)*shipping],
["Site Prep, Constr. Eq. and Installation", (siteMaterial + constrEquip) + (siteLabor + installCosts)]]
# Cost per Wdc
outData["costWdc"] = (totalCosts + totalFees + float(inputDict.get("interCost",0))) / (arraySizeDC * 1000)
outData["capFactor"] = float(outData["oneYearGenerationWh"])/(inverterSizeAC*1000*365.25*24) * 100
######
### Loans calculations for Direct, NCREB, Lease, Tax-equity, and PPA
######
### Full Ownership, Direct Loan
#Output - Direct Loan [C]
projectCostsDirect = 0
#Output - Direct Loan [D]
netFinancingCostsDirect = 0
#Output - Direct Loan [E]
OMInsuranceETCDirect = []
#Output - Direct Loan [F]
distAdderDirect = []
#Output - Direct Loan [G]
netCoopPaymentsDirect = []
#Output - Direct Loan [H]
costToCustomerDirect = []
#Output - Direct Loan [F53]
Rate_Levelized_Direct = 0
## Output - Direct Loan Formulas
projectCostsDirect = 0
#Output - Direct Loan [D]
payment = pmt(float(inputDict.get("loanRate",0))/100, loanYears, outData["totalCost"])
interestDirectPI = outData["totalCost"] * float(inputDict.get("loanRate",0))/100
principleDirectPI = (-payment - interestDirectPI)
patronageCapitalRetiredDPI = 0
netFinancingCostsDirect = -(principleDirectPI + interestDirectPI - patronageCapitalRetiredDPI)
#Output - Direct Loan [E] [F] [G] [H]
firstYearOPMainCosts = (1.25 * arraySizeDC * 12)
firstYearInsuranceCosts = (0.37 * outData["totalCost"]/100)
if (inputDict.get("landOwnership",0) == "Leased"):
firstYearLandLeaseCosts = float(inputDict.get("costAcre",0))*landAmount
else:
firstYearLandLeaseCosts = 0
for i in range (1, len(outData["allYearGenerationMWh"])+1):
OMInsuranceETCDirect.append(-firstYearOPMainCosts*math.pow((1 + .01),(i-1)) - firstYearInsuranceCosts*math.pow((1 + .025),(i-1)) - firstYearLandLeaseCosts*math.pow((1 + .01),(i-1)))
distAdderDirect.append(float(inputDict.get("distAdder",0))*outData["allYearGenerationMWh"][i])
netCoopPaymentsDirect.append(OMInsuranceETCDirect[i-1] + netFinancingCostsDirect)
costToCustomerDirect.append((netCoopPaymentsDirect[i-1] - distAdderDirect[i-1]))
#Output - Direct Loan [F53]
NPVLoanDirect = npv(float(inputDict.get("discRate",0))/100, [0,0] + costToCustomerDirect)
NPVallYearGenerationMWh = npv(float(inputDict.get("discRate",0))/100, [0,0] + outData["allYearGenerationMWh"].values())
Rate_Levelized_Direct = -NPVLoanDirect/NPVallYearGenerationMWh
#Master Output [Direct Loan]
outData["levelCostDirect"] = Rate_Levelized_Direct
outData["costPanelDirect"] = abs(NPVLoanDirect/numberPanels)
outData["cost10WPanelDirect"] = (float(outData["costPanelDirect"])/panelSize)*10
### NCREBs Financing
ncrebsRate = float(inputDict.get("NCREBRate",4.060))/100
ncrebBorrowingRate = 1.1 * ncrebsRate
ncrebPaymentPeriods = 44
ncrebCostToCustomer = []
# TODO ASAP: FIX ARRAY OFFSETS START 0
for i in range (1, len(outData["allYearGenerationMWh"])+1):
coopLoanPayment = 2 * pmt(ncrebBorrowingRate/2.0, ncrebPaymentPeriods, outData["totalCost"]) if i <= ncrebPaymentPeriods / 2 else 0
ncrebsCredit = -0.7 * (ipmt(ncrebsRate / 2, 2 * i - 1, ncrebPaymentPeriods, outData["totalCost"])
+ ipmt(ncrebsRate / 2, 2 * i, ncrebPaymentPeriods, outData["totalCost"])) if i <= ncrebPaymentPeriods / 2 else 0
financingCost = ncrebsCredit + coopLoanPayment
omCost = OMInsuranceETCDirect[i - 1]
netCoopPayments = financingCost + omCost
distrAdder = distAdderDirect[i - 1]
costToCustomer = netCoopPayments + distrAdder
ncrebCostToCustomer.append(costToCustomer)
NPVLoanNCREB = npv(float(inputDict.get("discRate", 0))/100, [0,0] + ncrebCostToCustomer)
Rate_Levelized_NCREB = -NPVLoanNCREB/NPVallYearGenerationMWh
outData["levelCostNCREB"] = Rate_Levelized_NCREB
outData["costPanelNCREB"] = abs(NPVLoanNCREB/numberPanels)
outData["cost10WPanelNCREB"] = (float(outData["costPanelNCREB"])/panelSize)*10
### Lease Buyback Structure
#Output - Lease [C]
projectCostsLease = outData["totalCost"]
#Output - Lease [D]
leasePaymentsLease = []
#Output - Lease [E]
OMInsuranceETCLease = OMInsuranceETCDirect
#Output - Lease [F]
distAdderLease = distAdderDirect
#Output - Lease [G]
netCoopPaymentsLease = []
#Output - Lease [H]
costToCustomerLease = []
#Output - Lease [H44]
NPVLease = 0
#Output - Lease [H49]
Rate_Levelized_Lease = 0
## Tax Lease Formulas
#Output - Lease [D]
for i in range (0, 12):
leaseRate = float(inputDict.get("taxLeaseRate",0))/100.0
if i>8: # Special behavior in later years:
leaseRate = leaseRate - 0.0261
leasePaymentsLease.append(-1*projectCostsLease/((1.0-(1.0/(1.0+leaseRate)**12))/(leaseRate)))
# Last year is different.
leasePaymentsLease[11] += -0.2*projectCostsLease
for i in range (12, 25):
leasePaymentsLease.append(0)
#Output - Lease [G] [H]
for i in range (1, len(outData["allYearGenerationMWh"])+1):
netCoopPaymentsLease.append(OMInsuranceETCLease[i-1]+leasePaymentsLease[i-1])
costToCustomerLease.append(netCoopPaymentsLease[i-1]-distAdderLease[i-1])
#Output - Lease [H44]. Note the extra year at the zero point to get the discounting right.
NPVLease = npv(float(inputDict.get("discRate", 0))/100, [0,0]+costToCustomerLease)
#Output - Lease [H49] (Levelized Cost Three Loops)
Rate_Levelized_Lease = -NPVLease/NPVallYearGenerationMWh
#Master Output [Lease]
outData["levelCostTaxLease"] = Rate_Levelized_Lease
outData["costPanelTaxLease"] = abs(NPVLease/numberPanels)
outData["cost10WPanelTaxLease"] = (float(outData["costPanelTaxLease"])/float(panelSize))*10
### Tax Equity Flip Structure
# Tax Equity Flip Function
def taxEquityFlip(PPARateSixYearsTE, discRate, totalCost, allYearGenerationMWh, distAdderDirect, loanYears, firstYearLandLeaseCosts, firstYearOPMainCosts, firstYearInsuranceCosts, numberPanels):
#Output Tax Equity Flip [C]
coopInvestmentTaxEquity = -totalCost*(1-0.53)
#Output Tax Equity Flip [D]
financeCostCashTaxEquity = 0
#Output Tax Equity Flip [E]
cashToSPEOForPPATE = []
#Output Tax Equity Flip [F]
derivedCostEnergyTE = 0
#Output Tax Equity Flip [G]
OMInsuranceETCTE = []
#Output Tax Equity Flip [H]
cashFromSPEToBlockerTE = []
#Output Tax Equity Flip [I]
cashFromBlockerTE = 0
#Output Tax Equity Flip [J]
distAdderTaxEquity = distAdderDirect
#Output Tax Equity Flip [K]
netCoopPaymentsTaxEquity = []
#Output Tax Equity Flip [L]
costToCustomerTaxEquity = []
#Output Tax Equity Flip [L64]
NPVLoanTaxEquity = 0
#Output Tax Equity Flip [F72]
Rate_Levelized_Equity = 0
## Tax Equity Flip Formulas
#Output Tax Equity Flip [D]
#TEI Calcs [E]
financeCostOfCashTE = 0
coopFinanceRateTE = 2.7/100
if (coopFinanceRateTE == 0):
financeCostOfCashTE = 0
else:
payment = pmt(coopFinanceRateTE, loanYears, -coopInvestmentTaxEquity)
financeCostCashTaxEquity = payment
#Output Tax Equity Flip [E]
SPERevenueTE = []
for i in range (1, len(allYearGenerationMWh)+1):
SPERevenueTE.append(PPARateSixYearsTE * allYearGenerationMWh[i])
if ((i>=1) and (i<=6)):
cashToSPEOForPPATE.append(-SPERevenueTE[i-1])
else:
cashToSPEOForPPATE.append(0)
#Output Tax Equity Flip [F]
derivedCostEnergyTE = cashToSPEOForPPATE[0]/allYearGenerationMWh[1]
#Output Tax Equity Flip [G]
#TEI Calcs [F] [U] [V]
landLeaseTE = []
OMTE = []
insuranceTE = []
for i in range (1, len(allYearGenerationMWh)+1):
landLeaseTE.append(firstYearLandLeaseCosts*math.pow((1 + .01),(i-1)))
OMTE.append(-firstYearOPMainCosts*math.pow((1 + .01),(i-1)))
insuranceTE.append(- firstYearInsuranceCosts*math.pow((1 + .025),(i-1)) )
if (i<7):
OMInsuranceETCTE.append(float(landLeaseTE[i-1]))
else:
OMInsuranceETCTE.append(float(OMTE[i-1]) + float(insuranceTE[i-1]) + float(landLeaseTE[i-1]))
#Output Tax Equity Flip [H]
#TEI Calcs [T]
SPEMgmtFeeTE = []
EBITDATE = []
EBITDATEREDUCED = []
managementFee = 10000
for i in range (1, len(SPERevenueTE)+1):
SPEMgmtFeeTE.append(-managementFee*math.pow((1 + .01),(i-1)))
EBITDATE.append(float(SPERevenueTE[i-1]) + float(OMTE[i-1]) + float(insuranceTE[i-1]) + float(SPEMgmtFeeTE[i-1]))
if (i<=6):
cashFromSPEToBlockerTE.append(float(EBITDATE[i-1]) * .01)
else:
cashFromSPEToBlockerTE.append(0)
EBITDATEREDUCED.append(EBITDATE[i-1])
#Output Tax Equity Flip [I]
#TEI Calcs [Y21]
cashRevenueTE = -totalCost * (1 - 0.53)
buyoutAmountTE = 0
for i in range (1, len(EBITDATEREDUCED) + 1):
buyoutAmountTE = buyoutAmountTE + EBITDATEREDUCED[i-1]/(math.pow(1+0.12,i))
buyoutAmountTE = buyoutAmountTE * 0.05
cashFromBlockerTE = - (buyoutAmountTE) + 0.0725 * cashRevenueTE
#Output Tax Equity Flip [K] [L]
for i in range (1, len(allYearGenerationMWh)+1):
if (i==6):
netCoopPaymentsTaxEquity.append(financeCostCashTaxEquity + cashToSPEOForPPATE[i-1] + cashFromSPEToBlockerTE[i-1] + OMInsuranceETCTE[i-1] + cashFromBlockerTE)
else:
netCoopPaymentsTaxEquity.append(financeCostCashTaxEquity + cashFromSPEToBlockerTE[i-1] + cashToSPEOForPPATE[i-1] + OMInsuranceETCTE[i-1])
costToCustomerTaxEquity.append(netCoopPaymentsTaxEquity[i-1] - distAdderTaxEquity[i-1])
#Output Tax Equity Flip [L37]
NPVLoanTaxEquity = npv(float(inputDict.get("discRate",0))/100, [0, 0] + costToCustomerTaxEquity)
#Output - Tax Equity [F42]
Rate_Levelized_TaxEquity = -NPVLoanTaxEquity/NPVallYearGenerationMWh
#TEI Calcs - Achieved Return [AW 21]
#[AK]
MACRDepreciation = []
MACRDepreciation.append(-0.99*0.2*(totalCost-totalCost*0.5*0.9822*0.3))
MACRDepreciation.append(-0.99*0.32*(totalCost-totalCost*0.5*0.9822*0.3))
MACRDepreciation.append(-0.99*0.192*(totalCost-totalCost*0.5*0.9822*0.3))
MACRDepreciation.append(-0.99*0.1152*(totalCost-totalCost*0.5*0.9822*0.3))
MACRDepreciation.append(-0.99*0.1152*(totalCost-totalCost*0.5*0.9822*0.3))
MACRDepreciation.append(-0.99*0.0576*(totalCost-totalCost*0.5*0.9822*0.3))
#[AI] [AL] [AN]
cashRevenueTEI = [] #[AI]
slDepreciation = [] #[AL]
totalDistributions = [] #[AN]
cashRevenueTEI.append(-totalCost*0.53)
for i in range (1,7):
cashRevenueTEI.append(EBITDATE[i-1]*0.99)
slDepreciation.append(totalCost/25)
totalDistributions.append(-cashRevenueTEI[i])
#[AJ]
ITC = totalCost*0.9822*0.3*0.99
#[AM]
taxableIncLoss = [0]
taxableIncLoss.append(cashRevenueTEI[1]+MACRDepreciation[0])
#[AO]
capitalAcct = []
capitalAcct.append(totalCost*0.53)
condition = capitalAcct[0] - 0.5*ITC + taxableIncLoss[1] + totalDistributions[0]
if condition > 0:
capitalAcct.append(condition)
else:
capitalAcct.append(0)
#[AQ]
ratioTE = [0]
#[AP]
reallocatedIncLoss = []
#AO-1 + AN + AI + AK + AJ
for i in range (0, 5):
reallocatedIncLoss.append(capitalAcct[i+1] + totalDistributions[i+1] + MACRDepreciation[i+1] + cashRevenueTEI[i+2])
ratioTE.append(reallocatedIncLoss[i]/(cashRevenueTEI[i+2] + MACRDepreciation[i+1]))
taxableIncLoss.append(cashRevenueTEI[i+2]+MACRDepreciation[i+1]-ratioTE[i+1]*(MACRDepreciation[i+1]-totalDistributions[i+1]))
condition = capitalAcct[i+1] + taxableIncLoss[i+2] + totalDistributions[i+1]
if condition > 0:
capitalAcct.append(condition)
else:
capitalAcct.append(0)
#[AR]
taxesBenefitLiab = [0]
for i in range (1, 7):
taxesBenefitLiab.append(-taxableIncLoss[i]*0.35)
#[AS] [AT]
buyoutAmount = 0
taxFromBuyout = 0
for i in range (0, len(EBITDATEREDUCED)):
buyoutAmount = buyoutAmount + .05*EBITDATEREDUCED[i]/(math.pow(1.12,(i+1)))
taxFromBuyout = -buyoutAmount*0.35
#[AU] [AV]
totalCashTax = []
cumulativeCashTax = [0]
for i in range (0, 7):
if i == 1:
totalCashTax.append(cashRevenueTEI[i] + ITC + taxesBenefitLiab[i] + 0 + 0)
cumulativeCashTax.append(cumulativeCashTax[i] + totalCashTax[i])
elif i == 6:
totalCashTax.append(cashRevenueTEI[i] + 0 + taxesBenefitLiab[i] + buyoutAmount + taxFromBuyout)
cumulativeCashTax.append(cumulativeCashTax[i] + totalCashTax[i] + buyoutAmount + taxFromBuyout)
else:
totalCashTax.append(cashRevenueTEI[i] + 0 + taxesBenefitLiab[i] + 0 + 0)
cumulativeCashTax.append(cumulativeCashTax[i] + totalCashTax[i])
#[AW21]
if (cumulativeCashTax[7] > 0):
cumulativeIRR = round(irr(totalCashTax), 4)
else:
cumulativeIRR = 0
# Deleteme: Variable Dump for debugging
# variableDump = {}
# variableDump["TaxEquity"] = {}
# variableDump["TaxEquity"]["coopInvestmentTaxEquity"] = coopInvestmentTaxEquity
# variableDump["TaxEquity"]["financeCostCashTaxEquity"] = financeCostCashTaxEquity
# variableDump["TaxEquity"]["cashToSPEOForPPATE"] = cashToSPEOForPPATE
# variableDump["TaxEquity"]["derivedCostEnergyTE"] = derivedCostEnergyTE
# variableDump["TaxEquity"]["OMInsuranceETCTE"] = OMInsuranceETCTE
# variableDump["TaxEquity"]["cashFromSPEToBlockerTE"] = cashFromSPEToBlockerTE
# variableDump["TaxEquity"]["cashFromBlockerTE"] = cashFromBlockerTE
# variableDump["TaxEquity"]["distAdderTaxEquity"] = distAdderTaxEquity
# variableDump["TaxEquity"]["netCoopPaymentsTaxEquity"] = netCoopPaymentsTaxEquity
# variableDump["TaxEquity"]["NPVLoanTaxEquity"] = NPVLoanTaxEquity
return cumulativeIRR, Rate_Levelized_TaxEquity, NPVLoanTaxEquity
# Function Calls Mega Sized Tax Equity Function Above
z = 0
PPARateSixYearsTE = z / 100
nGoal = float(inputDict.get("taxEquityReturn",0))/100
nValue = 0
for p in range (0, 3):
while ((z < 50000) and (nValue < nGoal)):
achievedReturnTE, Rate_Levelized_TaxEquity, NPVLoanTaxEquity = taxEquityFlip(PPARateSixYearsTE, inputDict.get("discRate", 0), outData["totalCost"], outData["allYearGenerationMWh"], distAdderDirect, loanYears, firstYearLandLeaseCosts, firstYearOPMainCosts, firstYearInsuranceCosts, numberPanels)
nValue = achievedReturnTE
z = z + math.pow(10,p)
PPARateSixYearsTE = z/100.0
z = z - math.pow(10,p)
PPARateSixYearsTE = z/100
#Master Output [Tax Equity]
outData["levelCostTaxEquity"] = Rate_Levelized_TaxEquity
outData["costPanelTaxEquity"] = abs(NPVLoanTaxEquity/numberPanels)
outData["cost10WPanelTaxEquity"] = (float(outData["costPanelTaxEquity"])/panelSize)*10
### PPA Comparison
#Output - PPA [F]
distAdderPPA = distAdderDirect
#Output - PPA [G]
netCoopPaymentsPPA = []
#Output - PPA [H]
costToCustomerPPA = []
#Output - PPA [I]
costToCustomerPPA = []
#Output - PPA [H40]
NPVLoanPPA = 0
#Output - PPA [I40]
Rate_Levelized_PPA = 0
## PPA Formulas
#Output - PPA [G] [H]
for i in range (1, len(outData["allYearGenerationMWh"])+1):
netCoopPaymentsPPA.append(-outData["allYearGenerationMWh"][i]*float(inputDict.get("firstYearEnergyCostPPA",0))*math.pow((1 + float(inputDict.get("annualEscRatePPA", 0))/100),(i-1)))
costToCustomerPPA.append(netCoopPaymentsPPA[i-1]-distAdderPPA[i-1])
#Output - PPA [H58]
NPVLoanPPA = npv(float(inputDict.get("discRate", 0))/100, [0,0]+costToCustomerPPA)
#Output - PPA [F65]
Rate_Levelized_PPA = -NPVLoanPPA/NPVallYearGenerationMWh
#Master Output [PPA]
outData["levelCostPPA"] = Rate_Levelized_PPA
outData["firstYearCostKWhPPA"] = float(inputDict.get("firstYearEnergyCostPPA",0))
outData["yearlyEscalationPPA"] = float(inputDict.get("annualEscRatePPA", 0))
# Add all Levelized Costs to Output
outData["LevelizedCosts"] = [["Direct Loan", Rate_Levelized_Direct],
["NCREBs Financing", Rate_Levelized_NCREB],
["Lease Buyback", Rate_Levelized_Lease],
["Tax Equity Flip", Rate_Levelized_TaxEquity]]
outData["LevelizedCosts"].append({"name":"PPA Comparison", "y":Rate_Levelized_PPA, "color":"gold"})
# Stdout/stderr.
outData["stdout"] = "Success"
outData["stderr"] = ""
return outData
def test_ipmt(self):
assert_almost_equal(np.round(np.ipmt(0.1 / 12, 1, 24, 2000), 2), -16.67)
def Payment_Interest(): #要求同上
a = np.ipmt(eval(e1.get()), eval(e2.get()), eval(e3.get()),
eval(e4.get()))
a = 0 - a
v2.set(a)
def test_when(self):
# begin
assert_equal(np.rate(10, 20, -3500, 10000, 1),
np.rate(10, 20, -3500, 10000, 'begin'))
# end
assert_equal(np.rate(10, 20, -3500, 10000),
np.rate(10, 20, -3500, 10000, 'end'))
assert_equal(np.rate(10, 20, -3500, 10000, 0),
np.rate(10, 20, -3500, 10000, 'end'))
# begin
assert_equal(np.pv(0.07, 20, 12000, 0, 1),
np.pv(0.07, 20, 12000, 0, 'begin'))
# end
assert_equal(np.pv(0.07, 20, 12000, 0),
np.pv(0.07, 20, 12000, 0, 'end'))
assert_equal(np.pv(0.07, 20, 12000, 0, 0),
np.pv(0.07, 20, 12000, 0, 'end'))
# begin
assert_equal(np.fv(0.075, 20, -2000, 0, 1),
np.fv(0.075, 20, -2000, 0, 'begin'))
# end
assert_equal(np.fv(0.075, 20, -2000, 0),
np.fv(0.075, 20, -2000, 0, 'end'))
assert_equal(np.fv(0.075, 20, -2000, 0, 0),
np.fv(0.075, 20, -2000, 0, 'end'))
# begin
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0, 1),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'begin'))
# end
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'end'))
assert_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0, 0),
np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'end'))
# begin
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 1),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'begin'))
# end
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'end'))
assert_equal(np.ppmt(0.1 / 12, 1, 60, 55000, 0, 0),
np.ppmt(0.1 / 12, 1, 60, 55000, 0, 'end'))
# begin
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 1),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'begin'))
# end
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'end'))
assert_equal(np.ipmt(0.1 / 12, 1, 24, 2000, 0, 0),
np.ipmt(0.1 / 12, 1, 24, 2000, 0, 'end'))
# begin
assert_equal(np.nper(0.075, -2000, 0, 100000., 1),
np.nper(0.075, -2000, 0, 100000., 'begin'))
# end
assert_equal(np.nper(0.075, -2000, 0, 100000.),
np.nper(0.075, -2000, 0, 100000., 'end'))
assert_equal(np.nper(0.075, -2000, 0, 100000., 0),
np.nper(0.075, -2000, 0, 100000., 'end'))
payments_year = 1 t = years * payments_year pv = 1000 t_0 = date(2018, 10, 21) # how much is the value of each payment (pmt) pmt = np.pmt(r, t, pv) # Now, how much of the pmt is interests (i) and how much is principal (p)? # Period to calculate per = 1 # Calculate the interest ipmt = np.ipmt(r / payments_year, per, t, pv) # Calculate the principal ppmt = np.ppmt(r / payments_year, per, t, pv) # Now, let us create a table for this data set. # first we create the date range for all the payment periods rng = pd.date_range(start=t_0, periods=t, freq='365D') rng.name = "Date" # Then, we create the columns for the table. We do that by creating a "Data Frame" in Pandas # we use the date range as index. df = pd.DataFrame(index=rng,
# plt.show()
# 摊还（分期）表
# 矢量化也适用于金融领域。
# 给定年利率，支付频率（每年的次数），初始贷款余额和贷款期限，你可以以矢量化方式创建包含月贷款余额和付款的摊还表。让我们先设置一些标量常量：
freq = 12 # 每年12个月
rate = .0675 # 年利率6.75%
nper = 30 # 30年
pv = 200000 # 贷款面值
rate /= freq # 按月
nper *= freq # 360个月
# NumPy预装了一些财务函数，与Excel表兄弟不同，它们能够以矢量的形式输出。
# 债务人（或承租人）每月支付一笔由本金和利息部分组成的固定金额。由于未偿还的贷款余额下降，总付款的利息部分随之下降。
periods = np.arange(1, nper + 1, dtype=int)
principal = np.ppmt(rate, periods, nper, pv)
interest = np.ipmt(rate, periods, nper, pv)
pmt = principal + interest
# 接下来，你需要计算每月的余额，包括支付前和付款后的余额，可以定义为原始余额的未来价值减去年金(支付流)的未来价值，使用折扣因子d：
# 从功能上看，如下所示：
def balance(pv, rate, nper, pmt):
d = (1 + rate)**nper
return pv * d - pmt * (d - 1) / rate
# 最后，你可以使用Pandas 的 DataFrame 将其放到表格格式中。小心这里的标志。从债务人的角度看，PMT是一种流出。
import pandas as pd
cols = ['beg_bal', 'prin', 'interest', 'end_bal']
data = [
def interest_payment(price, down_payment, interest, loan_term, x):
r = interest / 1200.0
N = loan_term * 12.0
P = price - down_payment
payment = np.ipmt(r, x, N, P)
return payment * (-1)
def test_ipmt(self):
np.round(np.ipmt(0.1/12,1,24,2000),2) == 16.67
def complete_Anticip_FPPYList(D_date1, Loan_Amount1, D_date2, Loan_Amount2,
D_date3, Loan_Amount3, D_date4, Loan_Amount4,
Repayment_Option, Expected_Graduation_Date,
Repayment_Term, IntR, PO):
final_dates, period_inStatus, status, disbAmtList, cum_disb_aftercurve, interest_aftercurve, min_Pay_aftercurve, PO_List, Anticipated_Def_Amt, Anticipated_PartialPrepay_Amt, Anticipated_FullPrepay_Amt, capitalisationList_aftercurve = capitalisation_aftercurve(
D_date1, Loan_Amount1, D_date2, Loan_Amount2, D_date3, Loan_Amount3,
D_date4, Loan_Amount4, Repayment_Option, Expected_Graduation_Date,
Repayment_Term, IntR, PO)
endpoint_default, endpoint_ParPrepay, endpoint_FullPrepay, unit_defaults, unit_severity, unit_ParPrepay, unit_FullPrepay = curve_database(
D_date1, Loan_Amount1, D_date2, Loan_Amount2, D_date3, Loan_Amount3,
D_date4, Loan_Amount4, Repayment_Option, Expected_Graduation_Date,
Repayment_Term, IntR, PO)
InterestRate = np.round(IR(IntR) / 100, 3) # interest rate required
if (Repayment_Option != "IM"):
indices = [i for i, x in enumerate(status) if x == "Grace"]
last_index = max(indices)
else:
indices = [i for i, x in enumerate(status) if x == "In-School"]
last_index = max(indices)
repay_Start_Index = last_index + 1
del Anticipated_FullPrepay_Amt[repay_Start_Index:]
if (Repayment_Option != "IM"):
RepaymentTerm_Mos = Repayment_Term + 1 # repayment term required in months
else:
RepaymentTerm_Mos = Repayment_Term - repay_Start_Index
principal_paid = []
interest_paid = []
total_payment = []
ending_balance = []
# for first ending balance
cap_amt_first_eBal = round(capitalisationList_aftercurve[last_index], 2)
for per in range(RepaymentTerm_Mos):
principal_Paid = -np.ppmt(InterestRate, per, RepaymentTerm_Mos,
cap_amt_first_eBal)
principal_paid.append(principal_Paid)
interest_Paid = -np.ipmt(InterestRate, per, RepaymentTerm_Mos,
cap_amt_first_eBal)
interest_paid.append(interest_Paid)
total_payment.append(principal_Paid + interest_Paid)
# calculating the ending balance at each period
for i in range(repay_Start_Index, len(status)):
if (status[i] == "In-School" or status[i] == "Grace"):
ending_balance.append("-")
interest_paid.append("-")
total_payment.append("-")
principal_paid.append("-")
else:
i = i - repay_Start_Index
start_previous_bal = cap_amt_first_eBal - principal_paid[i]
ending_balance.append(start_previous_bal)
first_fullPrepay_calc = start_previous_bal * endpoint_FullPrepay * unit_FullPrepay[
repay_Start_Index] # prior month ending principal * current month Full prepay unit
Anticipated_FullPrepay_Amt.append(first_fullPrepay_calc)
cap_amt_first_eBal = start_previous_bal
return final_dates, period_inStatus, status, disbAmtList, cum_disb_aftercurve, interest_aftercurve, min_Pay_aftercurve, PO_List, Anticipated_Def_Amt, Anticipated_PartialPrepay_Amt, Anticipated_FullPrepay_Amt, capitalisationList_aftercurve
