def mortgage_df(interest): rng = pd.date_range( start_date, periods=payments_per_year * term_remaining, freq='MS') rng.name = "Payment Date" df = pd.DataFrame(index=rng, columns=[ 'Payment', 'Principal Paid', 'Interest Paid', 'Ending Balance'], dtype='float') df.reset_index(inplace=True) df.index += 1 df.index.name = "Period" loan = Loan(principal=mortgage_remaining, interest=interest / 100, term=term_remaining, currency='£') for period in range(1, len(df)+1): schedule = loan.schedule(period) df.loc[period, 'Payment'] = round(schedule.payment, 2) df.loc[period, 'Ending Balance'] = round(schedule.balance, 2) df.loc[period, 'Interest Paid'] = round(schedule.total_interest, 2) df.loc[period, 'Principal Paid'] = round( mortgage_remaining - schedule.balance, 2) df = df.round(2) return df
df_A = mortgage_df(options['a']['interest_rate']) df_A """ --- # Option B """ df_B = mortgage_df(options['b']['interest_rate']) df_B """ --- # Option C """ df_C = mortgage_df(options['c']['interest_rate']) df_C """ --- """ loan = Loan(principal=200000, interest=.06, term=30) loan loan.schedule(1).payment loan.schedule(2).payment loan.schedule(3).payment
class Engine(object): """Docstring for Engine. """ def __init__(self, purchase_price, appraised_price, down_pct, interest, property_tax=6000, rent=1700, months=60, appreciation=.05, vacancy=.05, management=0.06, closing_cost=.015, insurance=100, capital_expenditure=.05, repair=.03, hoa=25, rent_increase=0.03): """ Parameters ---------- purchase_price : TODO appraised_price : TODO down_pct : TODO sell_at : TODO, optional appreciation : TODO, optional vacancy : TODO, optional management : TODO, optional property_tax : TODO, optional closing_cost : TODO, optiona insurrance : TODO, optional capital_expenditure : TODO, optional repair : TODO, optional """ if purchase_price < appraised_price: raise ValueError( 'Purchase price should be larger than bank appraised price.') self._purchase_price = purchase_price self._appraised_price = appraised_price self._down_pct = down_pct self._interest = interest self._rent = rent self._months = months self._appreciation = appreciation self._vacancy = vacancy self._management = management self._property_tax = property_tax self._closing_cost = closing_cost self._insurance = insurance self._capital_expenditure = capital_expenditure self._repair = repair self._hoa = hoa self._rent_increase = rent_increase self._loan = Loan(self.loan_amount, self._interest, term=30) def monthly_cash_flow(self, year): pct_cost = self._vacancy + self._management \ + self._capital_expenditure + self._repair dollor_cost = self._hoa + float(self._loan.monthly_payment) \ + self._property_tax / 12 + self._insurance rent_multiplier = (1 + self._rent_increase)**year return self._rent * rent_multiplier * (1 - pct_cost) - dollor_cost @property def years(self): return int(self._months / 12) @property def loan_amount(self): return self._appraised_price * (1 - self._down_pct) @property def inital_investment(self): val = self._appraised_price * (self._down_pct + self._closing_cost) \ + (self._purchase_price - self._appraised_price) return val @property def resale_value(self): return self._purchase_price * (1 + self._appreciation)**self.years @property def payoff_at_sale(self): return float(self._loan.schedule(self._months).balance) @property def profit_from_sale(self): return self.resale_value * (1 - 0.06) - self.payoff_at_sale @property def net_cash_flow(self): cash_flow = 0 for i in range(self.years): cash_flow += 12 * self.monthly_cash_flow(i) return cash_flow @property def net_profit(self): return self.profit_from_sale + self.net_cash_flow - self.inital_investment def annual_return(self, type='compound'): if type == 'compound': return (self.net_profit / self.inital_investment + 1)**(1 / self.years) - 1 if type == 'average': return self.net_profit / self.inital_investment / self.years raise NotImplementedError
#!/usr/bin/env python from mortgage import Loan loan = Loan(principal=200000, interest=.03, term=15) print('{:>4} {:>10s} {:>10s} {:>10s} {:>10s}'.format('', 'payment', 'interest', 'principal', 'remaining')) print('{:>4} {:>10s} {:>10s} {:>10s} {:>10s}'.format('', '-------', '--------', '---------', '---------')) for installment in loan.schedule(): print('{:>4}: {:>10.02f} {:>10.02f} {:>10.02f} {:>10.02f}'.format( installment.number, installment.payment, installment.interest, installment.principal, installment.balance)) loan.summarize
def CollateralisedMortgageObligationWaterfall(cmo): dfCashflows = pd.DataFrame(columns=[ 'Month', 'Senior_principal', 'Mezzanine_principal', 'Junior_principal', 'Senior_interest', 'Mezzanine_interest', 'Junior_interest' ]) principal = np.array([ cmo['trancheSenior_principalowed'], cmo['trancheMezzanine_principalowed'], cmo['trancheJunior_principalowed'] ]) interestClaim = np.array([ cmo['trancheSenior_interestclaim'], cmo['trancheMezzanine_interestclaim'], cmo['trancheJunior_interestclaim'] ]) interestRate = cmo['interest_rate'] paymentYears = cmo['payment_years'] if sum(principal) != float( cmo['total_size']) or sum(interestClaim) != float( cmo['total_size']): print( "Principal / Interest Claim Tranche allocations do not equate to the total size!" ) return paymentPeriods = paymentYears * 12 principalExJunior = np.resize(principal, len(principal) - 1) principalPaidSubtotal = [0, 0, 0] principalTrancheFull = [False, False] interestClaimExJunior = np.resize(interestClaim, len(interestClaim) - 1) interestClaimSubTotal = [0, 0, 0] interestPaidSubTotal = [0, 0, 0] interestTrancheFull = [False, False] loan = Loan(principal=float(sum(principal)), interest=float(interestRate), term=int(paymentYears)) for m in range(paymentPeriods): principalInPeriod = float(loan.schedule(m + 1).principal) interestInPeriod = float(loan.schedule(m + 1).interest) #allocate the principal payments to all tranches sequentially #except Junior when these are not full principalCashflowsForPeriod = np.array([0, 0, 0]) for i in range(len(principalExJunior)): if principalPaidSubtotal[i] < principal[i]: principalPaidSubtotal[i] += principalInPeriod principalCashflowsForPeriod[i] = principalInPeriod break else: principalTrancheFull[i] = True #when full, allocate to the Junior tranche TrueList = [j for j, x in enumerate(principalTrancheFull) if x] if len(TrueList) == len(principalExJunior): i += 1 principalPaidSubtotal[i] = +principalInPeriod principalCashflowsForPeriod[len(principalCashflowsForPeriod) - 1] = principalInPeriod #allocate the interest payments to all tranches sequentially #except Junior when these are not full interestCashflowsForPeriod = np.array([0, 0, 0]) for i in range(len(interestClaimExJunior)): if interestClaimSubTotal[i] < interestClaim[i]: interestClaimSubTotal[i] += principalInPeriod interestPaidSubTotal[i] += interestInPeriod interestCashflowsForPeriod[i] = interestInPeriod break else: interestTrancheFull[i] = True #when full, allocate to the Junior tranche TrueList = [j for j, x in enumerate(interestTrancheFull) if x] if len(TrueList) == len(interestClaimExJunior): i += 1 interestPaidSubTotal[i] += interestInPeriod interestCashflowsForPeriod[len(interestCashflowsForPeriod) - 1] = interestInPeriod #add the cashflows to the dataframe row = np.append(np.append(m + 1, principalCashflowsForPeriod), interestCashflowsForPeriod) dfCashflows.loc[len(dfCashflows)] = row dfCashflows.plot.area(x='Month', y=[ 'Senior_principal', 'Mezzanine_principal', 'Junior_principal', 'Senior_interest', 'Mezzanine_interest', 'Junior_interest' ], color=[ 'navy', 'royalblue', 'slategrey', 'steelblue', 'cornflowerblue', 'aqua' ]) plt.suptitle('CMO Cashflows by Tranche Type \n Size ' + str(sum(principal) / 1000000) + 'mm, ' + str(paymentYears) + ' years, Interest Rate ' + str(interestRate)) plt.show() print(dfCashflows[[ 'Senior_principal', 'Mezzanine_principal', 'Junior_principal', 'Senior_interest', 'Mezzanine_interest', 'Junior_interest' ]].sum())