Question

it is very important that you show your work from the calculations (right down what you used as PV, i FV, n, cash flows,.....etc. in your calculation)

NCP bank extend a $400,000, 20-year mortgage at 5%. The interest rate increases to 6% soon after origination. Suppose the loan is expected to be prepaid in 9 years. What is the loss (interest rate risk) to the bank from the mortgage?

Answer 1

Loan amount = 400000 USD

Time Period = 20 years

Initial Annual Interest Rate = 5 %

New Annual Interest Rate = 6 %

Assumption:

1. As the loan is expected to be prepaid in 9 years, it is assumed that the remaining principal at the end of 9 years is paid off in one go.

2. Monthly payouts in interests

Part 1

P = PV = 400000

time = N = 20 years

R = Interest = 5 %

Formula =

EMI = [P x R x (1+R)^N]/[(1+R)^N-1]

=(400000*(0.05/12)*(1 + (0.05/12))^(20*12))/((1+(0.05/12))^(20*12)-1)

= 2639.82 USD

Total Payout to be done after 20 years if interest rate was 5 % = 2639.82*12*20 = 633,557.51

Interest to be paid = 633557.51 - 400000 = 233,558 USD

Part 2 P = PV = 400000 time = N = 20 years R = Interest = 6 % Formula = EMI = [P x R x (1+R)^N]/[(1+R)^N-1] =(400000*(0.06/12)*(1 + (0.06/12))^(20*12))/((1+(0.06/12))^(20*12)-1) = 2865.72 USD Total Payout to be done after 20 years if interest rate was 6 % = 2665.72*12*20 = 687,773.82 Interest to be paid = 687,773.82 - 400000 = 287,773.82 USD Part 3 Suppose the loan is expected to be prepaid in 9 years, total interest loss to the bank if interest rate is 6% Total Interest paid till the end of 9 years Total amount in EMI paid = 108 * 2865.72 = 309,498.22 USD Lets plot the payment charge to identify the interest paid till the end of 9 years EMI No. EMI Amount Interest/ EMI Principal/ EMI Principal Outstanding Cummalative Int. TOTALS         687,773.82           287,773.82       400,000.00 1             2,865.72               2,000.00              865.72                   399,134.28               2,000.00 2             2,865.72               1,995.67              870.05                   398,264.22               3,995.67 3             2,865.72               1,991.32              874.40                   397,389.82               5,986.99 4             2,865.72               1,986.95              878.78                   396,511.04               7,973.94 5             2,865.72               1,982.56              883.17                   395,627.88               9,956.50 6             2,865.72               1,978.14              887.58                   394,740.29             11,934.64 7             2,865.72               1,973.70              892.02                   393,848.27             13,908.34 8             2,865.72               1,969.24              896.48                   392,951.79             15,877.58 9             2,865.72               1,964.76              900.97                   392,050.82             17,842.34 10             2,865.72               1,960.25              905.47                   391,145.35             19,802.59 11             2,865.72               1,955.73              910.00                   390,235.35             21,758.32 12             2,865.72               1,951.18              914.55                   389,320.80             23,709.50 13             2,865.72               1,946.60              919.12                   388,401.68             25,656.10 14             2,865.72               1,942.01              923.72                   387,477.97             27,598.11 15             2,865.72               1,937.39              928.33                   386,549.63             29,535.50 16             2,865.72               1,932.75              932.98                   385,616.66             31,468.25 17             2,865.72               1,928.08              937.64                   384,679.02             33,396.33 18             2,865.72               1,923.40              942.33                   383,736.69             35,319.72 19             2,865.72               1,918.68              947.04                   382,789.65             37,238.41 20             2,865.72               1,913.95              951.78                   381,837.87             39,152.36 21             2,865.72               1,909.19              956.53                   380,881.34             41,061.55 99             2,865.72               1,454.31           1,411.41                   289,451.15           173,157.85 100             2,865.72               1,447.26           1,418.47                   288,032.68           174,605.11 101             2,865.72               1,440.16           1,425.56                   286,607.12           176,045.27 102             2,865.72               1,433.04           1,432.69                   285,174.43           177,478.31 103             2,865.72               1,425.87           1,439.85                   283,734.58           178,904.18 104             2,865.72               1,418.67           1,447.05                   282,287.53           180,322.85 105             2,865.72               1,411.44           1,454.29                   280,833.24           181,734.29 106             2,865.72               1,404.17           1,461.56                   279,371.69           183,138.45 107             2,865.72               1,396.86           1,468.87                   277,902.82           184,535.31 108             2,865.72               1,389.51           1,476.21                   276,426.61           185,924.83 So Interest paid = 185,924.83 Hence Interest Loss to the bank =287,773.82 - 185,924.83 = 101,848.99