Present Balance on Amortization

[jeff2813]

Hi there, I have an amortization table that shows a running remaining total of my loan balance. Although i can scroll down to see the balance on any given date i would like to be able to see the current balance in a cell without scrolling down. I added in a remaining balance title in cell in F12 but would like to know if there is a formula to calculate the "current balance as of todays date" in H12. Ex. As of today Dec 23 the current balance should display $9458.44. Hopefully this makes sense. Even if the columns #'s don't fully work, I can adjust them to my sheet.

1 A	B	C	D	E	F	G	H
2
3
4
5
6		Loan Amount	10,000.00			Rate (per period)	0.192%​
7		Annual Interest Rate	5.00%			Total Payments	11,256.09
8		Term of Loan in Years	5			Total Interest	1,256.09
9		First Payment Date	10/27/2019			Est. Interest Savings	55.50
10		Payment Frequency	Bi-Weekly				.
11		Compound Period	Bi-Weekly				.
12		Payment Type	End of Period		Balance	remaining
		Rounding	On
		Bi-Weekly Payment	87.01
Amortization Schedule
No.	Due Date	Payment Due	Additional Payment		Interest	Principal	Balance
							10,000.00
1	10/27/19	87.01	100.00		19.23	167.78	9,832.22
2	11/10/19	87.01	100.00		18.91	168.10	9,664.12
3	11/24/19	87.01			18.58	68.43	9,595.69
4	12/8/19	87.01			18.45	68.56	9,527.13
5	12/22/19	87.01			18.32	68.69	9,458.44
6	1/5/20	87.01			18.19	68.82	9,389.62
7	1/19/20	87.01			18.06	68.95	9,320.67
8	2/2/20	87.01			17.92	69.09	9,251.58
9	2/16/20	87.01			17.79	69.22	9,182.36
10	3/1/20	87.01			17.66	69.35	9,113.01
11	3/15/20	87.01			17.53	69.48	9,043.53
12	3/29/20	87.01			17.39	69.62	8,973.91
13	4/12/20	87.01			17.26	69.75	8,904.16
14	4/26/20	87.01			17.12	69.89	8,834.27
15	5/10/20	87.01			16.99	70.02	8,764.25
16	5/24/20	87.01			16.85	70.16	8,694.09
17	6/7/20	87.01			16.72	70.29	8,623.80
18	6/21/20	87.01			16.58	70.43	8,553.37
19	7/5/20	87.01			16.45	70.56	8,482.81
20	7/19/20	87.01			16.31	70.70	8,412.11
21	8/2/20	87.01			16.18	70.83	8,341.28
22	8/16/20	87.01			16.04	70.97	8,270.31
23	8/30/20	87.01			15.90	71.11	8,199.20
24	9/13/20	87.01			15.77	71.24	8,127.96
25	9/27/20	87.01			15.63	71.38	8,056.58
26	10/11/20	87.01			15.49	71.52	7,985.06
27	10/25/20	87.01			15.36	71.65	7,913.41
28	11/8/20	87.01			15.22	71.79	7,841.62
29	11/22/20	87.01			15.08	71.93	7,769.69
30	12/6/20	87.01			14.94	72.07	7,697.62
31	12/20/20	87.01			14.80	72.21	7,625.41
32	1/3/21	87.01			14.66	72.35	7,553.06
33	1/17/21	87.01			14.53	72.48	7,480.58
34	1/31/21	87.01			14.39	72.62	7,407.96
35	2/14/21	87.01			14.25	72.76	7,335.20
36	2/28/21	87.01			14.11	72.90	7,262.30
37	3/14/21	87.01			13.97	73.04	7,189.26
38	3/28/21	87.01			13.83	73.18	7,116.08
39	4/11/21	87.01			13.68	73.33	7,042.75
40	4/25/21	87.01			13.54	73.47	6,969.28
41	5/9/21	87.01			13.40	73.61	6,895.67
42	5/23/21	87.01			13.26	73.75	6,821.92
43	6/6/21	87.01			13.12	73.89	6,748.03
44	6/20/21	87.01			12.98	74.03	6,674.00
45	7/4/21	87.01			12.83	74.18	6,599.82
46	7/18/21	87.01			12.69	74.32	6,525.50
47	8/1/21	87.01			12.55	74.46	6,451.04
48	8/15/21	87.01			12.41	74.60	6,376.44
49	8/29/21	87.01			12.26	74.75	6,301.69
50	9/12/21	87.01			12.12	74.89	6,226.80
51	9/26/21	87.01			11.97	75.04	6,151.76
52	10/10/21	87.01			11.83	75.18	6,076.58
53	10/24/21	87.01			11.69	75.32	6,001.26
54	11/7/21	87.01			11.54	75.47	5,925.79
55	11/21/21	87.01			11.40	75.61	5,850.18
56	12/5/21	87.01			11.25	75.76	5,774.42
57	12/19/21	87.01			11.10	75.91	5,698.51
58	1/2/22	87.01			10.96	76.05	5,622.46
59	1/16/22	87.01			10.81	76.20	5,546.26
60	1/30/22	87.01			10.67	76.34	5,469.92
61	2/13/22	87.01			10.52	76.49	5,393.43
62	2/27/22	87.01			10.37	76.64	5,316.79
63	3/13/22	87.01			10.22	76.79	5,240.00
64	3/27/22	87.01			10.08	76.93	5,163.07
65	4/10/22	87.01			9.93	77.08	5,085.99
66	4/24/22	87.01			9.78	77.23	5,008.76
67	5/8/22	87.01			9.63	77.38	4,931.38
68	5/22/22	87.01			9.48	77.53	4,853.85
69	6/5/22	87.01			9.33	77.68	4,776.17
70	6/19/22	87.01			9.18	77.83	4,698.34
71	7/3/22	87.01			9.04	77.97	4,620.37
72	7/17/22	87.01			8.89	78.12	4,542.25
73	7/31/22	87.01			8.74	78.27	4,463.98
74	8/14/22	87.01			8.58	78.43	4,385.55
75	8/28/22	87.01			8.43	78.58	4,306.97
76	9/11/22	87.01			8.28	78.73	4,228.24
77	9/25/22	87.01			8.13	78.88	4,149.36
78	10/9/22	87.01			7.98	79.03	4,070.33
79	10/23/22	87.01			7.83	79.18	3,991.15
80	11/6/22	87.01			7.68	79.33	3,911.82
81	11/20/22	87.01			7.52	79.49	3,832.33
82	12/4/22	87.01			7.37	79.64	3,752.69
83	12/18/22	87.01			7.22	79.79	3,672.90
84	1/1/23	87.01			7.06	79.95	3,592.95
85	1/15/23	87.01			6.91	80.10	3,512.85
86	1/29/23	87.01			6.76	80.25	3,432.60
87	2/12/23	87.01			6.60	80.41	3,352.19
88	2/26/23	87.01			6.45	80.56	3,271.63
89	3/12/23	87.01			6.29	80.72	3,190.91
90	3/26/23	87.01			6.14	80.87	3,110.04
91	4/9/23	87.01			5.98	81.03	3,029.01
92	4/23/23	87.01			5.83	81.18	2,947.83
93	5/7/23	87.01			5.67	81.34	2,866.49
94	5/21/23	87.01			5.51	81.50	2,784.99
95	6/4/23	87.01			5.36	81.65	2,703.34
96	6/18/23	87.01			5.20	81.81	2,621.53
97	7/2/23	87.01			5.04	81.97	2,539.56
98	7/16/23	87.01			4.88	82.13	2,457.43
99	7/30/23	87.01			4.73	82.28	2,375.15
100	8/13/23	87.01			4.57	82.44	2,292.71
101	8/27/23	87.01			4.41	82.60	2,210.11
102	9/10/23	87.01			4.25	82.76	2,127.35
103	9/24/23	87.01			4.09	82.92	2,044.43
104	10/8/23	87.01			3.93	83.08	1,961.35
105	10/22/23	87.01			3.77	83.24	1,878.11
106	11/5/23	87.01			3.61	83.40	1,794.71
107	11/19/23	87.01			3.45	83.56	1,711.15
108	12/3/23	87.01			3.29	83.72	1,627.43
109	12/17/23	87.01			3.13	83.88	1,543.55
110	12/31/23	87.01			2.97	84.04	1,459.51
111	1/14/24	87.01			2.81	84.20	1,375.31
112	1/28/24	87.01			2.64	84.37	1,290.94
113	2/11/24	87.01			2.48	84.53	1,206.41
114	2/25/24	87.01			2.32	84.69	1,121.72
115	3/10/24	87.01			2.16	84.85	1,036.87
116	3/24/24	87.01			1.99	85.02	951.85
117	4/7/24	87.01			1.83	85.18	866.67
118	4/21/24	87.01			1.67	85.34	781.33
119	5/5/24	87.01			1.50	85.51	695.82
120	5/19/24	87.01			1.34	85.67	610.15
121	6/2/24	87.01			1.17	85.84	524.31
122	6/16/24	87.01			1.01	86.00	438.31
123	6/30/24	87.01			0.84	86.17	352.14
124	7/14/24	87.01			0.68	86.33	265.81
125	7/28/24	87.01			0.51	86.50	179.31
126	8/11/24	87.01			0.34	86.67	92.64
127	8/25/24	87.01			0.18	86.83	5.81
128	9/8/24	5.82			0.01	5.81	0.00
			End

 

[Eric W]

How about:

=VLOOKUP(TODAY(),B22:H149,7)

 

[jeff2813]

How about:

=VLOOKUP(TODAY(),B22:H149,7)

Works great thank you very much!

 

[jeff2813]

Sorry. What exactly does the 7 refer to? TY

 

[Eric W]

The way VLOOKUP up works is that it finds the matching value (TODAY() in this case, and it's also a <= condition for the match) in the first column of the range (B22:H149). Once it finds the match, it returns the value from the 7th column over, which is the H column.

 

[jeff2813]

Awesome, Thanks alot