Question

Sue takes out a 3-year loan that she repays using the amortization method. She makes monthly payments at a nominal annual interest rate of 4.8% compounded monthly. The first payment is $50 and is to be paid one month from the date of the loan. Each succeeding monthly payment will be 2% larger than the prior payment. Sue's outstanding loan balance after the 25 th payment is

Answer 1

Annual interest rate = 4.8%

Monthly interest rate = 4.8%/12 = 0.4%

We chalk out the payments incremented at 2% every month in excel

We first compute the Loan amount using NPV function in excel

Loan amount = NPV(0.4%, All-monthly payments)

The loan outstanding at t=0 is $2396.32

Time in months	Payments	Loan outstanding
0		2396.3
1	50.0	2355.9
2	51.0	2314.3
3	52.0	2271.6
4	53.1	2227.6
5	54.1	2182.4
6	55.2	2135.9
7	56.3	2088.1
8	57.4	2039.1
9	58.6	1988.6
10	59.8	1936.8
11	60.9	1883.6
12	62.2	1829.0
13	63.4	1772.9
14	64.7	1715.3
15	66.0	1656.2
16	67.3	1595.5
17	68.6	1533.3
18	70.0	1469.4
19	71.4	1403.9
20	72.8	1336.6
21	74.3	1267.7
22	75.8	1197.0
23	77.3	1124.5
24	78.8	1050.1
25	80.4	973.9
26	82.0	895.8
27	83.7	815.7
28	85.3	733.6
29	87.1	649.5
30	88.8	563.3
31	90.6	475.0
32	92.4	384.5
33	94.2	291.8
34	96.1	196.8
35	98.0	99.6
36	100.0	0.0

Hence, Sue's outstanding loan balance after 25th payment = $973.9