Loan calculator in Python

Today I was working on a prototype and needed to create the code to create a loan projection. Here you have a simple code for doing it including some elements like subsidy over interest and capital payments. In addition insurance payments are change based on inflation rates and with a lag in time.

The code also includes the creation of a excel as an output in which I have set style for column names, values and the insertion of a graph.

import array
from array import array
import numpy as np
import pandas as pan
from xlsxwriter.workbook import Workbook
from xlsxwriter.utility import xl_range
from xlsxwriter.utility import xl_rowcol_to_cell

monto = 2442900
descuento = 14620.62

plazo = 180 #Term
tasa = 0.113 # interest rate
life_insurance_rate= 0.6
damage_insurance_rate= 0.275
comision = ‘Financiada’
comision_ini = 0.015 # fee
valor_vivienda = monto # House value

seg_vida = monto /1000 * life_insurance_rate
seg_danos = monto /1000 * damage_insurance_rate * 1.16
pago_req = np.pmt(tasa/12,plazo,-monto,when=’end’)
pago_total = pago_req + seg_danos + seg_vida

if comision == ‘Financiada’:
comision_ini = monto*.015
else:
comision_ini = 0.0

balance = monto + comision_ini
interes_req = 0.0
capital_acre = 0.0
capital_apoyo = 0.0
capital = 0.0
apoyo = 0.0

data = np.zeros((360,10))
data[0][9] = monto

for i in range(1,plazo+1):
data[i][0] = i
data[i][1] = balance * tasa / 12 # interes requerido
data[i][2] = balance # Saldo inicial

#Calculo de seguros
if i == 60:
first_amount_5years = balance

if i > 59 and i 119:
seg_vida = first_amount_10years/1000 * life_insurance_rate
seg_danos = valor_vivienda *.85 * (1+.03*1.5)**10 /1000 * damage_insurance_rate * 1.16

if balance <=0:
seg_vida = 0.0
seg_danos = 0.0
# Calculo de intereses
interes_per = balance * tasa / 12
interes_acre = balance * .01 / 12
interes_apoyo = interes_per – interes_acre

#Calculo ∂e capital
capital_acre = min(balance, pago_req – interes_per + descuento – interes_acre – (pago_req – interes_per) – seg_vida – seg_danos)
capital_apoyo = max(0, min(pago_req – interes_per – capital_acre, balance – interes_acre))
capital = capital_acre + capital_apoyo

balance = max(0, balance – capital)

apoyo = interes_apoyo + capital_apoyo

data[i][3] = capital
data[i][4] = interes_acre
data[i][5] = apoyo
data[i][6] = seg_vida
data[i][7] = seg_danos
data[i][8] = interes_per + capital + seg_danos + seg_vida
data[i][9] = balance

# output to a csv file
file = open(‘tabla.csv’, ‘w+’)
file.write(‘Periodo,Interes requerido, Saldo inicial, Capital, Interes Acreditado, Apoyo, Seg. Vida, Seg. Danos, Pago Total, Saldo Final’)
for i in data:
file.write(‘\n’)
for a in i:
file.write(str(a)+’,’)
file.close()

# output to an excel file
wb = Workbook(‘Amortizacion.xlsx’)
encabezado = wb.add_format({‘bold’: True, ‘font_name’: ‘Calibri’, ‘font_size’: 12, ‘bottom’: 1})
number_format = wb.add_format({‘num_format’: ‘#,##0.00’})
encabezado.set_bottom(1)
sheet = wb.add_worksheet(‘Tabla_de_amortizacion’)
sheet.set_column(0,29,10)
sheet.set_zoom(80)
sheet.hide_gridlines(3)
sheet.write_string(2,0,’Periodo’,encabezado)
sheet.write_string(2,1,’Interes requerido’,encabezado)
sheet.write_string(2,2,’Saldo Inicial’,encabezado)
sheet.write_string(2,3,’Capital’,encabezado)
sheet.write_string(2,4,’Interes Acreditado’,encabezado)
sheet.write_string(2,5,’Apoyo’,encabezado)
sheet.write_string(2,6,’Seg. Vida’,encabezado)
sheet.write_string(2,7,’Seg. Danos’,encabezado)
sheet.write_string(2,8,’Pago Total’,encabezado)
sheet.write_string(2,9,’Saldo Final’,encabezado)
ld = len(data)
for row in range(0,ld):
v = data[row]
j = 0
for col in v:
sheet.write_number(row+3,j,max(col,0),number_format)
j+=1

chart = wb.add_chart({‘type’: ‘line’})
chart.add_series({
‘name’:’Capital’,
‘values’:’=Tabla_de_amortizacion!$D$’+str(4)+’:$D$’+str(plazo + 3),
‘line’:{‘color’: ‘#2e9fff’}
})
chart.add_series({
‘name’: ‘Interes Acr.’,
‘values’: ‘=Tabla_de_amortizacion!$E$’+str(4)+’:$E$’+str(plazo + 3),
‘line’: {‘color’: ‘#000072’}
})
chart.add_series({
‘name’: ‘Apoyo’,
‘values’: ‘=Tabla_de_amortizacion!$F$’+str(4)+’:$F$’+str(plazo + 3),
‘line’: {‘color’: ‘#9BBB59’}
})
chart.add_series({
‘name’: ‘Seg. Vida’,
‘values’: ‘=Tabla_de_amortizacion!$G$’+str(4)+’:$G$’+str(plazo + 3),
‘line’: {‘color’: ‘#E46C0A’}
})
chart.add_series({
‘name’: ‘Seg. Danos’,
‘values’: ‘=Tabla_de_amortizacion!$H$’+str(4)+’:$H$’+str(plazo + 3),
‘line’: {‘color’: ‘#00FF00’}
})
chart.add_series({
‘name’: ‘Pago Total’,
‘values’: ‘=Tabla_de_amortizacion!$I$’+str(4)+’:$I$’+str(plazo + 3),
‘line’: {‘color’: ‘#38C99E’}
})
chart.set_legend({‘position’: ‘bottom’})
chart.set_x_axis({
‘major_gridlines’: {
‘visible’: True,
‘line’: {‘width’: .01, ‘dash_type’: ‘dash’, ‘color’: ‘#FFFFFF’}
},
})
chart.set_y_axis({
‘major_gridlines’: {
‘visible’: True,
‘line’: {‘width’: .01, ‘dash_type’: ‘dash’, ‘color’: ‘#d3d3d3’}
},
})
chart.set_size({‘width’: 720, ‘height’: 576})
chart.set_chartarea({
‘border’: {‘none’: True},
‘fill’: {‘color’: ‘white’},
})

sheet.insert_chart(‘L4’, chart)

wb.close()