Question

A $11,000 loan is to be repaid with 9 equal half-yearly instalments. Interest is at 7.7%p.a. compounding half-yearly Calculate the principal repaid in the fourth instalment. (use excel; answer to include cents but do not use the comma separator)

Answer 1

Formula A ) Exel Formula to Calculate EMI = PMT(Rate,Nper,PV,FV,Type).

in exel sheet, in we choose one cell and enter the above formula values as explained below, it will give desired answer:

means

Rate= rate of interest applicable on the loan per installment basis. here installment is half yearly and interest rate is 7.7% pa. So interest rate for half yearly is= 7.7%/2= 3.85%

Nper= total number of installements

PV= loan amount

FV= future value or cash balance at the end of last payment, which will be Zero. So here it will =0

Type= is 0 or 1- where intallemnt is due at the begning of the period type will be 1 or if installement is due at the end of period type will be 0. Here we are assuiing payment will be at the end of half year so Type =0

EMI = PMT(3.85%,9,11000,0,0)

= -1469.33

answer will be in negative because it is an outflow

Formula B: (P X R) X ((1+R)^N)/((1+R)^N-1)

Here:

P= Loan Amount

R= periodical rate of interest like in our case frequency of intallement is half yearly, it will be 3.85% (7.7%/2)

N= Number of installents

EMI = (11000*3.85%) X ((1+3.85%)^9/((1+3.85%)^9-1)

=1469.33

Table of Loan balance , payment of installment and interest during loan period

Instalment number	Principal at beginning	half year interest rate	Interest	Half yearly payment	Balance at the end of half year	Payment of Principal
A	B	C =7.7%/2	D= (B*C)	E (EMI)	F = B+D-E	G =E-D
1	11000.00	3.85%	423.50	1469.33	9,954.17	1045.83
2	9954.17	3.85%	383.24	1469.33	8,868.08	1086.09
3	8868.08	3.85%	341.42	1469.33	7,740.17	1127.91
4	7740.17	3.85%	298.00	1469.33	6,568.84	1171.33
5	6568.84	3.85%	252.90	1469.33	5,352.41	1216.43
6	5352.41	3.85%	206.07	1469.33	4,089.15	1263.26
7	4089.15	3.85%	157.43	1469.33	2,777.26	1311.90
8	2777.26	3.85%	106.92	1469.33	1,414.86	1362.40
9	1414.86	3.85%	54.47	1469.33	0.00	1414.86
EMI
Formula A	'PMT(Rate,NPER,PV,FV,type)
	=PMT(3.85%,9,11000,0,0)
	-1469.33
Formula B	(P X R) X ((1+R)^N)/((1+R)^N-1)
	=(11000*3.85%)*((1+3.85%)^9)/((1+3.85%)^9-1)
	1469.33

so as per above table in forth installment we have paid interest of $ 298 out of installment of $ 1469.33 So

Principal in forth installment= Installement minus Interest paid in installment

= 1469.33-298

   = $1171.33