def test_pmt(self): res = np.pmt(0.08 / 12, 5 * 12, 15000) tgt = -304.145914 assert_allclose(res, tgt) # Test the edge case where rate == 0.0 res = np.pmt(0.0, 5 * 12, 15000) tgt = -250.0 assert_allclose(res, tgt) # Test the case where we use broadcast and # the arguments passed in are arrays. res = np.pmt([[0.0, 0.8], [0.3, 0.8]], [12, 3], [2000, 20000]) tgt = np.array([[-166.66667, -19311.258], [-626.90814, -19311.258]]) assert_allclose(res, tgt)
def __init__(self, loan_id, grade, int_rate, term, amount, issue_date, last_date, defaults, investment, total_payment, total_principle, recoveries): self.id = loan_id self.grade = grade self.int_rate = int_rate self.term = int(term.strip().split(' ')[0]) self.amount = amount self.initial_amount = self.amount self.issue_date = issue_date self.last_date = last_date self.investment = investment self.defaults = defaults self.total_payment = total_payment self.total_principle = total_principle self.recoveries = recoveries self.imbalance_ratio = np.nan self.term_realized = self.last_date - self.issue_date if self.defaults: # warnings.warn('have not yet implemented defaulting loans') self.amount = self.total_payment self.installment = np.round(-np.pmt(self.int_rate / 12, self.term_realized, self.amount), 2) self.remaining_term = self.term_realized self.scale = (self.investment / self.initial_amount) self.imbalance = 0 self.complete = False self.fee = 0.01
def add_current_rents(dataset,buildings,year,rent=0,convert_res_rent_to_yearly=0,convert_res_sales_to_yearly=0): buildings['nonres_rent'] = dataset.load_attr('nonresidential_rent',year) if not rent: buildings['res_rent'] = dataset.load_attr('residential_sales_price',year) if convert_res_sales_to_yearly: buildings.res_rent = np.pmt(developer.INTERESTRATE,developer.PERIODS,buildings.res_rent)*-1 else: buildings['res_rent'] = dataset.load_attr('residential_rent',year)*(12 if convert_res_rent_to_yearly else 1) buildings['rent'] = buildings['nonres_rent'].combine_first(buildings['res_rent']) return buildings
def levelize_costs(self): if hasattr(self, 'is_levelized'): inflation = float(cfg.cfgfile.get('case', 'inflation_rate')) rate = self.cost_of_capital - inflation if self.is_levelized == 0: self.values_level = - np.pmt(rate, self.book_life, 1, 0, 'end') * self.values util.convert_age(self, vintages=self.vintages, years=self.years, attr_from='values_level', attr_to='values_level', reverse=False) else: self.values_level = self.values.copy() util.convert_age(self, vintages=self.vintages, years=self.years, attr_from='values_level', attr_to='values_level', reverse=False) self.values = np.pv(rate, self.book_life, -1, 0, 'end') * self.values else: raise ValueError('Supply Technology id %s needs to indicate whether costs are levelized ' %self.id)
def levelize_costs(self): if hasattr(self, 'is_levelized'): inflation = float(cfg.cfgfile.get('case', 'inflation_rate')) rate = self.cost_of_capital - inflation if self.is_levelized == 0: self.values_level = - np.pmt(rate, self.book_life, 1, 0, 'end') * self.values util.convert_age(self, vintages=self.vintages, years=self.years, attr_from='values_level', attr_to='values_level', reverse=False) else: self.values_level = self.values.copy() util.convert_age(self, vintages=self.vintages, years=self.years, attr_from='values_level', attr_to='values_level', reverse=False) self.values = np.pv(rate, self.book_life, -1, 0, 'end') * self.values else: util.convert_age(self, vintages=self.vintages, years=self.years, reverse=False)
def test_pmt_decimal(self): res = np.pmt(Decimal('0.08') / Decimal('12'), 5 * 12, 15000) tgt = Decimal('-304.1459143262052370338701494') assert_equal(res, tgt) # Test the edge case where rate == 0.0 res = np.pmt(Decimal('0'), Decimal('60'), Decimal('15000')) tgt = -250 assert_equal(res, tgt) # Test the case where we use broadcast and # the arguments passed in are arrays. res = np.pmt([[Decimal('0'), Decimal('0.8')], [Decimal('0.3'), Decimal('0.8')]], [Decimal('12'), Decimal('3')], [Decimal('2000'), Decimal('20000')]) tgt = np.array([[Decimal('-166.6666666666666666666666667'), Decimal('-19311.25827814569536423841060')], [Decimal('-626.9081401700757748402586600'), Decimal('-19311.25827814569536423841060')]]) # Cannot use the `assert_allclose` because it uses isfinite under the covers # which does not support the Decimal type # See issue: https://github.com/numpy/numpy/issues/9954 assert_equal(res[0][0], tgt[0][0]) assert_equal(res[0][1], tgt[0][1]) assert_equal(res[1][0], tgt[1][0]) assert_equal(res[1][1], tgt[1][1])
def get_possible_rents_by_use(dset): parcels = dset.parcels # need a prevailing rent for each parcel nodeavgrents = pd.read_csv('avenodeprice.csv',index_col='node_id') # convert from price per sqft to yearly rent per sqft nodeavgrents['rent'] = np.pmt(spotproforma.INTERESTRATE,spotproforma.PERIODS,nodeavgrents.price*-1) # need predictions of rents for each parcel avgrents = pd.DataFrame(index=parcels.index) avgrents['residential'] = nodeavgrents.rent.ix[parcels._node_id].values # make a stupid assumption converting the residential # rent which I have to non-residential rent which I don't have avgrents['office'] = nodeavgrents.rent.ix[parcels._node_id].values*1.0 avgrents['retail'] = nodeavgrents.rent.ix[parcels._node_id].values*.8 avgrents['industrial'] = nodeavgrents.rent.ix[parcels._node_id].values*.5 return avgrents
def actual_monthly(self): """User actually paid monthly money for the car.""" actual_monthly = numpy.pmt( self.loan_at / 12, self.total_months, -self.finance_value, self.loan_at_end ) logger.info('numpy.pmt({},{},{},{})={}'.format( self.loan_at / 12, self.total_months, -self.finance_value, self.loan_at_end, actual_monthly )) return round(actual_monthly, 2)
def main(): """Module retirement""" if not len(sys.argv) == 2: print('usage: {} rate'.format(sys.argv[0])) sys.exit(1) rate = float(sys.argv[1])/12/100 startdate = 2004 + 10./12 today = 2015 + 5./12 amount = 222000 retirementdate = 1981 + 60 + 10./12 endend = 1981 + 10./12 + 100 inflation = 2./100./12. + 1 spending = 40000 contribution = np.pmt(rate, int(round((today - startdate) * 12)), 0, amount) print('contribution {}'.format(contribution)) cur_time = today while cur_time < retirementdate: cur_time += 1./12 interest = amount * rate amount += -contribution + interest contribution *= inflation spending *= inflation print('retire contribution {} amount {} spending {}' .format(contribution, amount, spending)) while cur_time < endend and amount > 0: cur_time += 1./12 interest = amount * rate amount += interest amount -= spending / 12. spending *= inflation print('end time {} spending {} amount {}'.format(cur_time, spending, amount)) print('')
def walk_forward_projection(principal, annual_rate, months=None, payment=1200): """ :param payment: :param principal: positive value :param months: compounding periods (can be weeks) :param annual_rate: matches compounding periods (can be weekly rate) :return: """ loan_table = pd.DataFrame(columns=('payment', 'interest_segment', 'principal_segment', 'balance')) annual_rate /= 12 # Make the rate a monthly rate. # Initialise Table loan_table.loc[0] = [0, 0, 0, principal] # Handles pay by amount if months is not None: payment = -np.pmt(annual_rate, months, principal) # Handles pay by amount time_step = 1 balance = principal while not (balance <= 0 or time_step > 100): fraction_interest = balance * annual_rate if balance > payment: balance = round(balance + fraction_interest - payment, 2) else: payment = balance balance = 0 fractional_principal = payment - fraction_interest loan_table.loc[time_step] = [payment, fraction_interest, fractional_principal, balance] time_step += 1 return loan_table
def get_mort(interest, value, years, insurance, hoa): print("Button clicked") while True: try: interest = float(interest) interest = interest / 100 value = float(value) years = float(years) hoa = float(hoa) insurance = float(insurance) insurance = insurance / 100 if interest > 0 and value > 0 and years > 0 and insurance >= 0: monthly_payment = -1 * np.pmt(interest / 12, years * 12, value) print(monthly_payment) if hoa != 0: monthly_payment += hoa if insurance != 0: to_add = monthly_payment * insurance monthly_payment += to_add print(monthly_payment) display_label['text'] = '$' + str(monthly_payment.round(2)) else: display_label['text'] = 'Please type a positive number' except: display_label['text'] = 'Please type a number' break else: break
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 amortize(argv, lump_sum=0, lump_sum_dt=None): init_addl_principal = FLAGS.add_payment pmt = -round(np.pmt(FLAGS.loan_ir/FLAGS.pa_payments, FLAGS.loan_length_yrs*FLAGS.pa_payments, FLAGS.loan_amt), 2) # initialize the variables to keep track of the periods and running balances p = 1 # Init temporary variables with default flag values; this is because we do not want to override the flag values beg_balance = FLAGS.loan_amt end_balance = FLAGS.loan_amt start_date = datetime.strptime(FLAGS.loan_start_dt, "%d-%m-%Y") if lump_sum_dt: lump_sum_dt = datetime.strptime(lump_sum_dt, "%d-%m-%Y") var_ir = FLAGS.var_ir loan_ir = FLAGS.loan_ir add_payment = FLAGS.add_payment loan_amt = FLAGS.loan_amt while end_balance > 0: # Fluctuate variable interest... if var_ir and start_date.month == 6 and loan_ir < 0.06: # print('Modifying interest rate...') # Assumes that interest rate changes occur mid-year if FLAGS.var_ir_fluct == 'chaotic': loan_ir = loan_ir * np.random.uniform(0.5, 1.5) if FLAGS.var_ir_fluct == 'aggressive': loan_ir = loan_ir * np.random.uniform(0.8,1.2) if FLAGS.var_ir_fluct == 'moderate': loan_ir = loan_ir * np.random.uniform(0.875,0.125) if FLAGS.var_ir_fluct == 'conservative': loan_ir = loan_ir * np.random.uniform(0.95,1.05) # print(loan_ir) # Recalculate the interest based on the current balance interest = round(((loan_ir/FLAGS.pa_payments) * beg_balance), 2) # Determine payment based on whether or not this period will pay off the loan pmt = min(pmt, beg_balance + interest) loan_amt = pmt - interest # Ensure additional payment gets adjusted if the loan is being paid off. # If the difference between the beginning balance and principal is < additional payment, reduce additional # payment to match remaining balance. # Add lump-sum event (Assumes that payment occurs at month begin) if start_date == lump_sum_dt: adhoc_paymnt = min((add_payment + lump_sum), beg_balance - loan_amt) # print(f'Adhoc - Lump sum payment: ${adhoc_paymnt:0.0f}') end_balance = beg_balance - (loan_amt + adhoc_paymnt) else: FLAGS.add_payment = min(add_payment, beg_balance - loan_amt) end_balance = beg_balance - (loan_amt + add_payment) adhoc_paymnt = 0 yield OrderedDict([('Month', start_date), ('Period', p), ('Begin Balance', beg_balance), ('Payment', pmt), ('Principal', FLAGS.loan_amt), ('Interest', interest), ('Interest_Rate', loan_ir), ('Additional_Payment', add_payment), ('Adhoc Payment', adhoc_paymnt), ('End Balance', end_balance)]) if add_payment > (beg_balance - loan_amt): add_payment = init_addl_principal # Increment the counter, balance and date p += 1 start_date += relativedelta(months=1) beg_balance = end_balance
def test_pay_amount_validation(self): amount = 10000 periods = 24 loan = self.create_loan('fixed-annuity', amount, 1, periods) self.assertTrue(loan.line_ids) self.assertEqual(len(loan.line_ids), periods) line = loan.line_ids.filtered(lambda r: r.sequence == 1) self.assertAlmostEqual( - numpy.pmt(1 / 100 / 12, 24, 10000), line.payment_amount, 2) self.assertEqual(line.long_term_principal_amount, 0) loan.long_term_loan_account_id = self.lt_loan_account loan.compute_lines() line = loan.line_ids.filtered(lambda r: r.sequence == 1) self.assertGreater(line.long_term_principal_amount, 0) self.post(loan) self.assertTrue(loan.start_date) line = loan.line_ids.filtered(lambda r: r.sequence == 1) self.assertTrue(line) self.assertFalse(line.move_ids) self.assertFalse(line.invoice_ids) wzd = self.env['account.loan.generate.wizard'].create({}) action = wzd.run() self.assertTrue(action) self.assertFalse(wzd.run()) self.assertTrue(line.move_ids) self.assertIn(line.move_ids.id, action['domain'][0][2]) line.move_ids.post() with self.assertRaises(UserError): self.env['account.loan.pay.amount'].create({ 'loan_id': loan.id, 'amount': (amount - amount / periods) / 2, 'fees': 100, 'date': datetime.strptime( line.date, DF ).date() + relativedelta(months=-1) }).run() with self.assertRaises(UserError): self.env['account.loan.pay.amount'].create({ 'loan_id': loan.id, 'amount': amount, 'fees': 100, 'date': datetime.strptime( line.date, DF ).date() }).run() with self.assertRaises(UserError): self.env['account.loan.pay.amount'].create({ 'loan_id': loan.id, 'amount': 0, 'fees': 100, 'date': datetime.strptime( line.date, DF ).date() }).run() with self.assertRaises(UserError): self.env['account.loan.pay.amount'].create({ 'loan_id': loan.id, 'amount': -100, 'fees': 100, 'date': datetime.strptime( line.date, DF ).date() }).run()
from __future__ import print_function import numpy as np print("Payment", np.pmt(0.01 / 12, 12 * 30, 10000000))
def expenseModel_CC(cntLimIntRate = 3.00 ,cntBalMonths = 36 ,obsRecentRepayTxnBar = 31 ,syph = 2 ,infRecentTxnBar = 31 ,infCountTxnBar = 3 ,fuzzThreshold = 85 ): jSonData = request.json #grab app context appRef = jSonData['reference'] appDate = jSonData['submissionTime'][:10] #stab commitment tables - observed & inferred obsCcVar = 'cntType accType accName accNum accBsb balCurrOut balAvail limit intRate minAmtDue minAmtDueDate cntLim cntBal cntObs cntRepayRatioObs'.split() ccCntObs = pd.DataFrame([[0]*len(obsCcVar)], columns=obsCcVar) infCcVar = 'cntType text infNarration txtHit amount cntInf accType accName accNum accBsb'.split() ccCntInf = pd.DataFrame([[0]*len(infCcVar)],columns=infCcVar) #cycle through accounts on bankstatment; for accIdx in np.arange(len(jSonData['bankData']['bankAccounts'])): #observed CC - get account characteristics - take conservative positions where no data if jSonData['bankData']['bankAccounts'][accIdx]['accountType'].upper().replace(" ", "") == 'CREDITCARD': cntType = 'obs' accName = jSonData['bankData']['bankAccounts'][accIdx]['accountName'] accType = jSonData['bankData']['bankAccounts'][accIdx]['accountType'] accNum = jSonData['bankData']['bankAccounts'][accIdx]['accountNumber'] accBsb = jSonData['bankData']['bankAccounts'][accIdx]['bsb'] balCurrOut = abs(float(jSonData['bankData']['bankAccounts'][accIdx]['currentBalance'])) balAvail = abs(float(jSonData['bankData']['bankAccounts'][accIdx]['availableBalance'])) try: intRate = float(jSonData['bankData']['bankAccounts'][accIdx]['additionalDetails']['interestRate']) except (ValueError, KeyError): intRate = 21.00 try: limit = float(jSonData['bankData']['bankAccounts'][accIdx]['additionalDetails']['creditLimit']) except (ValueError, KeyError): limit = (balCurrOut) + (balAvail) try: minAmtDue = float(jSonData['bankData']['bankAccounts'][accIdx]['additionalDetails']['minimumAmountDue']) except (ValueError, KeyError): minAmtDue = np.nan try: minAmtDueDate = jSonData['bankData']['bankAccounts'][accIdx]['additionalDetails']['minimumAmountDueDate'] except (ValueError, KeyError): minAmtDueDate = np.nan #calc obs commitment #limit position cntLim = round(limit * cntLimIntRate/100,2) #balance position cntBal = abs(round(np.pmt(intRate/100/12,cntBalMonths,balCurrOut,0),2)) #sum cntObs = round(cntLim + cntBal,2) #calc obs average monthly repayment #grab txns txnObs = pd.DataFrame(jSonData['bankData']['bankAccounts'][accIdx]['transactions']) #if no repayment txns, then ratio is null cntRepayRatioObs = np.nan if len(txnObs) > 0: #get repayment txns: credits, recent, repayment narration detection #credits txnRepay = txnObs[txnObs['amount']>0].copy() txnRepay['text'] = txnRepay['text'].str.upper() #repayment narration detection txnRepay = txnRepay[txnRepay.text.str.contains("CREDIT CARD REPAYMENT|CREDIT CARD PAYMENT|CREDIT CARD PAYMEN|CC REPAYMENT|CC PAYMENT|CC AUTO PAYMENT|PAYMENT THANKYOU")] #some exist and recent if (len(txnRepay) > 0) and (np.datetime64(txnRepay.date.max()) >= (np.datetime64(appDate)-obsRecentRepayTxnBar)): #wrangle commitment at point of repayment txnRepay['monYY'] = pd.to_datetime(txnRepay['date'], format='%Y-%m-%d').dt.to_period("M") txnRepay['balBeforeTxn'] = txnRepay.balance.astype(float) + txnRepay.amount.astype(float) txnRepay['cnt'] = abs(np.pmt(intRate/100/12,cntBalMonths,txnRepay.balBeforeTxn,0)) + cntLim txnRepay = txnRepay.groupby('monYY').agg({'amount':'sum','cnt':'sum'}) \ .merge( \ txnRepay.groupby('monYY').agg({'amount':'sum','cnt':'sum'}).groupby('amount').agg({'amount':'count'}).rename(columns={'amount':'amountCount'}).reset_index() \ ,how='left',on='amount') #exclude repayment anomalies i.e. principal payments - +- 2 standard deviations of repayment #max procedure if txnRepay[txnRepay.amount == txnRepay.amount.max()]['amountCount'].item() == 1: if txnRepay.amount.max() > (np.std(txnRepay[txnRepay.amount != txnRepay.amount.max()].amount) * syph) + (txnRepay[txnRepay.amount != txnRepay.amount.max()].amount.mean()): txnRepay = txnRepay[txnRepay.amount != txnRepay.amount.max()] #min procedure - may not need. if txnRepay[txnRepay.amount == txnRepay.amount.min()]['amountCount'].item() == 1: if txnRepay.amount.min() < (np.std(txnRepay[txnRepay.amount != txnRepay.amount.min()].amount) * syph) - (txnRepay[txnRepay.amount != txnRepay.amount.max()].amount.mean()): txnRepay = txnRepay[txnRepay.amount != txnRepay.amount.min()] #calculate ratio: repayment amount/commitment cntRepayRatioObs = (txnRepay.amount/txnRepay.cnt).mean() #write observed CC + commitment deets to dataframe tmpAppend = pd.DataFrame([[cntType,accType,accName,accNum,accBsb,balCurrOut,balAvail,limit,intRate,minAmtDue,minAmtDueDate,cntLim,cntBal,cntObs,cntRepayRatioObs]],columns=obsCcVar) ccCntObs = ccCntObs.append(tmpAppend,ignore_index=True) #inferred CC from transaction and savings accounts #cycle through transaction/savings accounts if jSonData['bankData']['bankAccounts'][accIdx]['accountType'].upper().replace(" ", "") in 'TRANSACTION SAVINGS'.split(): #grab transcations txnInf = pd.DataFrame(jSonData['bankData']['bankAccounts'][accIdx]['transactions']) #wrangle outbound CC repayment txns if len(txnInf) > 0: #debits txnInf = txnInf[txnInf['amount']<0] txnInf['text'] = txnInf['text'].str.upper() #get repayment types txnInfCcRepay = txnInf[txnInf.type.str.upper()=="CREDIT CARD REPAYMENTS"].copy() if len(txnInfCcRepay) > 0: #clean narrations txnInfCcRepay['infNarration'] = txnInfCcRepay.text.apply(lambda x: re.sub("\d+", " ", x)) #aggregate by narration cashflow infAgg = txnInfCcRepay.groupby('infNarration text'.split()).agg({'amount':'count sum'.split(),'date':'min max'.split()}).reset_index() infAgg.columns = ["_".join(x) for x in infAgg.columns.ravel()] #Recent txn threshold infAgg = infAgg[infAgg.date_max.apply(lambda x: np.datetime64(x) >= (np.datetime64(appDate)-infRecentTxnBar))==True] #at least 3 instances of repayment to be considered a commitment infAgg = infAgg[(infAgg['amount_count']>infCountTxnBar)] #exlcude failing txns txnInfCcRepay = txnInfCcRepay[txnInfCcRepay.infNarration.isin(infAgg.infNarration_.tolist())] #fuzzy deduplication of narrations; infAggList = infAgg.infNarration_.tolist() #set up inferred fuzzy match table infFuzz = pd.DataFrame([[0]*5], columns='idInf txt fuzzScore txtHit idInfHit'.split()) tmpList = infAggList.copy() #fuzzy de-duping for idx, txt in enumerate(infAggList): tmpList = [t for t in tmpList if t is not txt] if len(tmpList) == 0: break appendFuzz = pd.DataFrame(process.extract(txt,tmpList)) appendFuzz.rename(columns={0:'txtHit',1:'fuzzScore'},inplace=True) appendFuzz['idxHit'] = appendFuzz.index appendFuzz = appendFuzz[appendFuzz.fuzzScore >= fuzzThreshold] if len(appendFuzz) == 0: next else: appendFuzz.loc[appendFuzz.fuzzScore >= fuzzThreshold,'idInf'] = idx appendFuzz.loc[appendFuzz.fuzzScore >= fuzzThreshold,'txt'] = txt infFuzz = infFuzz.append(appendFuzz,ignore_index=True) tmpList = [t for t in tmpList if t not in appendFuzz.txtHit.tolist()] infFuzz = infFuzz[1:] if len(infFuzz) > 0: tmp0 = txnInfCcRepay.merge(infFuzz['idInf txt'.split()].drop_duplicates(['idInf','txt'],keep = 'first',inplace=False),how='left',left_on='infNarration',right_on='txt',suffixes=["",""]) tmp1 = tmp0[tmp0.idInf.isnull()].drop('idInf',axis=1).merge(infFuzz['idInf txtHit'.split()].drop_duplicates(['idInf','txtHit'],keep = 'first',inplace=False),how='left',left_on='infNarration',right_on='txtHit',suffixes=["",""]) txnInfCcRepay = tmp0[tmp0.idInf.notnull()].append(tmp1,ignore_index=True).drop('txt',axis=1) else: txnInfCcRepay['txtHit'] = np.nan tmp0 = txnInfCcRepay.groupby('infNarration',as_index=False).agg({'amount':'mean'}) tmp0['idInf'] = tmp0.index txnInfCcRepay = txnInfCcRepay.merge(tmp0['idInf infNarration'.split()],how='left',on='infNarration') #inferred CC deets infCc = txnInfCcRepay.drop_duplicates('idInf')['idInf text infNarration txtHit'.split()].copy() #calculate average monthly repayment of inferred faciltiees txnInfCcRepay['monYY'] = pd.to_datetime(txnInfCcRepay['date'], format='%Y-%m-%d').dt.to_period("M") txnInfCcRepayMon = txnInfCcRepay.groupby('idInf monYY'.split()).agg({'amount':'sum'}).reset_index() \ .merge(txnInfCcRepay.groupby('monYY').agg({'amount':'count'}).rename(columns={'amount':'amountCount'}).reset_index() \ ,how='left',on='monYY') #apply anomaly repayment exclusion tmpSyph = pd.DataFrame([[0]*len(txnInfCcRepayMon.columns.values.tolist())],columns=txnInfCcRepayMon.columns.values.tolist()) for idx in txnInfCcRepayMon.idInf.unique().tolist(): tmp0 = txnInfCcRepayMon[txnInfCcRepayMon.idInf==idx] if tmp0[tmp0.amount == tmp0.amount.max()]['amountCount'].item() == 1: if tmp0.amount.max() > (np.std(tmp0[tmp0.amount != tmp0.amount.max()].amount) * syph) + (tmp0[tmp0.amount != tmp0.amount.max()].amount.mean()): tmp0 = tmp0[tmp0.amount != tmp0.amount.max()] #may not need min procedure - or specific syph threshold. if tmp0[tmp0.amount == tmp0.amount.min()]['amountCount'].item() == 1: if tmp0.amount.min() < (np.std(tmp0[tmp0.amount != tmp0.amount.min()].amount) * syph) - (tmp0[tmp0.amount != tmp0.amount.max()].amount.mean()): tmp0 = tmp0[tmp0.amount != tmp0.amount.min()] tmpSyph = tmpSyph.append(tmp0,ignore_index=True) txnInfCcRepayMon = tmpSyph[1:].copy() infCc = infCc.merge(txnInfCcRepayMon.groupby('idInf',as_index=False).agg({'amount':'mean'}),how='left',on='idInf') infCc['amount'] = abs(infCc['amount']) #get tran/savings account deets infCc['cntType'] = 'inf' infCc['accName'] = jSonData['bankData']['bankAccounts'][accIdx]['accountName'] infCc['accType'] = jSonData['bankData']['bankAccounts'][accIdx]['accountType'] infCc['accNum'] = jSonData['bankData']['bankAccounts'][accIdx]['accountNumber'] infCc['accBsb'] = jSonData['bankData']['bankAccounts'][accIdx]['bsb'] #write inferred CC + commitment deets to dataframe ccCntInf = ccCntInf.append(infCc,ignore_index=True) #aggregate observed and inferred ccCntObs = ccCntObs[1:] ccCntInf = ccCntInf[1:] #average repayment across inferred CCs # cntRepayRatioObsMu = ccCntObs.cntRepayRatioObs.mean() #apply repayRatio to inferred repayments to get commitment; #nan not resolving... # if cntRepayRatioObsMu != np.nan: # ccCntInf['cntInf'] = ccCntInf.apply(lambda x: x.amount/cntRepayRatioObsMu , axis=1) ccCntObs_jS = ccCntObs.to_json() # ccCntInf_jS = ccCntInf.to_json() # return(ccCntObs_jS,ccCntInf_jS) return(ccCntObs_jS)
import numpy as np # Calculate the EAA for Project 1 eaa_project1 = np.pmt(rate=wacc, nper=8, pv=-1 * npv_project1, fv=0) print("Project 1 EAA: " + str(round(eaa_project1, 2))) # Calculate the EAA for Project 2 eaa_project2 = np.pmt(rate=wacc, nper=7, pv=-1 * npv_project2, fv=0) print("Project 2 EAA: " + str(round(eaa_project2, 2)))
import pandas as pd import numpy as np from datetime import date # Define the variables for the mortgage Interest_Rate = 0.04 Years = 30 Payments_Year = 12 Principal = 200000 Addl_Principal = 50 start_date = (date(2019, 6, 1)) '''Basic use of numpy financial functions ''' # monthly payment pmt = np.pmt( Interest_Rate / Payments_Year, Years * Payments_Year, Principal ) #https://docs.scipy.org/doc/numpy/reference/generated/numpy.pmt.html # payment portioning per = Years * 12 interest_payment = np.ipmt(Interest_Rate / Payments_Year, per, Years * Payments_Year, Principal) principal_payment = np.ppmt(Interest_Rate / Payments_Year, per, Years * Payments_Year, Principal) print( f"For payment period {per}, you will pay ${-interest_payment} in interest and ${-principal_payment} in principal" ) ''' Setting up pandas ''' rng = pd.date_range(start_date, periods=Years * Payments_Year, freq='MS') rng.name = "Payment_Date"
def test_pmt(self): assert_almost_equal(np.pmt(.05,12,1000,100,1), -113.43614, 3)
def minimum_payment(self): return -np.pmt(self.monthly_rate, self.term_in_months, self.loan.purchase_amount)
#!/usr/bin/python import numpy print "Payment", numpy.pmt(0.01/12, 12 * 30, 10000000)
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
pl.figure() pl.plot(x_data_some_missing,'*') # that works too # lots of other random functions are in numpy, have a look! # double-check that the array manipulation or math function you're about to write hasn't already been written # http://docs.scipy.org/doc/numpy/reference/index.html # for example...Mark Mitsubishi will give you 72 months no interest # and you could buy a Mirage with automatic and AC there for $12,598 # payments per month is easy: 12598/72 = 175 # however...what if we miss this special deal, and we're stuck with # what the bank offers, 48 months, 3.5% interest # nm.pmt(interest rate per pay period, number of pay periods, present value of the loan) nm.pmt(0.035/12.0,48,12598) # $281.64 a month...dang, we better get to the dealership! # here's another random function, interp1d # go to that example file interp1d_example.py # on the back end of numpy is C and some fortran # so, speed of operations in is about the same as if you wrore the code in C # yet it's often easier to read numpy code # except if you're got a really weird for loop over all the elements of an array # itertools can help sometimes, but sometimes it's really hard to read or just can't be done
import numpy as np kredit = 2000000 rate = 0.11/12 # приводим к мес¤чным процентам period = 5*12 # срок приводим к мес¤цам payment = np.pmt(rate, period, kredit) print(round(payment,2))
def generate_lookup(self): keys = forms.keys() keys.sort() df_d = {} for name in keys: uses_distrib = forms[name] for parking_config in parking_configs: df = pd.DataFrame(index=fars) df['far'] = fars df['pclsz'] = tiledparcelsizes building_bulk = np.reshape(parcelsizes,(-1,1))*np.reshape(fars,(1,-1)) building_bulk = np.reshape(building_bulk,(-1,1)) if parking_config == 'deck': # need to converge in on exactly how much far is available for deck pkg orig_bulk = building_bulk while 1: parkingstalls = building_bulk*np.sum(uses_distrib*parking_rates)/SQFTPERRATE if np.where(np.absolute(orig_bulk-building_bulk-parkingstalls*parking_sqft_d[parking_config]) > 10.0)[0].size == 0: break building_bulk = orig_bulk - parkingstalls*parking_sqft_d[parking_config] df['build'] = building_bulk parkingstalls = building_bulk*np.sum(uses_distrib*parking_rates)/SQFTPERRATE parking_cost = parking_cost_d[parking_config]*parkingstalls*parking_sqft_d[parking_config] df['spaces'] = parkingstalls if parking_config == 'underground': df['parksqft'] = parkingstalls*parking_sqft_d[parking_config] stories = building_bulk / tiledparcelsizes if parking_config == 'deck': df['parksqft'] = parkingstalls*parking_sqft_d[parking_config] stories = (building_bulk+parkingstalls*parking_sqft_d[parking_config]) / tiledparcelsizes if parking_config == 'surface': stories = building_bulk / (tiledparcelsizes-parkingstalls*parking_sqft_d[parking_config]) df['parksqft'] = parkingstalls*parking_sqft_d[parking_config] stories[np.where(stories < 0.0)] = np.nan stories /= PARCELUSE; cost = building_cost(uses_distrib,stories,df)*building_bulk+parking_cost # acquisition cost! df['parkcost'] = parking_cost df['cost'] = cost yearly_cost_per_sqft = np.pmt(INTERESTRATE,PERIODS,cost)/(building_bulk*EFFICIENCY) df['yearly_pmt'] = yearly_cost_per_sqft break_even_weighted_rent = self.PROFITFACTOR*yearly_cost_per_sqft*-1.0 if name == 'retail': break_even_weighted_rent[np.where(fars>MAXRETAILHEIGHT)] = np.nan if name == 'industrial': break_even_weighted_rent[np.where(fars>MAXINDUSTRIALHEIGHT)] = np.nan #df['even_rent'] = break_even_weighted_rent df['even_rent'] = building_cost(uses_distrib,stories,df) df_d[(name,parking_config)] = df self.df_d = df_d min_even_rents_d = {} BIG = 999999 for name in keys: min_even_rents = None for parking_config in parking_configs: even_rents = df_d[(name,parking_config)]['even_rent'].fillna(BIG) min_even_rents = even_rents if min_even_rents is None else np.minimum(min_even_rents,even_rents) min_even_rents = min_even_rents.replace(BIG,np.nan) min_even_rents_d[name] = min_even_rents # this is the minimum cost per sqft for this form and far self.min_even_rents_d = min_even_rents_d
# 净现值 = np.npv(利率, 现金流) # 将1000元以1%的年利率存入银行5年,每年加存100元, # 相当于一次性存入多少钱? npv = np.npv(0.01, [-1000, -100, -100, -100, -100, -100]) print(round(npv, 2)) fv = np.fv(0.01, 5, 0, npv) print(round(fv, 2)) # 内部收益率 = np.irr(现金流) # 将1000元存入银行5年,以后逐年提现100元、200元、 # 300元、400元、500元,银行利率达到多少,可在最后 # 一次提现后偿清全部本息,即净现值为0元? irr = np.irr([-1000, 100, 200, 300, 400, 500]) print(round(irr, 2)) npv = np.npv(irr, [-1000, 100, 200, 300, 400, 500]) print(npv) # 每期支付 = np.pmt(利率, 期数, 现值) # 以1%的年利率从银行贷款1000元,分5年还清, # 平均每年还多少钱? pmt = np.pmt(0.01, 5, 1000) print(round(pmt, 2)) # 期数 = np.nper(利率, 每期支付, 现值) # 以1%的年利率从银行贷款1000元,平均每年还pmt元, # 多少年还清? nper = np.nper(0.01, pmt, 1000) print(int(nper)) # 利率 = np.rate(期数, 每期支付, 现值, 终值) # 从银行贷款1000元,平均每年还pmt元,nper年还清, # 年利率多少? rate = np.rate(nper, pmt, 1000, 0) print(round(rate, 2))
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'))
def minimum_payment(self): return -self.days_in_month * np.pmt(self.daily_rate, self.term_in_days, self.loan.purchase_amount)
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
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
import numpy as np np.pmt(0.075 / 12, 12 * 15, 200000)
import numpy as np principal = 2500.00 per = np.arange(1 * 12) + 1 ipmt = np.ipmt(0.0824 / 12, per, 1 * 12, principal) ppmt = np.ppmt(0.0824 / 12, per, 1 * 12, principal) pmt = np.pmt(0.0824 / 12, 1 * 12, principal) np.allclose(ipmt + ppmt, pmt) fmt = '{0:2d} {1:8.2f} {2:8.2f} {3:8.2f}' for payment in per: index = payment - 1 principal = principal + ppmt[index] print fmt.format(payment, ppmt[index], ipmt[index], principal) interestpd = np.sum(ipmt) np.round(interestpd, 2)
import pandas as pd import numpy as np import Tkinter original_balance = 250000 term_month = 360 yr_int_rate = 0.05 remain_term_in_month = term_month current_balance = original_balance payment = -np.pmt(yr_int_rate / 12, term_month, original_balance) month_prepay = 0.01 lgd = 0.4 month_drawdown_rate = 0.01 # part 1: prep for 0 to 75 months cum_pd = pd.read_excel(r'C:\Users\shm\Downloads\Effective Maturity Tables & Lifetime EL Calculation.xlsx', sheetname = 'cum_pd') cum_pd.columns = [x.lower() for x in cum_pd.columns] cum_pd['month_i'] = range(3, (cum_pd.shape[0] + 1)* 3, 3) row0 = pd.DataFrame([0, 0]).T row0.columns = cum_pd.columns cum_pd = pd.concat([row0, cum_pd], axis = 0) cum_pd['month_incr'] = cum_pd.cumulative_pd.diff().fillna(0) / 3 # from 78 to 360 cum_after = pd.DataFrame(columns = cum_pd.columns) cum_after['month_i'] = np.arange(cum_pd['month_i'].max() + 3, term_month + 3, 3) cum_after['month_incr'] = cum_pd.month_incr.values[-1]
def get_LoanInfo(loan_number): #loan_number = '1213498' server = '10.203.1.105\\alpha' database = 'test_yang' username = '******' password = '******' cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) query = """ select LoanNum, FstNm, LstNm, IntRate, FICOScore, HmSt, LoanAmt,b.savings,a.AmortTerm from REPORTS_LS_DM.dbo.Secondary as a inner join [lsprodreports].Reports.dbo.vw_Loan_Info as b on a.loanNum = b.loan_number where LoanNum = 'aaaaaaaa' and b.loan_number = 'aaaaaaaa' """ query = query.replace('aaaaaaaa',loan_number) queryResult = sql.read_sql(query, cnxn) queryResult.loc[len(queryResult)] = ["LoanNum","FstNm","LstNm","IntRate","Fico","State","Amt","savings","Term"] if len(queryResult) > 1 : principal = queryResult["LoanAmt"][0] interest_rate = queryResult["IntRate"][0]/100 term = queryResult["AmortTerm"][0] per = np.arange(term) + 1 ipmt = np.ipmt(interest_rate/12, per, term, principal) ppmt = np.ppmt(interest_rate/12, per, term, principal) pmt = np.pmt(interest_rate/12, term, principal) saving = queryResult["savings"][0] PrepaymentStartYear = 3 principal1 = principal ipmt1 = [] ppmt1 = [] pmt1 = [] lastPaymentMonth = 0 for payment in per: index = payment - 1 if payment> PrepaymentStartYear * 12: total_pay = -pmt + saving InterestPayment = principal1 * interest_rate /12 else: total_pay = -pmt InterestPayment = principal1 * interest_rate /12 if total_pay>=principal1: total_pay = principal1 + InterestPayment if lastPaymentMonth==0: lastPaymentMonth = payment PrincipalPayment = total_pay - InterestPayment principal1 = principal1 - PrincipalPayment ipmt1 = np.append(ipmt1,InterestPayment) ppmt1 = np.append(ppmt1,PrincipalPayment) pmt1 = np.append(pmt1,total_pay) total_int_saved ="$"+ str( round(ipmt.sum() *-1 - ipmt1.sum() ,0)).replace(".0","") erate1 = str(round(np.rate (lastPaymentMonth,(principal + ipmt1.sum())/lastPaymentMonth, -principal,0 )*12*100 , 2)) erate2 = str(round(np.rate (term,(principal + ipmt1.sum())/30/12, -principal,0 )*12*100,2)) lastPaymentYear = str(round(lastPaymentMonth/12,1)) lastPaymentMonth = str(lastPaymentMonth) return queryResult,total_int_saved,erate1,erate2,lastPaymentYear,lastPaymentMonth else: return queryResult, '0.0','0.0','0.0','1900','01'
#fv函数参数为利率,参数,每期支付金额,现值 print "Future value",np.fv(0.03/4, 5*4,-10,1000) print u"现值" #pv函数参数为利率,参数,每期支付金额,终值 print "Present value",np.pv(0.03/4,5*4,-10,1376.09633204) #npv参数为利率和一个表示现金流的数组. cashflows = np.random.randint(100,size=5) cashflows = np.insert(cashflows,0,-100) print "Cashflows",cashflows print "Net present value",np.npv(0.03,cashflows) print u"内部收益率" print "Internal rate of return",np.irr([-100, 38, 48,90 ,17,36]) print u"分期付款" #pmt输入为利率和期数,总价,输出每期钱数 print "Payment",np.pmt(0.10/12,12*30,100000) #nper参数为贷款利率,固定的月供和贷款额,输出付款期数 print "Number of payments", np.nper(0.10/12,-100,9000) #rate参数为付款期数,每期付款资金,现值和终值计算利率 print "Interest rate",12*np.rate(167,-100,9000,0) print u"窗函数" #bartlett函数可以计算巴特利特窗 window = np.bartlett(42) print "bartlett",window #blackman函数返回布莱克曼窗。该函数唯一的参数为输出点的数量。如果数量 #为0或小于0,则返回一个空数组。 window = np.blackman(10) print "blackman",window # hamming函数返回汉明窗。该函数唯一的参数为输出点的数量。如果数量为0或 # 小于0,则返回一个空数组。
left_on='ZIP OR POSTAL CODE', right_on='RegionName') print(avgPriceMergeDF) #Adds an average rent column and other calculated fields avgRentMergeDF = pd.merge(RedfinDF, ZillowRentDF, left_on='ZipBedKey', right_on='ZipBedKey', how='left') print(avgRentMergeDF) avgRentMergeDF['PRICE'] = avgRentMergeDF['PRICE'].astype(float) avgRentMergeDF['2019-09'] = avgRentMergeDF['2019-09'].astype(float) #Monthly rental cost assumptions mortgageEstimate = np.pmt(mortgageRate, 12 * 30, avgRentMergeDF['PRICE']) / 12 * -1 propertyTaxEstimate = (avgRentMergeDF['PRICE'] * propertyTaxRate) / 12 insruanceEstimate = 150 HOACost = 200 depreciationCost = (avgRentMergeDF['PRICE'] * .01) / 12 monthlyCostEstimate = mortgageEstimate + propertyTaxEstimate + insruanceEstimate + HOACost + depreciationCost #Adds a monthly cost estimate field avgRentMergeDF['monthlyCostEstimate'] = monthlyCostEstimate avgRentMergeDF[ 'AvgRentVariance'] = avgRentMergeDF['2019-09'] - monthlyCostEstimate #Print the DF to a csv avgRentMergeDF.to_csv(
def calc_engine(original_balance, term_month, yr_int_rate, month_prepay, lgd, month_drawdown_rate): remain_term_in_month = term_month current_balance = original_balance payment = -np.pmt(yr_int_rate / 12, term_month, original_balance) common_columns = ['monthIndex', 'starting_balance', 'schedule_payment', 'interest_paid', 'amortized_amount', 'prepay_amount', 'default_amount', 'recovery_amount', 'loss_amount', 'cash_drawdown_amount', 'ending_balance', 'total_cashflow', 'discount_cashflow', 'negative_balance', 'discount_loss'] detailed_calc_result = pd.DataFrame(index = range(1, int(term_month) + 1), columns = common_columns) # to calculate for each column in excel file for monthIndex in range(1, int(term_month) + 1): if monthIndex == 1: # D: starting_balance starting_balance = current_balance # E: schedule_payment if monthIndex >= remain_term_in_month: schedule_payment = starting_balance * (1 + yr_int_rate / 12) else: if starting_balance < payment: schedule_payment = starting_balance else: schedule_payment = payment # F: interest_paid G: amortized_amount interest_paid = starting_balance * yr_int_rate / 12 amortized_amount = schedule_payment - interest_paid # H: prepay_amount if ((monthIndex >= remain_term_in_month) | (starting_balance <= schedule_payment)): prepay_amount = 0 else: prepay_amount = starting_balance * month_prepay # I: default_amount default_amount = (starting_balance - prepay_amount) * def_rate_curve.ix[def_rate_curve.month_i == monthIndex, 'rate'].values[0] # J: recovery_amount K: loss_amount L: cash_drawdown_amount recovery_amount = default_amount * (1 - lgd) loss_amount = default_amount - recovery_amount cash_drawdown_amount = original_balance * month_drawdown_rate # M: ending_balance N: total_cashflow ending_balance = starting_balance - amortized_amount - prepay_amount - default_amount + cash_drawdown_amount total_cashflow = schedule_payment + prepay_amount - loss_amount - cash_drawdown_amount # O: discount_cashflow P: negative_balance discount_cashflow = total_cashflow / ((1 + yr_int_rate / 12) ** monthIndex) # P: negative_balance if ((ending_balance <= 0.1) | (monthIndex >= remain_term_in_month)): negative_balance = 1 else: negative_balance = 0 # R: discount_loss discount_loss = loss_amount / ((1 + yr_int_rate / 12) ** monthIndex) detailed_calc_result.ix[monthIndex, :] = [monthIndex, starting_balance, schedule_payment, interest_paid, amortized_amount, prepay_amount, default_amount, recovery_amount, loss_amount, cash_drawdown_amount, ending_balance, total_cashflow, discount_cashflow, negative_balance, discount_loss] else: # D: starting_balance starting_balance = ending_balance # E: schedule_payment if monthIndex >= remain_term_in_month: schedule_payment = starting_balance * (1 + yr_int_rate / 12) else: if starting_balance < payment: schedule_payment = starting_balance else: schedule_payment = payment # F: interest_paid G: amortized_amount interest_paid = starting_balance * yr_int_rate / 12 amortized_amount = schedule_payment - interest_paid # H: prepay_amount if ((monthIndex >= remain_term_in_month) | (starting_balance <= schedule_payment)): prepay_amount = 0 else: prepay_amount = starting_balance * month_prepay # I: default_amount default_amount = (starting_balance - prepay_amount) * def_rate_curve.ix[def_rate_curve.month_i == monthIndex, 'rate'].values[0] # J: recovery_amount K: loss_amount L: cash_drawdown_amount recovery_amount = default_amount * (1 - lgd) loss_amount = default_amount - recovery_amount cash_drawdown_amount = original_balance * month_drawdown_rate # M: ending_balance N: total_cashflow ending_balance = starting_balance - amortized_amount - prepay_amount - default_amount + cash_drawdown_amount total_cashflow = schedule_payment + prepay_amount - loss_amount - cash_drawdown_amount # O: discount_cashflow P: negative_balance discount_cashflow = total_cashflow / ((1 + yr_int_rate / 12) ** monthIndex) # P: negative_balance if ((ending_balance <= 0.1) | (monthIndex >= remain_term_in_month)): negative_balance = 1 else: negative_balance = 0 # R: discount_loss discount_loss = loss_amount / ((1 + yr_int_rate / 12) ** monthIndex) detailed_calc_result.ix[monthIndex, :] = [monthIndex, starting_balance, schedule_payment, interest_paid, amortized_amount, prepay_amount, default_amount, recovery_amount, loss_amount, cash_drawdown_amount, ending_balance, total_cashflow, discount_cashflow, negative_balance, discount_loss] # calculate with detailed method ending_date = detailed_calc_result.query('negative_balance == 1').monthIndex.values[0] selected_data = detailed_calc_result.ix[detailed_calc_result.monthIndex <= ending_date, :] life_time_value = selected_data['discount_cashflow'].sum() effective_life = np.sum(selected_data.discount_cashflow * selected_data.monthIndex) / np.sum(selected_data.discount_cashflow) total_loss_w_discount = np.sum(selected_data.discount_loss) # calculate by prorate pd lifetime_pd_lookup = final_cum_pd.ix[np.digitize([effective_life], final_cum_pd.month_i) - 1, 'cumulative_pd'].values[0] ead = original_balance lifetime_el = lifetime_pd_lookup * lgd * ead discount_at_half_effective_life = lifetime_el /((1 + yr_int_rate / 12) ** (effective_life / 2)) input_summary = pd.DataFrame([original_balance, term_month, yr_int_rate, month_prepay, lgd, month_drawdown_rate]).T input_summary.columns = ['original_balance', ' term_month', ' yr_int_rate', ' month_prepay', ' lgd', ' month_drawdown_rate'] output_compare = pd.DataFrame([ending_date, life_time_value, effective_life, total_loss_w_discount, lifetime_pd_lookup, lifetime_el, discount_at_half_effective_life]).T output_compare.columns = ['ending_date', ' life_time_value', ' effective_life', ' total_loss_w_discount', ' lifetime_pd_lookup', ' lifetime_el', ' discount_at_half_effective_life'] output_sumary = pd.concat([input_summary, output_compare], axis = 1) return output_sumary
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, 1), np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'begin'), 4) # end assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0), np.pmt(0.08 / 12, 5 * 12, 15000., 0, 'end'), 4) assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000., 0, 0), np.pmt(0.08 / 12, 5 * 12, 15000., 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., 1), np.nper(0.075, -2000, 0, 100000., 'begin'), 4) # end assert_almost_equal(np.nper(0.075, -2000, 0, 100000.), np.nper(0.075, -2000, 0, 100000., 'end'), 4) assert_almost_equal(np.nper(0.075, -2000, 0, 100000., 0), np.nper(0.075, -2000, 0, 100000., 'end'), 4)
def cashflow_levered(purchase_price=PURCHASE_PRICE, rent=RENT, years=5): net_cash_flow = [] df = pd.DataFrame(columns=[range(0, years)], index=INDEXES) down_payment_value = purchase_price * item['down_payment'] loan_fees = purchase_price * item.get('loan_fees', 0.01) # get the initial value net_cash_flow.append((down_payment_value + loan_fees + purchase_price * ACQUISITION_COST) * -1.0) # handle the middle value series = [] for year in xrange(0, years): series.append( cal_current_operation_flow(purchase_price, rent, year)) df = pd.concat(series, axis=1, keys=range(1, years + 1)) df = pd.DataFrame.transpose(df) net_sale_price = purchase_price * math.pow( 1 + HOME_PRICE_APPRECIATION, years) * (1 - DISPOSTITION_COST) net_cash_flow += df[UNLEVERAGED_CASH_FLOW_STR].tolist() yearly_loan_payment_point = np.pmt( item['loan_interest_rate'] / 12, 12 * 30, purchase_price * (1 - item['down_payment'])) * 12 # 30 years for i in range(1, len(net_cash_flow)): net_cash_flow[i] += yearly_loan_payment_point # handle the last point loan_balance_start_point = purchase_price - down_payment_value sum_temp = 0 for i in range(1, years + 1): sum_temp += yearly_loan_payment_point / math.pow( 1 + item['loan_interest_rate'], i) loan_balance_pv_of_final_point = loan_balance_start_point + sum_temp final_loan_balance = np.fv(item['loan_interest_rate'] / 12, 12 * years, 0, -loan_balance_pv_of_final_point) net_cash_flow[ -1] = net_cash_flow[-1] + net_sale_price - final_loan_balance # add loan cash flow #print net_cash_flow irr_value = round(irr(net_cash_flow), 4) cap_rate = round(abs(1.0 * net_cash_flow[1] / net_cash_flow[0]), 4) #print cap_rate * 100, irr_value * 100 returns = [df[INDEXES[0]].tolist()] revenue = [df[INDEXES[i]].tolist() for i in range(1, 4)] expenses = [df[INDEXES[i]].tolist() for i in range(4, 13)] return { "flow": { "returns": returns, "revenue": revenue, "expenses": expenses }, "cap_rate": cap_rate * 100, "irr": irr_value * 100 }
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
def tvmm(pval=None, fval=None, pmt=None, nrate=None, nper=None, due=0, pyr=1, noprint=True): """Computes the missing argument (set to ``None``) in a model relating the present value, the future value, the periodic payment, the number of compounding periods and the nominal interest rate in a cashflow. Args: pval (float, list): Present value. fval (float, list): Future value. pmt (float, list): Periodic payment. nrate (float, list): Nominal interest rate per year. nper (int, list): Number of compounding periods. due (int): When payments are due. pyr (int, list): number of periods per year. noprint (bool): prints enhanced output Returns: Argument set to None in the function call. **Details** The ``tvmmm`` function computes and returns the missing value (``pmt``, ``fval``, ``pval``, ``nper``, ``nrate``) in a model relating a finite sequence of payments made at the beginning or at the end of each period, a present value, a future value, and a nominal interest rate. The time intervals between consecutive payments are assumed to be equal. For internal computations, the effective interest rate per period is calculated as ``nrate / pyr``. **Examples** This example shows how to find different values for a loan of 5000, with a monthly payment of 130 at the end of the month, a life of 48 periods, and a interest rate of 0.94 per month (equivalent to a nominal interest rate of 11.32%). For a loan, the future value is 0. * Monthly payments: Note that the parameter ``pmt`` is set to ``None``. >>> tvmm(pval=5000, nrate=11.32, nper=48, fval=0, pyr=12) # doctest: +ELLIPSIS -130.00... When the parameter ``noprint`` is set to ``False``, a user friendly table with the data computed by the function is print. >>> tvmm(pval=5000, nrate=11.32, nper=48, fval=0, pyr=12, noprint=False) # doctest: +ELLIPSIS Present Value: ....... 5000.00 Future Value: ........ 0.00 Payment: ............. -130.01 Due: ................. END No. of Periods: ...... 48.00 Compoundings per Year: 12 Nominal Rate: ....... 11.32 Effective Rate: ..... 11.93 Periodic Rate: ...... 0.94 * Future value: >>> tvmm(pval=5000, nrate=11.32, nper=48, pmt=pmt, fval=None, pyr=12) # doctest: +ELLIPSIS -0.0... * Present value: >>> tvmm(nrate=11.32, nper=48, pmt=pmt, fval = 0.0, pval=None, pyr=12) # doctest: +ELLIPSIS 5000... All the arguments support lists as inputs. When a argument is a list and the ``noprint`` is set to ``False``, a table with the data is print. >>> tvmm(pval=[5, 500, 5], nrate=11.32, nper=48, fval=0, pyr=12, noprint=False) # doctest: +ELLIPSIS # pval fval pmt nper nrate erate prate due ------------------------------------------------------ 0 5.00 0.00 -0.13 48.00 11.32 11.93 0.94 END 1 500.00 0.00 -0.13 48.00 11.32 11.93 0.94 END 2 5.00 0.00 -0.13 48.00 11.32 11.93 0.94 END * Interest rate: >>> tvmm(pval=5000, nper=48, pmt=pmt, fval = 0.0, pyr=12) # doctest: +ELLIPSIS 11.32... * Number of periods: >>> tvmm(pval=5000, nrate=11.32/12, pmt=pmt, fval=0.0) # doctest: +ELLIPSIS 48.0... """ #pylint: disable=too-many-arguments #pylint: disable=too-many-branches numnone = 0 numnone += 1 if pval is None else 0 numnone += 1 if fval is None else 0 numnone += 1 if nper is None else 0 numnone += 1 if pmt is None else 0 numnone += 1 if nrate is None else 0 if numnone > 1: raise ValueError('One of the params must be set to None') if numnone == 0: pmt = None if pmt == 0.0: pmt = 0.0000001 nrate = numpy.array(nrate) if pval is None: result = numpy.pv(rate=nrate/100/pyr, nper=nper, fv=fval, pmt=pmt, when=due) elif fval is None: result = numpy.fv(rate=nrate/100/pyr, nper=nper, pv=pval, pmt=pmt, when=due) elif nper is None: result = numpy.nper(rate=nrate/100/pyr, pv=pval, fv=fval, pmt=pmt, when=due) elif pmt is None: result = numpy.pmt(rate=nrate/100/pyr, nper=nper, pv=pval, fv=fval, when=due) else: result = numpy.rate(pv=pval, nper=nper, fv=fval, pmt=pmt, when=due) * 100 * pyr if noprint is True: if isinstance(result, numpy.ndarray): return result.tolist() return result nrate = nrate.tolist() if pval is None: pval = result elif fval is None: fval = result elif nper is None: nper = result elif pmt is None: pmt = result else: nrate = result params = _vars2list([pval, fval, nper, pmt, nrate]) pval = params[0] fval = params[1] nper = params[2] pmt = params[3] nrate = params[4] # raise ValueError(nrate.__repr__()) erate, prate = iconv(nrate=nrate, pyr=pyr) if len(pval) == 1: if pval is not None: print('Present Value: ....... {:8.2f}'.format(pval[0])) if fval is not None: print('Future Value: ........ {:8.2f}'.format(fval[0])) if pmt is not None: print('Payment: ............. {:8.2f}'.format(pmt[0])) if due is not None: print('Due: ................. {:s}'.format('END' if due == 0 else 'BEG')) print('No. of Periods: ...... {:8.2f}'.format(nper[0])) print('Compoundings per Year: {:>5d}'.format(pyr)) print('Nominal Rate: ....... {:8.2f}'.format(nrate[0])) print('Effective Rate: ..... {:8.2f}'.format(erate)) print('Periodic Rate: ...... {:8.2f}'.format(prate)) else: if due == 0: sdue = 'END' txtpmt = [] for item, _ in enumerate(pval): txtpmt.append(pmt[item][-1]) else: sdue = 'BEG' txtpmt = [] for item, _ in enumerate(pval): txtpmt.append(pmt[item][0]) maxlen = 5 for value1, value2, value3, value4 in zip(pval, fval, txtpmt, nper): maxlen = max(maxlen, len('{:1.2f}'.format(value1))) maxlen = max(maxlen, len('{:1.2f}'.format(value2))) maxlen = max(maxlen, len('{:1.2f}'.format(value3))) maxlen = max(maxlen, len('{:1.2f}'.format(value4))) len_aux = len('{:d}'.format(len(pval))) fmt_num = ' {:' + '{:d}'.format(maxlen) + '.2f}' fmt_num = '{:<' + '{:d}'.format(len_aux) + 'd}' + fmt_num * 7 + ' {:3s}' # fmt_shr = '{:' + '{:d}'.format(len_aux) + 's}' fmt_hdr = ' {:>' + '{:d}'.format(maxlen) + 's}' fmt_hdr = '{:' + '{:d}'.format(len_aux) + 's}' + fmt_hdr * 7 + ' due' txt = fmt_hdr.format('#', 'pval', 'fval', 'pmt', 'nper', 'nrate', 'erate', 'prate') print(txt) print('-' * len_aux + '-' * maxlen * 7 + '-' * 7 + '----') for item, _ in enumerate(pval): print(fmt_num.format(item, pval[item], fval[item], txtpmt[item], nper[item], nrate[item], erate[item], prate[item], sdue))
def test_pmt(self): assert_almost_equal(np.pmt(0.08 / 12, 5 * 12, 15000), -304.146, 3)
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
import numpy as np # Derive the equivalent monthly mortgage rate from the annual rate mortgage_rate_periodic = (1 + mortgage_rate)**(1 / 12) - 1 # How many monthly payment periods will there be over 30 years? mortgage_payment_periods = 30 * 12 # Calculate the monthly mortgage payment (multiply by -1 to keep it positive) periodic_mortgage_payment = -1 * np.pmt( mortgage_rate_periodic, mortgage_payment_periods, mortgage_loan) print("Monthly Mortgage Payment: " + str(round(periodic_mortgage_payment, 2)))
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 test_pmt(self): assert_almost_equal(np.pmt(0,10,1000),-100)
def calcLCOH(self, elecFraction = None, heatFraction = None, form = 'H'): #fractions should be in terms of power incident on the dish, not the receiver if elecFraction == None: elecFraction = self.elecFrac if heatFraction == None: heatFraction = self.heatFrac #calculates sizing of installation default 1MW collectorArea = self.cspNameplate/(heatFraction*0.001) #MW / 0.1MW per m2 self.pvNameplate = collectorArea*elecFraction*0.001 totalNameplate = self.pvNameplate+self.cspNameplate #calculates system cost based on type of system if form == 'H': self.installCost = (self.pvCost+self.collectorCost)*(1+self.markup)*collectorArea elif form == 'PV' or heatFraction == 0: self.installCost = (self.pvCost)*(1+self.markup)*collectorArea elif form == 'TH' or elecFraction ==0: self.installCost = (self.collectorCost)*(1+self.markup)*collectorArea else: print('Invalid entries. Check calcLCOH call') quit() #depreciation schedule depSch = [] if self.MACRS == 'no': depSch = [0.050, 0.0950,0.0855,0.0750,0.0693,0.0623,0.059,0.0590, 0.0591,0.0590,0.0591,0.0590,0.0591,0.0590,0.0591,0.0315] for i in range(int(self.lifetime)-len(depSch)): depSch.append(0) else: depSch = [0.20,0.32,0.192,0.1152,0.0576] for i in range(int(self.lifetime)-len(depSch)): depSch.append(0) #loan payment setup Loan_Payment = -np.pmt(self.interestRate, self.loanTerm, self.installCost*self.debtToEquity) #Set NPV Lists PV = [] CPV_gen = [] CSP_gen = [] PV_CSP = [] PV_CPV = [] OMCSP = [] OMCPV = [] OMHybrid = [] Dep = [] Principal = [] Interest = [] Balance = self.debtToEquity*self.installCost CPV_savings = [] Subsidies = [] Dep_shield = [] Int_shield = [] Tax_shield = [] Cash_effect = [] #calculates annual generation pvGen = self.DNI*365*elecFraction*collectorArea cspGen = self.DNI*365*heatFraction*collectorArea #first year subsidies calculated outside the loop Fed_sub = (self.installCost*self.fedITC) State_sub = (self.installCost*self.stateITC) lumpIncentive = self.stateEPBB*totalNameplate*1000000 Subsidies.append(Fed_sub+State_sub+lumpIncentive) #Calculates lifetime financials for given parameters for i in range(int(self.lifetime)): CPV_gen.append((pvGen*((1-self.pvDegradation)**i))) CSP_gen.append((cspGen*((1-self.cspDegradation)**i))) PV_CPV.append((CPV_gen[i])/((1+self.discountRate)**(i+1))) PV_CSP.append((CSP_gen[i])/((1+self.discountRate)**(i+1))) OMCSP.append(self.cspOM*self.cspNameplate*1000*(1+self.omEscalation)**i) OMCPV.append(self.pvOM*self.pvNameplate*1000*(1+self.omEscalation)**i) OMHybrid.append(OMCSP[i] + OMCPV[i]) Interest.append((Balance*self.interestRate)) Principal.append((Loan_Payment-Interest[i])) Balance -= Principal[i] if pvGen==0 or cspGen==0: #if calculating pure LCOE or LCOH, ignore CPV savings CPV_savings.append(0) else: CPV_savings.append((CPV_gen[i]*self.elecCost)) if i < self.statePBIduration: montlyIncentive = self.statePBI*(CSP_gen[i]+CPV_gen[i]) else: montlyIncentive = 0 if len(Subsidies)<self.lifetime: Subsidies.append(montlyIncentive) Dep.append((depSch[i]*self.installCost)) Dep_shield.append((Dep[i]*self.taxRate)) Int_shield.append((Interest[i]*self.taxRate)) Tax_shield.append(Dep_shield[i]+Int_shield[i]) Cash_effect.append(((CPV_savings[i]+Tax_shield[i]+Subsidies[i]-OMHybrid[i]*(1-self.taxRate)-Principal[i]-Interest[i]))) PV.append(((Cash_effect[i])/math.pow(1+self.discountRate,i+1))) NPV = sum(PV)-self.installCost if pvGen != 0 and cspGen != 0: NPV_Energy = sum(PV_CSP) else: NPV_Energy = sum(PV_CSP)+sum(PV_CPV) self.LCOEnergy = -100*NPV/NPV_Energy self.priceChange = (self.LCOEnergy - self.fuelCost)/self.fuelCost fuel_displaced = (self.fuelCost*np.mean(cspGen))/100 avg_savings = -np.mean(OMHybrid)+np.mean(CPV_savings)+np.mean(fuel_displaced) self.payback = (self.installCost + Loan_Payment)/avg_savings
def pmt(principle, annual_interest_rate, loan_term): return abs(numpy.pmt(monthly_interest_rate(annual_interest_rate), loan_term, float(principle)))
print "Present value", np.pv(0.03 / 4, 5 * 4, -10, fval) #计算净现值。不明白? #按折现率计算的净现金流之和 cashflows = np.random.randint(100, size=5) cashflows = np.insert(cashflows, 0, -100) print "Cashflows:", cashflows print "Net present value:", np.npv(0.03, cashflows) #内部收益率 #净现值为0时的有效利率 print "Internal rate of return", np.irr(cashflows) #分期付款 #借30年,年利率为10%. 等额本息 print "Payment:", np.pmt(0.10 / 12, 12 * 30, 1000000) #计算付款期数 print "Number of payments:", np.nper(0.10 / 12, -100, 9000) #等额本金计算。麻烦点,每个月要算利息 #窗函数 #巴特利特窗 window = np.bartlett(42) plt.clf() plt.plot(window) plt.savefig('images/bartlett.png', format='png') #布莱克曼窗
def single_pmt(pv, n, rate, fv, pmt): np.pmt(rate/n, n, pv, fv, when = 'end')
#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:
# How many mortgage payment periods are there in a year? annual_payment_periods = 12 # What is the annual mortgage interest rate? mortgage_rate = 0.0375 # Derive the equivalent monthly mortgage rate from the annual rate mortgage_rate_periodic = (1 + mortgage_rate)**(1 / annual_payment_periods) - 1 # How many payment periods will there be in total? mortgage_payment_periods = mortgage_length * annual_payment_periods # Calculate the monthly mortgage payment (multiply by -1 to keep it positive) periodic_mortgage_payment = -1 * np.pmt(rate=mortgage_rate_periodic, nper=mortgage_payment_periods, pv=mortgage_loan) print("Monthly Mortgage Payment: " + str(round(periodic_mortgage_payment, 2))) # Calculate the amount of the first loan payment that will go towards interest initial_interest_payment = mortgage_rate_periodic * mortgage_loan print("Initial Interest Payment: " + str(round(initial_interest_payment, 2))) # Calculate the amount of the first loan payment that will go towards principal initial_principal_payment = periodic_mortgage_payment - initial_interest_payment print("Initial Principal Payment: " + str(round(initial_principal_payment, 2))) import numpy as np # Initialize the principal remaining array with length equal to the number of payment periods principal_remaining = np.repeat(0.0, mortgage_payment_periods)
def Payment(): a = np.pmt(eval(e1.get()), eval(e2.get()), eval(e3.get())) a = 0 - a v1.set(a)
def amortization_table(interest_rate, years, payments_year, principal, addl_principal=0, start_date=date.today()): """ Calculate the amortization schedule given the loan details Args: interest_rate: The annual interest rate for this loan years: Number of years for the loan payments_year: Number of payments in a year principal: Amount borrowed addl_principal (optional): Additional payments to be made each period. Assume 0 if nothing provided. must be a value less then 0, the function will convert a positive value to negative start_date (optional): Start date. Will start on first of next month if none provided Returns: schedule: Amortization schedule as a pandas dataframe summary: Pandas dataframe that summarizes the payoff information """ # Ensure the additional payments are negative if addl_principal > 0: addl_principal = -addl_principal # Create an index of the payment dates rng = pd.date_range(start_date, periods=years * payments_year, freq='MS') rng.name = "Payment_Date" # Build up the Amortization schedule as a DataFrame df = pd.DataFrame(index=rng, columns=[ 'Payment', 'Principal', 'Interest', 'Addl_Principal', 'Curr_Balance' ], dtype='float') # Add index by period (start at 1 not 0) df.reset_index(inplace=True) df.index += 1 df.index.name = "Period" # Calculate the payment, principal and interests amounts using built in Numpy functions per_payment = np.pmt(interest_rate / payments_year, years * payments_year, principal) df["Payment"] = per_payment 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) # Round the values df = df.round(2) # Add in the additional principal payments df["Addl_Principal"] = addl_principal # Store the Cumulative Principal Payments and ensure it never gets larger than the original principal df["Cumulative_Principal"] = (df["Principal"] + df["Addl_Principal"]).cumsum() df["Cumulative_Principal"] = df["Cumulative_Principal"].clip( lower=-principal) # Calculate the current balance for each period df["Curr_Balance"] = principal + df["Cumulative_Principal"] # Determine the last payment date try: last_payment = df.query("Curr_Balance <= 0")["Curr_Balance"].idxmax( axis=1, skipna=True) except ValueError: last_payment = df.last_valid_index() last_payment_date = "{:%m-%d-%Y}".format(df.loc[last_payment, "Payment_Date"]) # Truncate the data frame if we have additional principal payments: if addl_principal != 0: # Remove the extra payment periods df = df.ix[0:last_payment].copy() # Calculate the principal for the last row df.ix[last_payment, "Principal"] = -(df.ix[last_payment - 1, "Curr_Balance"]) # Calculate the total payment for the last row df.ix[last_payment, "Payment"] = df.ix[last_payment, ["Principal", "Interest"]].sum() # Zero out the additional principal df.ix[last_payment, "Addl_Principal"] = 0 # Get the payment info into a DataFrame in column order payment_info = (df[["Payment", "Principal", "Addl_Principal", "Interest"]].sum().to_frame().T) # Format the Date DataFrame payment_details = pd.DataFrame.from_items([ ('payoff_date', [last_payment_date]), ('Interest Rate', [interest_rate]), ('Number of years', [years]) ]) # Add a column showing how much we pay each period. # Combine addl principal with principal for total payment payment_details["Period_Payment"] = round(per_payment, 2) + addl_principal payment_summary = pd.concat([payment_details, payment_info], axis=1) return df, payment_summary
def test_pmt(self): assert_almost_equal(np.pmt(0.08/12,5*12,15000), -304.146, 3)
def payment(self, rate_per_year, num_periods, amount): payment = np.pmt(rate_per_year / 100 / 12, num_periods * 12, amount) return payment
def emi_calculation(loan_amount, tenture=5, interest_rate=15.00): monthly_emi = -1 * np.pmt(interest_rate / 12, tenture * 12, int(loan_amount)) return interest_rate, monthly_emi, tenture, loan_amount, 2
for i in range(len(gas_prices)): eff = np.arange(2, 5, 0.1) # COP rate = np.arange(0.05, 0.3, 0.01) # electricity price in $/kWh gas_price = gas_prices[i] # $/mmBTU GJ_per_MMBTU = 1.055 tonnes_per_GJ = 0.05 # NG emissions intensity gas_price += 20*tonnes_per_GJ*GJ_per_MMBTU #add CO2 price print('gas_price = ' + str(gas_price)) service_demand = 20 # mmBTU/yr gas_eff = 0.9 # COP gas_costs = service_demand / gas_eff * gas_price # $/yr y, x = np.meshgrid(eff, rate) # x-axis is rate, y-axis is efficiency mmbtu_per_kWh_thermal = 3.6e-3/GJ_per_MMBTU COP_to_HSPF = 3.6/GJ_per_MMBTU CRF = -np.pmt(0.05, 15, 1) print('CRF = ' + str(CRF)) net_fuel_cost = service_demand / y * x / mmbtu_per_kWh_thermal - gas_costs # mmBTU / COP * $/kWh / (mmBTU/kWh) breakeven_cap_cost = -net_fuel_cost / CRF plt.close() plt.figure() vscale = 2500 y *= COP_to_HSPF # convert to HSPF plot = plt.contourf(x, y, breakeven_cap_cost, cmap='coolwarm_r', vmin=-vscale, vmax=vscale) #plt.clabel(plot) plt.colorbar() #plt.clim([-5000, 2500]) plt.title('Breakeven Capital Cost Relative to Gas Furnace ($)\n' + scenario_names[i])