Question

831218EN97033791100 CENGAGE MINDTAP 1 50131 Chapter 5 Assignment Attempts Keep the Highest 10 IMITED 10. Loan amortization and capital recovery Catalog Offers After Shpra got a job, the first thing she bought was a new car She took out mortured loan for $25.000 with no (50) down payment. She agreed to pay of the loan by making annual payments for the next four years at the end of each year. Her bank's charging her an interest rate of per year. Yesterday, she called to ask that you help her compute the annual payments necessary to repay her loan ptions Calculate the annual payment and complete the following loan amortization table: Success Center Year Beginning Amount Payment Interest Paid Principal Paid Ending Balance D FOR YOU $25,000.00 y Tools $0.02 Grade New Save & Continue or Introductory lance Connewithout pe here to search

Answer 1

Yearly loan payment is calculated using PMT function in Excel :

rate = 6% (annual interest rate)

nper = 4 (4 year loan with 1 payment each year)

pv = 25000 (loan amount)

PMT is calculated to be $7,214.79

x A1 - A 1 ($7,214.79) fc C =PMT(6%,4,25000) D E B

Interest in any year = principal outstanding at beginning of year * 6%

Principal portion of yearly payment = yearly payment minus interest portion of payment

principal outstanding at end of year = principal outstanding at beginning of year minus principal portion of yearly payment

A B DE F G H 2 Principal outstanding at beginning Payment 1 $ 25,000.00 $7,214.79 2 $ 19,285.21 $7,214.79 3 $ 13,227.54 $7,214.79 4 $ 6,806.40 $7,214.79 Principal outstanding at end $ 19,285.21 $ 13,227.54 $ 6,806.40 $ $7,214.79 3 Interest Principal $1,500.00 $5,714.79 $1,157.11 $6,057.67 $ 793.65 $ 6,421.14 $ 408.38 $ 6,806.40 2 4 5

| A B C D E F G H Principal outstanding at 1 Year beginning 2 1 25000 32 =F2 4 3 =F3 5 4 =F4 Principal outstanding Payment Interest Principal at end =SH$2 =B2*6% =C2-D2 =B2-E2 =$H$2 =B3*6% =C3-D3 =B3-E3 =$H$2 =B4*6% =C4-D4 =B4-E4 =SH$2 =B5*6% =C5-D5 =B5-E5 =PMT(6%,4,-25000)