Question

Some years ago, Penny purchased the car of her dreams for $25,000 by paying 20% down at purchase time and taking a $20,000, 5 years, 6% per year, compounded monthly loan with 60 monthly payments of $3866.66 each. She is examining her loan situation and would like to have some specific information. Help her obtain the following:

(a) Verification of the current monthly payment amount.

(b) Total amount she will pay over 5 years.

(c) Total interest she will pay over the 5 years and the percentage this represent of the original loan amount of $20,000.

(d) After she missed payment #36 at the very end of the third year, according to the loan agreement, the interest rate increased from 6% to 10% per year, compounded monthly. Based on the remaining principal immediately after the late payment, determine the new monthly payment. Verify that this increased amount is necessary to pay off the loan at the increased rate.

(e) Penny is now in her fourth year, has paid the increased payment for 12 payments, and wants to get rid of this loan completely. She wishes to know the remaining principal amount when payment #48 is due. There is no penalty for early repayment of principal.

Please put information in excel to get answers

Answer 1

	(a)	Verification of current monthly payment
	Rate	Monthly interest rate =(6/12)%	0.50%
	Nper	Number of months of payment=5*12	60
	Pv	Loan amount	$20,000
	PMT	Monthly payment required	$386.66	(Using PMT function of excel with Rate=0.5%, Nper=60, Pv=-20000)
	(b)	Total amount she will pay over 5 years	$23,319.60	(388.66*60)
					23319.60
	.(c)	Total interest payment =23319.60-20000	$3,319.60
		Percentage of Interest of original loan	16.60%	(3319.60/20000)
	(d)	Loan Balance after payment of 36th payment =Present Value of future payments
		Number of Future Payments =60-36	24
		Loan Balance after payment of 36th payment	$8,724.07	(Using PV function of excel with Rate=0.5%, Nper=24, Pmt=-386.66)
	Pv	Loan Balance immediately after late payment	$8,724.07
	Rate	Monthly interest rate =(10/12)%	0.83333%
	Nper	Number of Future Payments	24
	PMT	New Monthly payment required	$402.57	(Using PMT function of excel with Rate=0.83333%, Nper=24, Pv=-8724.07)
	.(e)	REMAINING PRINCIPAL AMOUNT
	Nper	Number of future payment after 48th payment	12	(60-48)
		Remaining Principal after payment of 48th payment	$4,579.05	(Using PV function of excel with Rate=0.8333%, Nper=12, Pmt=-402.57)
		Remaining Principal JUST BEFORE payment of 48th payment	$4,981.62	(4579.05+402.57)
		remaining principal amount when payment #48 is due	$4,981.62
											O G7 - X Fax =PMT(G4, G5,-G6) G H I J K L M N 2 3 Verification of current monthly payment 4 Monthly interest rate =(6/12)% 0.50% 5 Number of months of payment=5*12 60 6 Loan amount $20,000 7 Monthly payment required $386.66! (Using PMT function of excel with Rate=0.5%, Nper=60, Pv=-20000) 9 Luuuuu x fc =PV(0.5%,G16,-G7) G17 A B C D E G H I J K L M N 0.50% (a) Rate Nper Pv PMT Verification of current monthly payment Monthly interest rate =(6/12)% Number of months of payment=5*12 Loan amount Monthly payment required $20,000 $386.66 (Using PMT function of excel with Rate=0.5%, Nper=60, Pv=-20000) (b) Total amount she will pay over 5 years $23,319.60 (388.66*60) 23319.60 Total interest payment =23319.60-20000 Percentage of Interest of original loan $3,319.60 16.60% (3319.60/20000) (d) Loan Balance after payment of 36th payment Present Value of future payments Number of Future Payments =60-36 Loan Balance after payment of 36th payment $8,724.07! (Using PV function of excel with Rate=0.5%, Nper=24, Pmt=-386.66) стролита G21 x fo =PMT(G19,620,-G18) C D E H I J K L M N G $20,000 $386.66 Loan amount Monthly payment required PMT (Using PMT function of excel with Rate=0.5%, Nper=60, PV=-20000) (b) vor 5 years Total amount she will pay over 5 years $23,319.60 (388.66*60) 23319.60 (c) Total interest payment =23319.60-20000 Percentage of Interest of original loan $3,319.60 16.60% (3319.60/20000) (d) Loan Balance after payment of 36th payment =Present Value of future payments Number of Future Payments =60-36 Loan Balance after payment of 36th payment $8,724.07 (Using PV function of excel with Rate=0.5%, Nper=24, Pmt=-386.66) Pv Rate $8,724.07 0.83333% Loan Balance immediately after late payment Monthly interest rate =(10/12)% Number of Future Payments New Monthly payment required 24 PMT $402.57 |(Using PMT function of excel with Rate=0.83333%, Nper=24, Pv=-8724.07) G25 fc =PV(G19,624,-G21) Дc D E J K L. M G 16.60% H I (3319.60/20000) Percentage of Interest of original loan (d) Loan Balance after payment of 36th payment =Present Value of future payments Number of Future Payments =60-36 24 Loan Balance after payment of 36th payment $8,724.07 (Using PV function of excel with Rate=0.5%, Nper=24, Pmt=-386.66) $8,724.07 0.83333% Pv Rate Nper PMT Loan Balance immediately after late payment Monthly interest rate =(10/12)% Number of Future Payments New Monthly payment required $402.57 (Using PMT function of excel with Rate=0.83333%, Nper=24, Pv=-8724.07) .(e) Nper 12 (60-48) REMAINING PRINCIPAL AMOUNT Number of future payment after 48th payment Remaining Principal after payment of 48th payment $4,579.05 | (Using PV function of excel with Rate=0.8333%, Nper=12, Pmt=-402.57)