Question

Five years ago you took out a 10-year amortizing loan to purchase an apartment. The loan has 4.0% APR with monthly payments of $1,800. How much do you owe on the loan today?

The remaining loan balance is $________. (round to the nearest dollar)

How much interest did you pay on the loan in the past year?

The interest paid in year five was $______. (round to the nearest dollar)

Over the entire period of 10 years, how much interest will you have paid on the loan?

The total amount of interest paid on the loan will be $_______. (round to the nearest dollar)

Answer 1

1]

First, we calculate the original loan amount using PV function in Excel :

rate = 4%/12 (converting annual rate into monthly rate)

nper = 10*12 (10 year loan with 12 monthly payments each year)

pmt = -1800 (Monthly payment. This is entered with a negative sign because it is a payment)

PV is calculated to be $177,786.31

Now, we calculate the principal paid off after 5 years (60 months) using CUMPRINC function in Excel :

rate = 4%/12 (converting annual rate into monthly rate)

nper = 10*12 (10 year loan with 12 monthly payments each year)

pv = 177786.31 (original loan amount)

start period = 1 (We are calculating principal paid off between 1st and 60th month)

end period = 60 (We are calculating principal paid off between 1st and 60th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $62,734.93

The balance loan principal outstanding after 5 years =  $177,786.31 - $62,734.93 = $115,051

b]

The interest paid in year 5 is calculated using CUMIPMT function in Excel :

rate = 4%/12 (converting annual rate into monthly rate)

nper = 10*12 (10 year loan with 12 monthly payments each year)

pv = 177786.31 (original loan amount)

start period = 49 (We are calculating principal paid off between 49th and 60th month)

end period = 60 (We are calculating principal paid off between 49th and 60th month)

type = 0 (each payment is made at the end of month)

CUMIPMT is calculated to be $4,287

c]

Total amount of interest paid = (monthly payment * total number of payments) - loan amount

Total amount of interest paid = ($1,800 * 10 * 12) - $177,786.31

Total amount of interest paid = $38,214