Question

A machine is purchased for $2 million dollars with a monthly loan payment lasting 3 years. With a nominal interest rate at 4%, what is the balance due after 22 months?

Answer 1

Nominal interest rate of 4% per annum will translate to 4%/12 = 0.33% as monthly rate of interest. We can set up a table in Excel to solve it once we know the monthly loan payment. That can be found using the formula for PV of annuity

A = P*((1-(1+r)^-n)/r)

Where

A = $2000000

P = to be found

r = 0.33% per month

n = 36, i.e., 12 months * 3 years

P = $2000000/((1-(1.0033^-36))/0.0033) = $59012.39

Now we can set it up in Excel to find out the balance due after 22 months. It will be 806080.525

Time	Machine cost	Interest = 0.33% * balance due previous month	Monthly payment	Balance due = balance due previous month + interest - monthly payment
0	2000000			2000000
1		6600.000	59012.390	1947587.610
2		6427.039	59012.390	1895002.259
3		6253.507	59012.390	1842243.377
4		6079.403	59012.390	1789310.390
5		5904.724	59012.390	1736202.724
6		5729.469	59012.390	1682919.803
7		5553.635	59012.390	1629461.048
8		5377.221	59012.390	1575825.880
9		5200.225	59012.390	1522013.715
10		5022.645	59012.390	1468023.970
11		4844.479	59012.390	1413856.060
12		4665.725	59012.390	1359509.395
13		4486.381	59012.390	1304983.386
14		4306.445	59012.390	1250277.441
15		4125.916	59012.390	1195390.966
16		3944.790	59012.390	1140323.366
17		3763.067	59012.390	1085074.044
18		3580.744	59012.390	1029642.398
19		3397.820	59012.390	974027.828
20		3214.292	59012.390	918229.730
21		3030.158	59012.390	862247.498
22		2845.417	59012.390	806080.525
23		2660.066	59012.390	749728.200
24		2474.103	59012.390	693189.913
25		2287.527	59012.390	636465.050
26		2100.335	59012.390	579552.995
27		1912.525	59012.390	522453.130
28		1724.095	59012.390	465164.835
29		1535.044	59012.390	407687.489
30		1345.369	59012.390	350020.468
31		1155.068	59012.390	292163.145
32		964.138	59012.390	234114.893
33		772.579	59012.390	175875.083
34		580.388	59012.390	117443.080
35		387.562	59012.390	58818.253
36		194.100	59012.390	-0.037