Question

Lowlife Company defaulted on a $190,000 loan that was due on December 31, 2018. The bank has agreed to allow Lowlife to repay the $190,000 by making a series of equal annual payments beginning on December 31, 2019. (FV of $1, PV of $1, FVA of $1, PVA of $1, FVAD of $1 and PVAD of $1) (Use appropriate factor(s) from the tables provided.)

Required:

1. Calculate the required annual payment if the bank’s interest rate is 10% and four payments are to be made.
2. Calculate the required annual payment if the bank’s interest rate is 8% and five payments are to be made.
3. If the bank’s interest rate is 10%, how many annual payments of $27,885 would be required to repay the debt?
4. If three payments of $69,117 are to be made, what interest rate is the bank charging Lowlife?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3		1)
4
5		Calculation of Annual Payment:
6
7		Annual payment can be calculated using PMT(RATE,NPER,PV,FV,TYPE) function in Excel as follows:
8
9		Given the following data:
10		Amount to be Repaid	190000
11		Duration	4	Years
12		Interest rate	0.1
13
14		Annual Payment can be calculated as below:
15		RATE	=D12
16		NPER (No of Months):	=D11
17		PV (Loan Amount):	=-D10
18		FV	0
19		TYPE	0
20		Annual Payment	=PMT(D15,D16,D17,D18,D19)	=PMT(D15,D16,D17,D18,D19)
21
22		Hence Annual Payment is	=D20
23
24		2)
25
26		Calculation of Annual Payment:
27
28		Annual payment can be calculated using PMT(RATE,NPER,PV,FV,TYPE) function in Excel as follows:
29
30		Given the following data:
31		Amount to be Repaid	190000
32		Duration	5	Years
33		Interest rate	0.08
34
35		Annual Payment can be calculated as below:
36		RATE	=D33
37		NPER (No of Months):	=D32
38		PV (Loan Amount):	=-D31
39		FV	0
40		TYPE	0
41		Annual Payment	=PMT(D36,D37,D38,D39,D40)	=PMT(D15,D16,D17,D18,D19)
42
43		Hence Annual Payment is	=D41
44
45		3)
46
47		Number of payments should be such that the present value cash flows be equal to amount to be repaid.
48
49		Amount to be Repaid	190000
50		Annual Payment	27885
51		Interest rate	0.1
52
53		Assuming the number of payments is n, then
54		Present Value of All the payments	=$27885*(P/A,10%,n)
55
56		n should be such that
57		$27885*(P/A,10%,n) = $190000
58		or
59		(P/A,10%,n) =	=D49/D50
60
61		(P/A,10%,n) for different n value can be calculated as follows:
62		n	(P/A,10%,n)
63		1	=PV($D$51,C63,-1,0)
64		2	=PV($D$51,C64,-1,0)
65		3	=PV($D$51,C65,-1,0)
66		4	=PV($D$51,C66,-1,0)
67		5	=PV($D$51,C67,-1,0)
68		6	=PV($D$51,C68,-1,0)
69		7	=PV($D$51,C69,-1,0)
70		8	=PV($D$51,C70,-1,0)
71		9	=PV($D$51,C71,-1,0)
72		10	=PV($D$51,C72,-1,0)
73		11	=PV($D$51,C73,-1,0)
74		12	=PV($D$51,C74,-1,0)
75		13	=PV($D$51,C75,-1,0)
76		14	=PV($D$51,C76,-1,0)
77
78		Thus for n=12, (P/A,10%,n) = 6.81,
79		therefore
80		Number of payment required are	12
81
82		4)
83
84		Amount due	190000
85		Annual Payment	69117
86		Number of payments	3
87		Interest should be such that the present value of total payments be equal to the present value.
88		Rate function can be used to calculate the interest rate.
89
90		Rate(nper,pmt,PV, [fv],type) function of excel can be used to find the interest rate as follows:
91		NPER	=D86
92		PMT	=-D85
93		PV	=D84
94		FV	=D83
95
96		Interest rate	=RATE(D91,D92,D93,D94)	=RATE(D91,D92,D93,D94)
97
98		Hence annual interest rate is	=D96
99