Spreadsheet: 112448d1308581850-shifting-an-average-formula-down-automatically-excel-example
Sheet: Sheet1

Scenario 1 - No interest Only |  |  |  |  |  | Scenario 2 - 6 Months of Interest Only
 | Amortization Term | 36 |  |  |  |  | Amortization Term | 36
 | Interest Rate | 0.05 |  |  |  |  | Interest Rate | 0.05
 | Interest Only Period | 0 |  |  |  |  | Interest Only Period | 6
 | Amount | 1000000 |  |  |  |  | Amount | 1000000
Payment Number | Actual Amortization Schedule | Average Amortization | Principal Balance |  |  | Payment Number | Actual Amortization Schedule | Average Amortization | Principal Balance
1 | 10434.45712047529 | 13840.54784656152 | 986159.4521534385 |  |  | 1 | 0 | 0 | 1000000
2 | 10956.17997649906 | 13841 | 972318.4521534385 |  |  | 2 | 0 | 0 | 1000000
3 | 11503.98897532401 | 13841 | 958477.4521534385 |  |  | 3 | 0 | 0 | 1000000
4 | 12079.18842409021 | 13841 | 944636.4521534385 |  |  | 4 | 0 | 0 | 1000000
5 | 12683.14784529473 | 13841 | 930795.4521534385 |  |  | 5 | 0 | 0 | 1000000
6 | 13317.30523755945 | 13841 | 916954.4521534385 |  |  | 6 | 0 | 0 | 1000000
7 | 13983.17049943742 | 13841 | 903113.4521534385 |  |  | 7 | 10434.45712047529 | 13840.54784656152 | 986159.4521534385
8 | 14682.32902440931 | 13841 | 889272.4521534385 |  |  | 8 | 10956.17997649906 | 13840.54784656152 | 972318.9043068769
9 | 15416.44547562976 | 13841 | 875431.4521534385 |  |  | 9 | 11503.98897532401 | 13840.54784656152 | 958478.3564603154
10 | 16187.26774941125 | 13841 | 861590.4521534385 |  |  | 10 | 12079.18842409021 | 13840.54784656152 | 944637.8086137539
11 | 16996.63113688182 | 13841 | 847749.4521534385 |  |  | 11 | 12683.14784529473 | 13840.54784656152 | 930797.2607671923
12 | 17846.46269372591 | 13841 | 833908.4521534385 |  |  | 12 | 13317.30523755945 | 13840.54784656152 | 916956.7129206308
13 | 18738.7858284122 | 24855.63540585946 | 809052.816747579 |  |  | 13 | 13983.17049943742 | 13840.54784656152 | 903116.1650740693
14 | 19675.72511983281 | 24855.63540585946 | 784197.1813417196 |  |  | 14 | 14682.32902440931 | 13840.54784656152 | 889275.6172275078
15 | 20659.51137582445 | 24855.63540585946 | 759341.5459358601 |  |  | 15 | 15416.44547562976 | 13840.54784656152 | 875435.0693809462
16 | 21692.48694461568 | 24855.63540585946 | 734485.9105300007 |  |  | 16 | 16187.26774941125 | 13840.54784656152 | 861594.5215343847
17 | 22777.11129184647 | 24855.63540585946 | 709630.2751241412 |  |  | 17 | 16996.63113688182 | 13840.54784656152 | 847753.9736878232
18 | 23915.9668564388 | 24855.63540585946 | 684774.6397182818 |  |  | 18 | 17846.46269372591 | 13840.54784656152 | 833913.4258412616
19 | 25111.76519926073 | 24855.63540585946 | 659919.0043124224 |  |  | 19 | 18738.7858284122 | 24855.63540585946 | 809057.7904354022
20 | 26367.35345922377 | 24855.63540585946 | 635063.3689065629 |  |  | 20 | 19675.72511983281 | 24855.63540585946 | 784202.1550295427
21 | 27685.72113218495 | 24855.63540585946 | 610207.7335007035 |  |  | 21 | 20659.51137582445 | 24855.63540585946 | 759346.5196236833
22 | 29070.00718879421 | 24855.63540585946 | 585352.098094844 |  |  | 22 | 21692.48694461568 | 24855.63540585946 | 734490.8842178239
23 | 30523.50754823389 | 24855.63540585946 | 560496.4626889846 |  |  | 23 | 22777.11129184647 | 24855.63540585946 | 709635.2488119644
24 | 32049.68292564561 | 24855.63540585946 | 535640.8272831251 |  |  | 24 | 23915.9668564388 | 24855.63540585946 | 684779.613406105
25 | 33652.16707192788 | 44637.15008091233 | 491003.6772022128 |  |  | 25 | 25111.76519926073 | 24855.63540585946 | 659923.9780002455
26 | 35334.77542552429 | 44637.15008091233 | 446366.5271213005 |  |  | 26 | 26367.35345922377 | 24855.63540585946 | 635068.3425943861
27 | 37101.51419680049 | 44637.15008091233 | 401729.3770403881 |  |  | 27 | 27685.72113218495 | 24855.63540585946 | 610212.7071885266
28 | 38956.58990664052 | 44637.15008091233 | 357092.2269594758 |  |  | 28 | 29070.00718879421 | 24855.63540585946 | 585357.0717826672
29 | 40904.41940197254 | 44637.15008091233 | 312455.0768785634 |  |  | 29 | 30523.50754823389 | 24855.63540585946 | 560501.4363768077
30 | 42949.64037207117 | 44637.15008091233 | 267817.9267976511 |  |  | 30 | 32049.68292564561 | 24855.63540585946 | 535645.8009709483
31 | 45097.12239067471 | 44637.15008091233 | 223180.7767167388 |  |  | 31 | 33652.16707192788 | 38149.85106248948 | 497495.9499084588
32 | 47351.97851020846 | 44637.15008091233 | 178543.6266358264 |  |  | 32 | 35334.77542552429 | 38149.85106248948 | 459346.0988459693
33 | 49719.57743571888 | 44637.15008091233 | 133906.4765549141 |  |  | 33 | 37101.51419680049 | 38149.85106248948 | 421196.2477834798
34 | 52205.55630750482 | 44637.15008091233 | 89269.32647400175 |  |  | 34 | 38956.58990664052 | 38149.85106248948 | 383046.3967209903
35 | 54815.83412288011 | 44637.15008091233 | 44632.17639308942 |  |  | 35 | 40904.41940197254 | 38149.85106248948 | 344896.5456585008
36 | 57556.62582902408 | 44637.15008091233 | -4.973687822915963 |  |  | 36 | 42949.64037207117 | 38149.85106248948 | 306746.6945960113