Question

Consider a 30-year mortgage with an interest rate of 10% compounded monthly and a monthly payment
of $850.
(1) Calculate the principal.
(2) How much of the principal is paid the first, 5th, 20th and last year?
(3) How much interest is paid the first, 5th, 20th and last year year?
(4) What is the total amount of money paid during the 30 years?
(5) What is the total amount of interest paid during the 30 years?
(6) What is the unpaid balance after 25 years?
(7) How much has to be deposited into a savings account with an interest rate of 4% compounded
quarterly in order to pay the unpaid balance of the mortgage after 25 years?
(8) How much has to be deposited each quarter year in a fund with an interest rate of 8% compounded
quarterly in order to cover the unpaid balance after 25 years?

Answer 1

1]

The principal borrowed is calculated using PV function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

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

PV is calculated to be $96,858.20

2]

Principal paid in the 1st year is calculated using CUMPRINC function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 1 (we are calculating principal paid in 1st year, that is, between 1st and 12th month)

end period = 12 (we are calculating principal paid in 1st year, that is, between 1st and 12th month)

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

CUMPRINC is calculated to be $538.41

Principal paid in the 5th year is calculated using CUMPRINC function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 49 (we are calculating principal paid in 5th year, that is, between 49th and 60th month)

end period = 60 (we are calculating principal paid in 5th year, that is, between 49th and 60th month)

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

CUMPRINC is calculated to be $801.89

Principal paid in the 20th year is calculated using CUMPRINC function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 229 (we are calculating principal paid in 20th year, that is, between 229th and 240th month)

end period = 240 (we are calculating principal paid in 20th year, that is, between 229th and 240th month)

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

CUMPRINC is calculated to be $3,571.55

Principal paid in the 30th year is calculated using CUMPRINC function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 349 (we are calculating principal paid in 30th year, that is, between 349th and 360th month)

end period = 360 (we are calculating principal paid in 30th year, that is, between 349th and 360th month)

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

CUMPRINC is calculated to be $9,668.33

3]

Interest paid in the 1st year is calculated using CUMIPMT function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 1 (we are calculating interest paid in 1st year, that is, between 1st and 12th month)

end period = 12 (we are calculating interest paid in 1st year, that is, between 1st and 12th month)

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

CUMIPMT is calculated to be $9,661.59

Interest paid in the 5th year is calculated using CUMIPMT function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 49 (we are calculating interest paid in 5th year, that is, between 49th and 60th month)

end period = 60 (we are calculating interest paid in 5th year, that is, between 49st and 60th month)

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

CUMIPMT is calculated to be $9,398.11

Interest paid in the 20th year is calculated using CUMIPMT function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 229 (we are calculating interest paid in 20th year, that is, between 229th and 240th month)

end period = 240 (we are calculating interest paid in 20th year, that is, between 229th and 240th month)

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

CUMIPMT is calculated to be $6,628.45

Interest paid in the 30th year is calculated using CUMIPMT function in Excel :

rate = 10%/12 (monthly rate = annual rate / 12)

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

pv = 96858.20 (principal borrowed)

start period = 349 (we are calculating interest paid in 30th year, that is, between 349th and 360th month)

end period = 360 (we are calculating interest paid in 30th year, that is, between 349th and 360th month)

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

CUMIPMT is calculated to be $531.67

4]

Total amount of money paid = monthly payment * total number of payments

Total amount of money paid = $850 * 30 * 12 = $306,000