Question

PLEASE SOLVE IN EXCEL WITH CALCULATIONS AND WORK.
A mortgage for $100,000 is made with initial payments of $500 per month for the first year. The interest rate is 9 percent. After the first year, payments will increase to an amount that makes the loan fully amortizable over the remaining 24 years with constant monthly payments.
a. Calculate the interest deductions for the loan for the first year.
b. How much, if any, interest must be deferred until the second year?
c. How much interest will be deducted in the second year?

Answer 1

We will solve this using the excel solver tool. First, we put the information in required excel cells, and set payments beyond first year as 0 (Assumed, this value will adjust later).

The values will look like this.

There will be total 25*12 = 300 payments (monthly)

Year 1 Calculations:

Interest for each month = Loan Amount remaining * Interest Rate(Annual) /12

End balance of loan = Beginning Balance + Interest - Repayment

The calculations are shown in the excel as below (Enlarge to see the entire image)-

The formulae are as given below-

Thus, Answer A - Interest to be Paid in Year 1 = $ 9,127, Actual Payout = $ 500 * 12 = $ 6000

Answer B - Deferred Interest = $ 3127

-------------------------

Step 1: Apply the assumed payment of 0 for all the 300 payment cycles (300 months) and drag the formula down.For Year 2 and Beyond-

Without any payment, the loan will rise to an end value of $ 887,049 as shown in the figures below. The supporting formulae are also given. Now, we have to set the monthly repayment value such that the end value becomes 0.

Formulae used-

Solving using Solver with the following constraints-

Objective - Last value for Balance after 300 payments - Should be 0

Value of 0 (sets the ending balance to 0)

By changing payouts per month (this will adjust the monthly payouts post year 1 so that last value becomes 0)

From this, we get monthly payouts = $ 875 as shown below. Further,

Answer C - Interst deducted in second year = $ 9,230

See that the final payable amount after 25 years becomes 0.

This validates that the loan will be completely repaid after 25 years.