In: Economics
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?
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 |