unorthadox Amortization table

Hi all!

I am used to building AMORT table by hand, but this one has me a little baffled. Herein lies the problem (please see attached spreadsheet).

Problem: I need to use :

1) an Excel command to look-up the EOP Balance (Balance End) that searches for 0
2) then Sum the balance for ALL pmts should the EOP be Zero.

I am not sure if this would be an IF statement, which doesn't seem to work, or if there is a better way to look up the EOP balance (when 0) and sum said PMTover the AMORT term. This should be easy, but I do not know the best way to do it. In the example below, the period ends at 72.

THANK YOU! I WOULD BE WILLING TO COMPENSATE ANY WHO CAN COME UP WITH THE CORRECT ANSWER!

BEST.

STEW



INPUT Principal $3,000.00
Maturity (years) 12
Payments per year 6
Interest rate per year 12.0%


OUTPUT Payment $78.98
Total Payments ???????
Total Interest


Periods BOP Bal PMT Interest Principal EOP Bal
1 $3,000.00 $78.98 $60.00 $18.98 $2,981.02
2 $2,981.02 $78.98 $59.62 $19.36 $2,961.66
3 $2,961.66 $78.98 $59.23 $19.75 $2,941.91
4 $2,941.91 $78.98 $58.84 $20.14 $2,921.77
5 $2,921.77 $78.98 $58.44 $20.55 $2,901.22
6 $2,901.22 $78.98 $58.02 $20.96 $2,880.27
7 $2,880.27 $78.98 $57.61 $21.38 $2,858.89
8 $2,858.89 $78.98 $57.18 $21.80 $2,837.09
9 $2,837.09 $78.98 $56.74 $22.24 $2,814.85
10 $2,814.85 $78.98 $56.30 $22.68 $2,792.17
11 $2,792.17 $78.98 $55.84 $23.14 $2,769.03
12 $2,769.03 $78.98 $55.38 $23.60 $2,745.43
13 $2,745.43 $78.98 $54.91 $24.07 $2,721.36
14 $2,721.36 $78.98 $54.43 $24.55 $2,696.81
15 $2,696.81 $78.98 $53.94 $25.04 $2,671.76
16 $2,671.76 $78.98 $53.44 $25.55 $2,646.22
17 $2,646.22 $78.98 $52.92 $26.06 $2,620.16
18 $2,620.16 $78.98 $52.40 $26.58 $2,593.58
19 $2,593.58 $78.98 $51.87 $27.11 $2,566.47
20 $2,566.47 $78.98 $51.33 $27.65 $2,538.82
21 $2,538.82 $78.98 $50.78 $28.20 $2,510.62
22 $2,510.62 $78.98 $50.21 $28.77 $2,481.85
23 $2,481.85 $78.98 $49.64 $29.34 $2,452.51
24 $2,452.51 $78.98 $49.05 $29.93 $2,422.58
25 $2,422.58 $78.98 $48.45 $30.53 $2,392.05
26 $2,392.05 $78.98 $47.84 $31.14 $2,360.91
27 $2,360.91 $78.98 $47.22 $31.76 $2,329.15
28 $2,329.15 $78.98 $46.58 $32.40 $2,296.75
29 $2,296.75 $78.98 $45.93 $33.05 $2,263.70
30 $2,263.70 $78.98 $45.27 $33.71 $2,230.00
31 $2,230.00 $78.98 $44.60 $34.38 $2,195.62
32 $2,195.62 $78.98 $43.91 $35.07 $2,160.55
33 $2,160.55 $78.98 $43.21 $35.77 $2,124.78
34 $2,124.78 $78.98 $42.50 $36.48 $2,088.29
35 $2,088.29 $78.98 $41.77 $37.21 $2,051.08
36 $2,051.08 $78.98 $41.02 $37.96 $2,013.12
37 $2,013.12 $78.98 $40.26 $38.72 $1,974.40
38 $1,974.40 $78.98 $39.49 $39.49 $1,934.91
39 $1,934.91 $78.98 $38.70 $40.28 $1,894.63
40 $1,894.63 $78.98 $37.89 $41.09 $1,853.54
41 $1,853.54 $78.98 $37.07 $41.91 $1,811.63
42 $1,811.63 $78.98 $36.23 $42.75 $1,768.88
43 $1,768.88 $78.98 $35.38 $43.60 $1,725.28
44 $1,725.28 $78.98 $34.51 $44.47 $1,680.81
45 $1,680.81 $78.98 $33.62 $45.36 $1,635.44
46 $1,635.44 $78.98 $32.71 $46.27 $1,589.17
47 $1,589.17 $78.98 $31.78 $47.20 $1,541.97
48 $1,541.97 $78.98 $30.84 $48.14 $1,493.83
49 $1,493.83 $78.98 $29.88 $49.10 $1,444.73
50 $1,444.73 $78.98 $28.89 $50.09 $1,394.64
51 $1,394.64 $78.98 $27.89 $51.09 $1,343.55
52 $1,343.55 $78.98 $26.87 $52.11 $1,291.44
53 $1,291.44 $78.98 $25.83 $53.15 $1,238.29
54 $1,238.29 $78.98 $24.77 $54.21 $1,184.08
55 $1,184.08 $78.98 $23.68 $55.30 $1,128.78
56 $1,128.78 $78.98 $22.58 $56.40 $1,072.37
57 $1,072.37 $78.98 $21.45 $57.53 $1,014.84
58 $1,014.84 $78.98 $20.30 $58.68 $956.16
59 $956.16 $78.98 $19.12 $59.86 $896.30
60 $896.30 $78.98 $17.93 $61.05 $835.25
61 $835.25 $78.98 $16.70 $62.28 $772.97
62 $772.97 $78.98 $15.46 $63.52 $709.45
63 $709.45 $78.98 $14.19 $64.79 $644.66
64 $644.66 $78.98 $12.89 $66.09 $578.57
65 $578.57 $78.98 $11.57 $67.41 $511.16
66 $511.16 $78.98 $10.22 $68.76 $442.40
67 $442.40 $78.98 $8.85 $70.13 $372.27
68 $372.27 $78.98 $7.45 $71.54 $300.74
69 $300.74 $78.98 $6.01 $72.97 $227.77
70 $227.77 $78.98 $4.56 $74.43 $153.35
71 $153.35 $78.98 $3.07 $75.91 $77.43
72 $77.43 $78.98 $1.55 $77.43 $0.00
73 $0.00 $78.98 $0.00 $78.98 $(78.98)
74 $(78.98) $78.98 $(1.58) $80.56 $(159.54)
75 $(159.54) $78.98 $(3.19) $82.17 $(241.71)
76 $(241.71) $78.98 $(4.83) $83.81 $(325.53)
77 $(325.53) $78.98 $(6.51) $85.49 $(411.02)
78 $(411.02) $78.98 $(8.22) $87.20 $(498.22)
79 $(498.22) $78.98 $(9.96) $88.94 $(587.16)
80 $(587.16) $78.98 $(11.74) $90.72 $(677.89)
81 $(677.89) $78.98 $(13.56) $92.54 $(770.43)
82 $(770.43) $78.98 $(15.41) $94.39 $(864.81)
83 $(864.81) $78.98 $(17.30) $96.28 $(961.09)
84 $(961.09) $78.98 $(19.22) $98.20 $(1,059.29)
85 $(1,059.29) $78.98 $(21.19) $100.17 $(1,159.46)
86 $(1,159.46) $78.98 $(23.19) $102.17 $(1,261.63)
87 $(1,261.63) $78.98 $(25.23) $104.21 $(1,365.84)
88 $(1,365.84) $78.98 $(27.32) $106.30 $(1,472.14)
89 $(1,472.14) $78.98 $(29.44) $108.42 $(1,580.56)
90 $(1,580.56) $78.98 $(31.61) $110.59 $(1,691.15)
91 $(1,691.15) $78.98 $(33.82) $112.80 $(1,803.96)
92 $(1,803.96) $78.98 $(36.08) $115.06 $(1,919.02)
93 $(1,919.02) $78.98 $(38.38) $117.36 $(2,036.38)
94 $(2,036.38) $78.98 $(40.73) $119.71 $(2,156.09)
95 $(2,156.09) $78.98 $(43.12) $122.10 $(2,278.19)
96 $(2,278.19) $78.98 $(45.56) $124.54 $(2,402.73)
97 $(2,402.73) $78.98 $(48.05) $127.04 $(2,529.77)
98 $(2,529.77) $78.98 $(50.60) $129.58 $(2,659.34)
99 $(2,659.34) $78.98 $(53.19) $132.17 $(2,791.51)
100 $(2,791.51) $78.98 $(55.83) $134.81 $(2,926.32)
101 $(2,926.32) $78.98 $(58.53) $137.51 $(3,063.83)
102 $(3,063.83) $78.98 $(61.28) $140.26 $(3,204.09)
103 $(3,204.09) $78.98 $(64.08) $143.06 $(3,347.15)