Question

The purpose of this question is to give you experience creating an amortization schedule for a loan. As noted by Investopedia: ‘An amortization schedule is a complete table of periodic loan payments, showing the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term. While each periodic payment is the same amount early in the schedule, the majority of each payment is interest; later in the schedule, the majority of each payment covers the loan's principal. The last line of the schedule shows the borrower’s total interest and principal payments for the entire loan term.’

Suppose you intend to buy a new forklift truck for your business. The purchase will be

financed with a three-year loan of $50,000. The loan has an APR of 8%, with a periodic interest

rate of 2% per quarter. The loan will be repaid in 12 EQUAL quarterly payments. Complete

the amortization schedule below for this loan (remember to include workings to receive

partial credit):

Payment Quarter Interest Charged Interest Principal Opening Balance 50,000 Total Closing Balance

Answer 1

I have answered the question below using excel and have attached the image below.

Please upvote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

The Quarterly payment paid will be $ 4727.98 as shown below along with the amortization schedule:

Clipboard D А 1 Input data 2 Interest rate 3 Number of payments 4 Loan amount 5 Future value 6 Timing of payments 7 Monthly payment Algnment =PMT(D2,D3,D4,D5,06) в Function Formula arguments Value Given Rate 2.00% 3 years*4 Nper Pv $ (50,000.00) Fv $0.00 (End of month) Type PMT $4,727.98

Shedule of amortization Quarter Opening Interest Quarterly Payment Closing balance Charged Interest Principal Total balance =Col(2)*(3) =Col (6)-Col(4) =Col (2)-(5) (1) (2) (3) (4) (4) (5) (6) (7) 1 50000 0.02 1000.00 3727.98 4727.98 46272.02 2 46272.02 0.02 925.44 3802.54 4727.98 42469.48 3 42469.48 0.02 849.39 3878.59 4727.98 38590.89 4 38590.89 0.02 771.82 3956.16 4727.98 34634.73 5 34634.73 0.02 692.69 4035.29 4727.98 30599.44 6 30599.44 0.02 611.99 4115.99 4727.98 26483.45 7 26483.45 0.02 529.67 4198.31 4727.98 22285.14 8 22285.14 0.02 445.70 4282.28 4727.98 18002.86 9 18002.86 0.02 360.06 4367.92 4727.98 13634.94 10 13634.94 0.02 272.70 4455.28 4727.98 9179.66 11 9179.66 0.02 183.59 4544.39 4727.98 4635.27 12 4635.27 0.02 92.71 4635.27 4727.98 0.00