# -*- coding: utf-8 -*-
"""
Created on Sun May 15 23:56:07 2022

@author: Rohil
"""

# -*- coding: utf-8 -*-
"""
Created on Sat May 14 15:48:19 2022

@author: Rohil
"""
"""
The project is an immunisation problem
First we clean the data and compute all necessary metrics
Later we run a linear programming problem using all 3 of Rendington's conditions
for immunisation'

"""
#importing all necessary packages
import pandas as pd
import numpy as np
import os
import numpy_financial as npf
from scipy.optimize import linprog
from scipy import optimize
import math

#importing data
os.chdir(r"C:\Users\Rohil\Desktop\FIP Python project")
liabilities = pd.read_excel("liabilities.xlsx")
bonds = pd.read_excel("bonds.xlsx", index_col = "bond_id")

#basic data cleaning
liabilities.columns = ["Cashflow","Time"]
liabilities = liabilities.groupby(liabilities.Time).sum()
liabilities.reset_index(inplace = True)

#scanning the data
bonds.info()

#defining a function to compute YTM of each bond
#coupons are assumed  to be annual
def ytm_calculator(n, pv, pmt, fv =100):
    cf = [-1*pv]
    for i in np.arange(0,n):
        cf.append(pmt*fv*0.01)
    cf[-1] += fv
    return npf.irr(cf) 

#computing YTM for all bonds in the dataset
temp_list = []
for i in range(len(bonds.index)):
    ytm = ytm_calculator(bonds.iloc[i,3], bonds.iloc[i,7], bonds.iloc[i,2])
    temp_list.append(ytm)
    
#taking a weighted average of the YTM of all bonds
bonds['YTM'] = temp_list
bonds["Weighted_YTM"] = bonds["YTM"]*bonds["weight"]
Avg_YTM = bonds.Weighted_YTM.sum()/bonds.weight.sum()
#Average YTM is 0.04565168775736443

#the average ytm is going to be the discounting factor for our calculations
r= Avg_YTM
v = 1/(1 + r)

#Computing Present Value of liabilities
liabilities["PV"] = liabilities["Cashflow"]*(v**(liabilities["Time"]))
PV_L = round(liabilities.PV.sum())
PV_L

#Computing Modified Duration of liabilities
liabilities['ModDur'] = liabilities["Cashflow"]*liabilities["Time"]*(v**(liabilities["Time"]+1))
ModDur_L = round(liabilities.ModDur.sum()/PV_L,5)
ModDur_L

#Computing Convexity of liabilities
liabilities["convexity"] = liabilities["Cashflow"]*liabilities["Time"]*(liabilities["Time"]+1)*(v**(liabilities["Time"]+2))
Convexity_L = round(liabilities.convexity.sum())
Convexity_L

#Computing modified duration and convexity of bonds
for i in range(0,len(bonds)):
    term = math.floor(bonds.iloc[i,3] *2)/2
    coupon = bonds.iloc[i,2]
    ytm = bonds.iloc[i,10]
    PV0 = (npf.pv((r), term, coupon, 100)) * -1
    PVminus = (npf.pv((r) - 0.0001, term, coupon, 100)) * -1
    PVplus = (npf.pv((r) + 0.0001, term, coupon, 100)) * -1
    bonds.loc[i+1, "ModDur"] = (PVminus - PVplus) / (2 * 0.0001 * PV0)
    bonds.loc[i+1, "Convexity"] = (PVminus + PVplus - (2 * PV0)) / ((0.0001 ** 2) * PV0)


# Optimizing to find the portfolio that matches the conditions for immunization
#And matches our requirement for lowest possible cost
PV_A = np.array(bonds.trade_price)

lhs = np.array([PV_A, bonds["ModDur"] * PV_A])
rhs = np.array([PV_L, ModDur_L * PV_L])

opt = optimize.linprog(c=PV_A, A_eq=lhs, b_eq=rhs)
X = opt.x
weights = (X * PV_A) / (X * PV_A).sum()
Convexity_A = (opt.x * PV_A * bonds["Convexity"]).sum()
bonds["units"] = opt.x

#Checking for all 3 conditions of Redington IMmunization theory
Total_PV_A = (opt.x * PV_A).sum()
round((Total_PV_A - PV_L)/Total_PV_A)
#Present Value of assets and liabilities are equal

Total_ModDur_A = (weights * bonds['ModDur']).sum()
round(Total_ModDur_A - ModDur_L)
#Volatility of assets and liabilities are equal

Convexity_A > Convexity_L
#Convexity of assets is more than convexity of liabilities
#All 3 conditions are satisfied
#Hence we can conclude that the portfolio has been immunised

#Displaying the final output 
final_portfolio = bonds[["trade_price","units"]]
print(final_portfolio)

#Showing final cost of portfolio
final_portfolio["cost"] = final_portfolio["trade_price"] * final_portfolio["units"]
final_portfolio.cost.sum()