Question

20 years ago XYZ Inc. took out a $100 million loan with an interest rate of 6 percent compounded quarterly over its 35 year life. With interest rates now so low, XYZ is now looking to potentially refinance the loan.

A. Show how much interest was paid on the loan last year

B. If new debt with an interest rate of 4.75 percent compounded quarterly can be raised with a 2 percent flotation cost, should they refinance this loan?

Please show using Excel formulas

Answer 1

A

Existing monthly payment:

Monthly payment	=	[P × R × (1+R)^N ] / [(1+R)^N -1]
Using the formula:
Loan amount	P	$                                                  100,000,000
Rate of interest per period:
Annual rate of interest		6.000%
Frequency of payment	=	Once in 1 month period
Numer of payments in a year	=	12/1 =	12
Rate of interest per period	R	0.06 /12 =	0.5000%
Total number of payments:
Frequency of payment	=	Once in 1 month period
Number of years of loan repayment	=	35
Total number of payments	N	35 × 12 =	420
Period payment using the formula	=	[ 100000000 × 0.005 × (1+0.005)^420] / [(1+0.005 ^420 -1]
Monthly payment	=	$                                                    570,189.71

Loan balance after 20 years:

Loan balance	=	PV * (1+r)^n - P[(1+r)^n-1]/r
Loan amount	PV =	100,000,000.00
Rate of interest	r=	0.5000%
nth payment	n=	240
Payment	P=	570,189.71
Loan balance	=	100000000*(1+0.005)^240 - 570189.71*[(1+0.005)^240-1]/0.005
Loan balance	=	67,569,483.56

Loan amount		$                                                             100,000,000.00
Less: current balance		$                                                                67,569,483.56
Principal paid		$                                                                32,430,516.44
Total payments:
Monthly payment × 240 =		$                                                             136,845,530.40
Less: principal paid		$                                                                32,430,516.44
Interest paid		$                                                             104,415,013.96

Total interest paid is $104,415,013.96

B
Monthly payment on new loan:

New loan
Rate	4.75%
Period	180
Loan amount	$ 67,569,483.56
Monthly payment	$      525,577.01
Particulars	Amount	× factor	Present value
Monthly payment 180 periods	$      525,577.01	$118.50	$ 62,282,722.89
Points paid today			$   1,351,389.67
Total present value			$ 63,634,112.56

Present value of loan payments (using old loan rate) is greater than amount received today.

Refinance is better.