Loan Interest Payment Puzzle / Question

[Pat Mustard]

I'll reply to meself as I can see where I went wrong last night:

Ok I did a quick excel working of the problem.

First paying the small loan first:
Small

Code:

Principle Interest Lump   payment balance
2000      1.05     2100   2000         100
100        1.05      105   2000       -1895

Big

Code:

Principle Interest Lump     payment balance
20,000.00 1.03    20,600.00 0.00       20,600.00
20,600.00 1.03    21,218.00 1,895.00   19,323.00
19,323.00 1.03    19,902.69 2000       17,902.69
17,902.69 1.03    18,439.77 2000       16,439.77
16,439.77 1.03    16,932.96 2000       14,932.96
14,932.96 1.03    15,380.95 2000       13,380.95
13,380.95 1.03    13,782.38 2000       11,782.38
11,782.38 1.03    12,135.85 2000       10,135.85
10,135.85 1.03    10,439.93 2000        8,439.93
8,439.93 1.03     8,693.13 2000        6,693.13
6,693.13 1.03     6,893.92 2000        4,893.92
4,893.92 1.03     5,040.74 2000        3,040.74
3,040.74 1.03     3,131.96 2000        1,131.96
1,131.96 1.03    1,165.92 2000         -834.08

Ok so Small before big gives you 16 monthly payments of $2,000 plus a final one of $1,165.92 totalling $33,165.92 total payment.

Let's do it the other way shall we.
Big

Code:

Principle Interest Lump        payment balance
20,000.00 1.03     20,600.00 2,000.00 18,600.00
18,600.00 1.03     19,158.00 2,000.00 17,158.00
17,158.00 1.03     17,672.74 2,000.00 15,672.74
15,672.74 1.03     16,142.92 2,000.00 14,142.92
14,142.92 1.03     14,567.21 2,000.00 12,567.21
12,567.21 1.03     12,944.23 2,000.00 10,944.23
10,944.23 1.03     11,272.55 2,000.00 9,272.55
9,272.55 1.03      9,550.73 2,000.00 7,550.73
7,550.73 1.03      7,777.25 2,000.00 5,777.25
5,777.25 1.03      5,950.57 2,000.00 3,950.57
3,950.57 1.03      4,069.09 2,000.00 2,069.09
2,069.09 1.03      2,131.16 2,000.00 131.16
131.16 1.03         135.09 2,000.00 -1,864.91

Small

Code:

Principle Interest Lump payment balance
2,000.00 1.05 2,100.00 0.00 2,100.00
2,100.00 1.05 2,205.00 0.00 2,205.00
2,205.00 1.05 2,315.25 0.00 2,315.25
2,315.25 1.05 2,431.01 0.00 2,431.01
2,431.01 1.05 2,552.56 0.00 2,552.56
2,552.56 1.05 2,680.19 0.00 2,680.19
2,680.19 1.05 2,814.20 0.00 2,814.20
2,814.20 1.05 2,954.91 0.00 2,954.91
2,954.91 1.05 3,102.66 0.00 3,102.66
3,102.66 1.05 3,257.79 0.00 3,257.79
3,257.79 1.05 3,420.68 0.00 3,420.68
3,420.68 1.05 3,591.71 0.00 3,591.71
3,591.71 1.05 3,771.30 0.00 3,771.30
[b]1)[/b] 3,771.30 1.05 3,959.86 2,000.00 1,959.86
1,959.86 1.05 2,057.86 2,000.00 57.86
57.86 1.05 60.75 2,000.00 -1,939.25

Second one comes out at 16 payments of $2,000 and a final payment of $60.75 giving $32,060.75.

My workings say that you will be better off by $1,000 or there abouts if you pay off the bigger loan first of all before paying the smaller one. Despite the fact the smaller one accures more interest the differential between the interests isn't enough to ovetake the principle differentials in the timeframe of full payment.

I'll run a half and half scenario in a minute, just want to post this first.

Problem 1) Every line after the inserted bold 1 is wrong, because as our glorious Imperator says I made a double payment of $1,000 that month. Should read

Code:

3,771.30     1.05     3,959.86     135.09     3,824.77
3,824.77     1.05     4,016.01     2,000.00     2,016.01
2,016.01     1.05     2,116.81     2,000.00     116.81
116.81     1.05     122.65     2,000.00     -1,877.35

Which means 17 payments of $2,000 and a final payment of $122.65, which is $34,122.65, meaning my original statement was wrong, it is better to clear the small loan first.

The first set of calculations are right as far as I can see.
Oh dear, and the half and half calcuations has another error, a copy pasta one. The last two lines of the big loan payments show $2,001 and $2,002 payments, because I forgot to ensure I was copying the cells rather than filling a series. This means an underpayment of $5.03 due to my original calculations.

The formulae in the spreadsheet are correct, both cases were down to errors in my inputting of figures.