Question

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
Year	Beginning Balance	Payment	Interest
1
2
3
b. Calculating % of Payment Representing Interest and Principal for Each Year
Year	Payment % Representing Interest	Payment % Representing Principal	Check: Total = 100%
1
2
3
Formulas

Year Beginning Balance Payment Interests Repayment of Principal Ending Balance

1

2

3

Answer 1

a. Setting up of amortization table:(amount in $)

Calculation of periodic payment A = P({i[1+i]^n} / {([1+i]^n)-1})

Where:

A is the periodic payment amount

P is the principal or the original loan balance, less any down-payments i.e 39000 in this case

i is the periodic interest rate. i.e 11%

n is the total number of periods i.e 3 year

so, now A = 39000({0.11[1+1.11]^3} / {([1+.11]^3)-1}) = 15960 approximately

Here payment of $15,960 consists of both interest and principal components.

interest = principal * rate of interest

Year	Balance (Start)	Payment	Principal	Interest	Balance (End)
1	$39,000	$ 15,960	$ 11,670	$ 4,290	$ 27,330
2	$ 27,330	$ 15,960	$ 12,954	$ 3006	$ 14,376
3	$ 14,376	$ 15,960	$ 14,378	$ 1,582	0

Calculating % of Payment Representing Interest and Principal for Each Year
Year	Payment % Representing Interest	Payment % Representing Principal	Check: Total = 100%
1	(4,290/15,960)*100= 26.88%	(11670/15,960)*100=73.12%	26.88+73.12=100
2	(3006/15960)*100=18.83%	(12954/15960)*100=81.17%	18.83+81.17=100
3