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])
