The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.
| Amortization schedule | |||||
| Loan amount to be repaid (PV) | $39,000.00 | ||||
| Interest rate (r) | 11.00% | ||||
| Length of loan (in years) | 3 | ||||
| a. Setting up amortization table | Formula | ||||
| Calculation of loan payment | #N/A | ||||
a. Complete an amortization schedule for a $39,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 11% compounded annually. Round all answers to the nearest cent.
Beginning Repayment Ending YearBalance PaymentInterestof PrincipalBalance
1 $ $ $ $ $ $
2 $ $ $ $ $ $
3 $ $ $ $ $ $
% Interest % Principal
year 1 % %
Year2 % %
Year 3:% %
Given,
Loan amount = $39,000
Interest @11% compounded annually
Term of loan = 3 years
Annual Payment = Loan Amount÷PVAF(11%,3)
=$39,000÷2.4437
=$16,000(rounded off)
(a) AMORTIZATION SCHEDULE OF THE LOAN :
| Year |
Balance at the beginning |
Repayment |
Principal |
Interest |
Closing Balance |
|---|---|---|---|---|---|
| 1 | $39,000 | $16,000 | $11,710 | $4290 | $27,290 |
| 2 | $27,290 | $16,000 | $13,000 | $3,000 | $14,290 |
| 3 | $14,290 | $16,000 | $14,290 | $1710 | - |
Workings:
Interest: [Balance at beginning ×interest rate]
Year 1. $39,000×11% = $4,290
Year 2 $27,290×11%. = $3,000
Year 3. $14,290×11% = $1710
Principal: [ Annual Payment - Interest ]
Year 1. $16,000 - $4,290 = $11,710
Year 2. $16,000 - $3,000 = $13,000
Year 3. $16,000 - $1,710 = $14,290
Closing Balance: [ Balance at the beginning - Principal]
Year 1. $39,000 - $11,710 = $27,290
Year 2. $27,290 - $13,000 = $14,290
Year 3. $14,290- $14,290. = $0
Get Answers For Free
Most questions answered within 1 hours.