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
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 Payment_Interest(): #要求同上 a = np.ipmt(eval(e1.get()), eval(e2.get()), eval(e3.get()), eval(e4.get())) a = 0 - a v2.set(a)
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 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