Some years ago, Penny purchased the car of her dreams for $25,000 by paying 20% down at pur­chase time and taking a $20,000, 5-year, 6% per year, compounded monthly loan with 60 monthly payments of $386.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 the 5 years.
(c) Total interest she will pay over the 5 years and the percentage this represents 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 immedi­ately after the late payment, determine the new monthly payment. Verify that this in­creased 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 princi­pal amount when payment #48 is due. There is no penalty for early repayment of principal.


Two spreadsheets follow the answers. You must write the functions.

(a) i/month = 0.06/12 = 0.005 (0.5%)
= -PMT(0.5%,60,20000) displays $386.66. Payment amount is correct

(b) Payment #60 is reduced to $386.38 to accommodate round-off error throughout.
Total paid = $23,199.32
(c) Total interest paid = $3199.32
Percentage of principal = 3199.32/20,000 * 100 = 16%

(d) Remaining principal after #36 is $8723.91. Increase i as of payment #37
New effective i = 0.10/12 = = 0.00833 (0.833%/month)

= -PMT(0.833%,624,8723.91) displays $402.56

(e) With interest due, payment #48 of 4,981.54 will pay off the entire loan balance
Parts (a) through (c)

Trades & Technology

You might also like to view...

Wheelbase is the distance between the center of the front wheel axle and the center of the rear axle.

Answer the following statement true (T) or false (F)

Trades & Technology

What was the outcome of the London Dock Strike in 1889?

a. Workers received reduced workweeks. b. Workers gained improved working conditions. c. Workers gained increased wages. d. Workers gained management representation.

Trades & Technology

Newer-style gun burners with special air-handling parts should be set in what range of CO2 percentage?

A) 11—13 percent B) 9—11 percent C) 0—3 percent D) 7—9 percent

Trades & Technology

What type of data connector is associated with the SAE J1587/1708 data bus?

A. Weatherproof B. Weatherpac C. 6-pin Deutsch D. 9-pin Deutsch

Trades & Technology