Question

You borrow money on a self liquidating installemnt loan (equal payments at the end of each year, each payment is part principal part interest) Loan $318,000 Interest Rate 13.20% Life (years) 58 Date of Loan January 1, 2020 Annual Payment: 6206.484138 Use the installment method - not straight line Do NOT round any interrmediate numbers. Do NOT turn this into a monthly problem. a) What is the annual payment? b) What are the total interest payments? c) After 25 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? d) After 25 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)? Redo the problem if the interest rate is 2.00% (for a well designed spreadsheet this should take 30 seconds) e) What is the annual payment? f) What are the total interest payments? g) After 25 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? h) After 25 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)?

Answer 1

We will use the PMT function in excel to find the value of annual payments.

1. So annual installment = PMT(13.20%,58,318000,0,0) = - $42007.64 (This will appear as a negative sign in excel)

This is the annual payment required at the end of year

This installment includes both Interest and Principal. In initial years, a large percentage of installment is interest. In later years only, quick principal reduction happens

During the course of loan, i would have paid = 58 x 42007.64 = $2436443.12

Out of which only 318000 is the Principal.

Total Interest paid = Total amount paid - Principal

= 2,436,443.12 - 318,000

= 2,118,443.12  

3. We will first find the loan outstanding after 25 installments have been paid, using FV function

Outstanding loan after 25 Installments= FV(13.20%,25,-42007.64,318000,0)

= $312,920.79

Amount paid after 25 installments = 25 X 42007.64 =  $1,050,191.00

Principal paid after 25 installments = Loan amount - Outstanding loan after 25 installments

= $318,000 - $312,920.70

= $5,079.21

Interest paid after 25 installments = Total amount paid - Principal paid

= 1,050,191.00 - 5079.21

= $1,045,111.79

Interest paid as a % of total interest = 1045,111.79 / 2,118,443.12 = 49.33%

Principal paid as % of loan = 5079.21/ 318000 = 1.60%

Head	Amount
PV	$318,000.00
Rate	13.20%
NPER	58.00
FV	0
Type	0
Equal Annual Installment	($42,007.64)
Total Amount paid	($2,436,443.12)
Total Interest Paid	($2,118,443.12)
Installments paid	25
After 25 Installments
Principal Left	($312,920.79)
Principal Paid	$5,079.21
Total Amount Paid	($1,050,191.00)
Total Interest Paid	($1,045,111.79)
Interest % of Total Interest	49.33%
Principal% of Total	1.60%

II. If the interest rate were to be reduced to 2%, values can be found in the table given below with the same of calculating as given above.

Head	Amount
PV	$318,000.00
Rate	2.00%
NPER	58.00
FV	0
Type	0
Equal Annual Installment	($9,313.16)
Total Amount paid	($540,163.35)
Total Interest Paid	($222,163.35)
Installments paid	25
After 25 Installments
Principal Left	($223,409.36)
Principal Paid	$94,590.64
Total Amount Paid	($232,829.03)
Total Interest Paid	($138,238.39)
Interest % of Total Interest	62.22%
Principal% of Total	29.75%

One can construct a table like below using Excel (just copy paste the table in excel in a new sheet)

Just change the values in blue.

Head	Values
PV	318000
Rate	0.132
NPER	58
FV	0
Type	0
Equal Annual Installment	=PMT(B3,B4,B2,B5,B6)
Total Amount Payment	=B8*B4
Total Interest Payment	=B9+B2
Installments paid	25
=CONCATENATE("After ",B12," Installments")
Principal Left	=FV(B3,B12,B8,B2,B6)
Principal Paid	=B2+B15
Total Amount Paid	=B8*B12
Total Interest Paid	=B17+B16
Interest as % of Total Interest	=B18/B10
Principal as % of Total Loan	=B16/B2