Question

20 years ago DEF 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, DEF is now looking to potentially refinance the loan.

A) The accounting department would like to know how much interest was paid on this 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?

Answer 1

A) WE use the function CUMIPMT to find the total interest paid on loans

We first find the total interest paid till 19 years and then total interest paid till 20th year

To find the interest paid last year, we substact total interest paid till 19th year from the  total interest paid till 20th year

The interest rate per period is 6%/4 = 1.5%

nper is the number of monthly periods

Total periods in the loan = 35*4 = 140

Ending payment number for 19th year = 19*4 = 76

Ending payment number for 20th year = 20*4 = 80

Principal	100,000,000
Interest rate	1.50%
Total periods in the loan	140
Starting payment number	1
Ending payment number for 19th year	76
Ending payment number for 20th year	80
Total Interest paid till 19th year	-100357440.6
Total Interest paid till 20th year	-104507218

Total interest paid last year = 104507218 - 100357441 = $4,149,777

B) We first find the monthly installment for the old loan using PMT function in excel

We now find the PV of the future payments

Rate	1.50%
Nper remaining	60
PMT	-1713073.86
PV after 20 years of future monthly payment	67461309.22

Hence, this is the value of the loan amount remaining after 20th year =$ 67,461,309

Floatation cost = 2%*67,461,309 = $1349226.18

Total value of loan remaining if refinanced = $67,461,309+$1349226.18 = $68810535.18

Now , we find the Monthly payment of the refinanced loan and check if this is lesser than the old loan

Rate	1.19%
Nper remaining	60
Loan amount	68810535.2
PMT	($1,610,033)

Here, we observe that after the loan is refinanced, DEF Inc needs to pay $1610033 monthly payment for the next 15 years, if refinance. If not refinance, it will have to pay $1713073.86.

Since, the payment reduced for the next 15 years after refinance, DEF Inc must refinance its loan