/***********************************************************************************************************/
/***********************************************************************************************************/
*This do-file produces Figure 2 ("Cost of debt")
* 
*Input
*- Master_CapitalIQ_Compustat.dta
*- Master_Dealscan.dta
/***********************************************************************************************************/
/***********************************************************************************************************/


/****************************************************************/
/**********   0. Intros   ***************************************/
/****************************************************************/
set more off
global path "C:\Users\tobia\Dropbox\6_WIP Papers\Trends in Corporate Borrowing"

/****************************************************************/
/**********   1. Graph for the U.S.  *****************************/
/****************************************************************/

	*Left-hand picture: Cost of oustanding debt (based on Compustat)
	
		use "$path/2_IntermediateFiles/Master_CapitalIQ_Compustat.dta", clear
		
			*Sample
			keep if fic == "USA"
			
			*Drop missing or zero value for total debt
			gen         total_debt_Compustat = dlc + dltt 
			drop        if total_debt_Compustat == .
			drop 		if total_debt_Compustat <= 0
			drop 		if 	xint == .
			drop 		if xint <= 0
			
			*Sanity check
			duplicates report gvkey year
		
			*Variables		
			gen interest = xint/total_debt_Compustat
			
			*Graph	
			collapse (mean) LIBOR mean_interest = interest  (p10) p10_interest = interest (p25) p25_interest = interest (p75) p75_interest = interest (p90) p90_interest = interest  [aw=total_debt_Compustat], by(year)
			
			gen p75p25 = p75_interest - p25_interest
		
			#d ;
			twoway 	(rarea p25_interest p75_interest year, sort color(gs10)  lwidth(vthin) yaxis(1)) 
					(line mean_interest year, color (black))
					(line LIBOR year, color (gs12))
					, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
					legend(pos(6) ring(50) col(1)
					order(2 "Interest expense / Debt" 1 "Interest expense / Debt (p25-p75)" 3 "LIBOR"))
					xtitle("Year") xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
					ytitle("Interest expense / Debt") ylabel(0.00(0.01)0.08, labsize(small) angle(90) nogrid)
					name(A, replace)
					;
			#d cr

			*Same as tabstat (to be able to read off exact numbers)
			tabstat p25_interest mean_interest p75_interest, by(year) format(%9.3f)

			
			
	*Right-hand picture: Cost of newly issued loans (based on Dealscan)
		use 	"$path/2_IntermediateFiles/Master_Dealscan.dta", clear
			
			*Sample
			keep 	if Country == "USA"
			keep if BaseRate == "LIBOR"
			keep if inlist(Currency, "United States Dollars")
			
			*TCB
			gen UFR_forTCB = UFR
			bys year: egen UFRmean = mean(UFR)	
			replace UFR_forTCB = UFRmean if UFR == .
			replace AISU = 0 if LoanType2 == "TermLoan" /*replace with non-missing value for TCB-calculation*/
			gen meanusage = 1 
			replace meanusage = 0.3 if LoanType2 == "Revolver"
			gen TCB = UFR_forTCB / Maturity + (1-meanusage)*AISU + meanusage * AISD  
				
			*Sanity check
			duplicates report FacilityID
			
				/**********************/
				preserve
					keep FacilityID year TCB
					keep if TCB != .
					save "$path/2_IntermediateFiles/TCB_US", replace
				restore
				/********************/
				
			*Graph
			collapse (mean) AISD AISU UFR TCB[aw=FacilityAmt], by(year)
			
			#d ;
			twoway 	(line AISD year, color(black) ) 
				(line AISU year, color (gs12))
				(line UFR year, color (black) lpattern(dash))
				(line TCB year, color(black) lpattern(dot))
				, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
				legend(pos(6) ring(50) col(1)
				order(1 "All-in-spread-drawn (AISD)" 2 "All-in-spread-undrawn (AISU)" 3 "Upfront fee" 4 "Total cost of borrowing (TCB)"))
				xtitle("Year") xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
				ytitle("Spread and fess on new loans (in bps)") ylabel(0.00(50)400, labsize(small) angle(90) nogrid)
				name(B, replace)
				;
			#d cr
			
			*Same as tabstat (to be able to read off exact numbers)
			tabstat AISD AISU UFR, by(year) format(%9.3f)


	*Combine left-hand and right-hand graph into one figure
			graph combine A B, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
			graph		export "$path/3_Results/Figure2.pdf", replace
			
			

			
