Question

You started a company 5 years ago by taking a loan of $100,000. The APR on the loan is 12%. You agreed to make fixed payments every month for 10 years. Today, your 60th payment is due and you decide to make a double payment. (You paid twice what you were paying each month). You will continue to make single payments to pay the rest of the loan. By how many months your loan shortened? (According to the contract you signed with the bank, you can make double payments, your APR stays the same, your fixed monthly payments do not change, therefore, your loan term shortens)

Answer 1

The monthly payment is calculated using PMT function in Excel :

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

nper = 10 * 12 (total number of payments = loan term in years * 12)

pv = 100,000 (loan amount)

PMT is calculated to be $1,434.71

The cumulative principal repaid until 60th month (with a single payment in 60th month) is calculated using CUMPRINC function in Excel :

rate = 12% / 12

nper = 10 * 12

pv = 100,000

start period = 1

start period = 60

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

CUMPRINC is calculated to be $35,502.58

Balance remaining on loan (with single payment in 60th month) = $100,000 - $35,502.58 = $64,497.42

In the 60th month, extra payment of $1,434.71 is made. This goes towards reducing the principal balance.

Principal outstanding after extra payment = $64,497.42 - $1,434.71 = $63,062.71

The number of months required to pay off remaining loan is calculated using NPER function in Excel :

rate = 12% / 12

pmt = -1,434.71

pv = 63,062.71

NPER is calculated to be 58

The loan term is shortened by 2 months (60 - 58)