/****************************************************************/
/**********   2. Graph for Europe  ******************************/
/****************************************************************/

	*Left-hand picture: Cost of oustanding debt (based on Compustat)
	
		use "$path/2_IntermediateFiles/Master_CapitalIQ_Compustat.dta", clear
		
			*Sample
			keep if inlist(fic, "AUT", "BEL", "DEU", "FIN", "FRA", "GRC", "IRL", "ITA", "LUX") | inlist(fic, "NLD", "PRT", "ESP")
			
			*Drop missing or zero value for total debt
			gen         total_debt_Compustat = dlc + dltt 
			drop        if total_debt_Compustat == .
			drop 		if total_debt_Compustat <= 0
			drop 		if 	xint == .
			drop 		if xint <= 0

			*Sanity check
			duplicates report gvkey year
		
			*Variables		
			gen interest = xint/total_debt_Compustat
			
			*Graph	(similar as for the US, but with LIBOR_EUR in addition to LIBOR)
			collapse (mean) LIBOR_EUR LIBOR mean_interest = interest  (p10) p10_interest = interest (p25) p25_interest = interest (p75) p75_interest = interest (p90) p90_interest = interest  [aw=total_debt_Compustat], by(year)
			
			gen p75p25 = p75_interest - p25_interest
		
			*Similar to US, but with LIBOR_EUR in addition to LIBOR
			#d ;
			twoway 	(rarea p25_interest p75_interest year, sort color(gs10)  lwidth(vthin) yaxis(1)) 
					(line mean_interest year, color (black))
					(line LIBOR year, color (gs12))
					(line LIBOR_EUR year, color (gs12) lpattern(dash))
					, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
					legend(pos(6) ring(50) col(1)
					order(2 "Interest expense / Debt" 1 "Interest expense / Debt (p25-p75)" 3 "LIBOR (USD)" 4 "LIBOR (EUR)"))
					xtitle("Year") xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
					ytitle("Interest expense / Debt") ylabel(0.00(0.01)0.08, labsize(small) angle(90) nogrid)
					name(A, replace)
					;
			#d cr

			*Same as tabstat (to be able to read off exact numbers)
			tabstat p25_interest mean_interest p75_interest, by(year) format(%9.3f)

			
			
	*Right-hand picture: Cost of newly issued loans (based on Dealscan)
		use 	"$path/2_IntermediateFiles/Master_Dealscan.dta", clear
			
			*Sample (similar as for US, but with additional restrictions on base rate and currency)
			keep if inlist(Country, "Austria", "Belgium", "Germany", "Finland", "France", "Greece", "Ireland", "Italy", "Luxembourg") | inlist(Country, "Netherlands", "Portugal", "Spain")	
			keep if inlist(BaseRate, "LIBOR", "Euribor")
			keep if inlist(Currency, "Euro", "United Kingdom Pounds", "United States Dollars")
					
			*Drop missing value for AISU(Revolver) or AISD(all)
			drop if LoanType2 == "Revolver" & AISU == .
			drop if AISD == .
			
			*TCB
			gen UFR_forTCB = UFR
			bys year: egen UFRmean = mean(UFR)	
			replace UFR_forTCB = UFRmean if UFR == .
			replace AISU = 0 if LoanType2 == "TermLoan" /*replace with non-missing value for TCB-calculation*/
			gen meanusage = 1 
			replace meanusage = 0.3 if LoanType2 == "Revolver"
			gen TCB = UFR_forTCB / Maturity + (1-meanusage)*AISU + meanusage * AISD  
				
			*Sanity check
			duplicates report FacilityID
			
			
				/**********************/
				preserve
					keep FacilityID year TCB
					keep if TCB != .
					save "$path/2_IntermediateFiles/TCB_Europe", replace
				restore
				/********************/
				
			
			*Graph
			collapse (mean) AISD AISU UFR TCB [aw=FacilityAmt], by(year)
			
			#d ;
			twoway 	(line AISD year, color(black) ) 
				(line AISU year, color (gs12))
				(line UFR year, color (black) lpattern(dash))
				(line TCB year, color(black) lpattern(dot))
				, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
				legend(pos(6) ring(50) col(1)
				order(1 "All-in-spread-drawn (AISD)" 2 "All-in-spread-undrawn (AISU)" 3 "Upfront fee" 4 "Total cost of borrowing (TCB)"))
				xtitle("Year") xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
				ytitle("Spread and fess on new loans (in bps)") ylabel(0.00(50)400, labsize(small) angle(90) nogrid)
				name(B, replace)
				;
			#d cr
			
			*Same as tabstat (to be able to read off exact numbers)
			tabstat AISD AISU UFR, by(year) format(%9.3f)


	*Combine left-hand and right-hand graph into one figure
			graph combine A B, graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
			graph		export "$path/3_Results/Figure2 (Europe).pdf", replace