loan without set payments

[conzmom]

I loaned my son money to continue his education. Here are the specifics-

• starting amount $6000
• interest 2.5%
• there is no set time to pay back - he plans to pay more after schooling is over
• he will be making $100 payments or more each month until out of school - then it will increase
• he will be adding more to this loan at semester time for 3 years

So, the amount of the loan will be increasing through the loan period and he doesn't have a set payment since he'll pay what he can each month as "good faith"

How can I create a spreadsheet to track this?

 

[BadgerRoad]

Have you tried an amortization schedule? I'm attaching one that might work.

Amortization Student Loan.xlsx
	A	B	C	D	E	F	G
1	Amount	$ 6,000.00
2	Ann Int	2.5000%	0.208333%
3	Payment	$ 100.00
4
5	Date	Term	Amount	Payment	Interest	Principal	Amount
6	12/1/2022	1	$ 6,000.00	$ 100.00	$ 6.25	$ 93.75	$ 5,906.25
7	1/1/2023	2	$ 5,906.25	$ 100.00	$ 12.30	$ 87.70	$ 5,818.55
8	2/1/2023	3	$ 5,818.55	$ 100.00	$ 12.12	$ 87.88	$ 5,730.68
9	3/1/2023	4	$ 5,730.68	$ 100.00	$ 11.94	$ 88.06	$ 5,642.62
10	4/1/2023	5	$ 5,642.62	$ 100.00	$ 11.76	$ 88.24	$ 5,554.37
11	5/1/2023	6	$ 5,554.37	$ 100.00	$ 11.57	$ 88.43	$ 5,465.94
12	6/1/2023	7	$ 5,465.94	$ 100.00	$ 11.39	$ 88.61	$ 5,377.33
13	7/1/2023	8	$ 5,377.33	$ 100.00	$ 11.20	$ 88.80	$ 5,288.53
14	8/1/2023	9	$ 5,288.53	$ 100.00	$ 11.02	$ 88.98	$ 5,199.55
15	9/1/2023	10	$ 5,199.55	$ 100.00	$ 10.83	$ 89.17	$ 5,110.38
16	10/1/2023	11	$ 5,110.38	$ 100.00	$ 10.65	$ 89.35	$ 5,021.03
17	11/1/2023	12	$ 5,021.03	$ 100.00	$ 10.46	$ 89.54	$ 4,931.49
18	12/1/2023	13	$ 4,931.49	$ 100.00	$ 10.27	$ 89.73	$ 4,841.76
19	1/1/2024	14	$ 4,841.76	$ 100.00	$ 10.09	$ 89.91	$ 4,751.85
20	2/1/2024	15	$ 4,751.85	$ 100.00	$ 9.90	$ 90.10	$ 4,661.75
21	3/1/2024	16	$ 4,661.75	$ 100.00	$ 9.71	$ 90.29	$ 4,571.46
22	4/1/2024	17	$ 4,571.46	$ 100.00	$ 9.52	$ 90.48	$ 4,480.99
23	5/1/2024	18	$ 4,480.99	$ 100.00	$ 9.34	$ 90.66	$ 4,390.32
24	6/1/2024	19	$ 4,390.32	$ 100.00	$ 9.15	$ 90.85	$ 4,299.47
25	7/1/2024	20	$ 4,299.47	$ 100.00	$ 8.96	$ 91.04	$ 4,208.43
26	8/1/2024	21	$ 4,208.43	$ 100.00	$ 8.77	$ 91.23	$ 4,117.19
27	9/1/2024	22	$ 4,117.19	$ 100.00	$ 8.58	$ 91.42	$ 4,025.77
28	10/1/2024	23	$ 4,025.77	$ 100.00	$ 8.39	$ 91.61	$ 3,934.16
29	11/1/2024	24	$ 3,934.16	$ 100.00	$ 8.20	$ 91.80	$ 3,842.35
30	12/1/2024	25	$ 3,842.35	$ 100.00	$ 8.00	$ 92.00	$ 3,750.36
31	1/1/2025	26	$ 3,750.36	$ 100.00	$ 7.81	$ 92.19	$ 3,658.17
32	2/1/2025	27	$ 3,658.17	$ 100.00	$ 7.62	$ 92.38	$ 3,565.79
33	3/1/2025	28	$ 3,565.79	$ 100.00	$ 7.43	$ 92.57	$ 3,473.22
34	4/1/2025	29	$ 3,473.22	$ 100.00	$ 7.24	$ 92.76	$ 3,380.46
35	5/1/2025	30	$ 3,380.46	$ 100.00	$ 7.04	$ 92.96	$ 3,287.50
36	6/1/2025	31	$ 3,287.50	$ 100.00	$ 6.85	$ 93.15	$ 3,194.35
37	7/1/2025	32	$ 3,194.35	$ 100.00	$ 6.65	$ 93.35	$ 3,101.00
38	8/1/2025	33	$ 3,101.00	$ 100.00	$ 6.46	$ 93.54	$ 3,007.46
39	9/1/2025	34	$ 3,007.46	$ 100.00	$ 6.27	$ 93.73	$ 2,913.73
40	10/1/2025	35	$ 2,913.73	$ 100.00	$ 6.07	$ 93.93	$ 2,819.80
41	11/1/2025	36	$ 2,819.80	$ 100.00	$ 5.87	$ 94.13	$ 2,725.68
42	12/1/2025	37	$ 2,725.68	$ 100.00	$ 5.68	$ 94.32	$ 2,631.35
43	1/1/2026	38	$ 2,631.35	$ 100.00	$ 5.48	$ 94.52	$ 2,536.84
44	2/1/2026	39	$ 2,536.84	$ 100.00	$ 5.29	$ 94.71	$ 2,442.12
45	3/1/2026	40	$ 2,442.12	$ 100.00	$ 5.09	$ 94.91	$ 2,347.21
46	4/1/2026	41	$ 2,347.21	$ 100.00	$ 4.89	$ 95.11	$ 2,252.10
47	5/1/2026	42	$ 2,252.10	$ 100.00	$ 4.69	$ 95.31	$ 2,156.79
48	6/1/2026	43	$ 2,156.79	$ 100.00	$ 4.49	$ 95.51	$ 2,061.28
49	7/1/2026	44	$ 2,061.28	$ 100.00	$ 4.29	$ 95.71	$ 1,965.58
50	8/1/2026	45	$ 1,965.58	$ 100.00	$ 4.09	$ 95.91	$ 1,869.67
51	9/1/2026	46	$ 1,869.67	$ 100.00	$ 3.90	$ 96.10	$ 1,773.57
52	10/1/2026	47	$ 1,773.57	$ 100.00	$ 3.69	$ 96.31	$ 1,677.26
53	11/1/2026	48	$ 1,677.26	$ 100.00	$ 3.49	$ 96.51	$ 1,580.76
54	12/1/2026	49	$ 1,580.76	$ 100.00	$ 3.29	$ 96.71	$ 1,484.05
55	1/1/2027	50	$ 1,484.05	$ 100.00	$ 3.09	$ 96.91	$ 1,387.14
56	2/1/2027	51	$ 1,387.14	$ 100.00	$ 2.89	$ 97.11	$ 1,290.03
57	3/1/2027	52	$ 1,290.03	$ 100.00	$ 2.69	$ 97.31	$ 1,192.72
58	4/1/2027	53	$ 1,192.72	$ 100.00	$ 2.48	$ 97.52	$ 1,095.20
59	5/1/2027	54	$ 1,095.20	$ 100.00	$ 2.28	$ 97.72	$ 997.49
60	6/1/2027	55	$ 997.49	$ 100.00	$ 2.08	$ 97.92	$ 899.56
61	7/1/2027	56	$ 899.56	$ 100.00	$ 1.87	$ 98.13	$ 801.44
62	8/1/2027	57	$ 801.44	$ 100.00	$ 1.67	$ 98.33	$ 703.11
63	9/1/2027	58	$ 703.11	$ 100.00	$ 1.46	$ 98.54	$ 604.57
64	10/1/2027	59	$ 604.57	$ 100.00	$ 1.26	$ 98.74	$ 505.83
65	11/1/2027	60	$ 505.83	$ 100.00	$ 1.05	$ 98.95	$ 406.89
66	12/1/2027	61	$ 406.89	$ 100.00	$ 0.85	$ 99.15	$ 307.73
67	1/1/2028	62	$ 307.73	$ 100.00	$ 0.64	$ 99.36	$ 208.38
68	2/1/2028	63	$ 208.38	$ 100.00	$ 0.43	$ 99.57	$ 108.81
69	3/1/2028	64	$ 108.81	$ 100.00	$ 0.23	$ 99.77	$ 9.04
70	4/1/2028	65	$ 9.04	$ 9.04	$ 0.02	$ 9.02	$ 0.02
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2/12
C6	C6	=B1
D6:D70	D6	=MIN($B$3,C6)
E6	E6	=$B$1*$B$2/24
F6:F70	F6	=D6-E6
G6:G70	G6	=MAX(C6-F6,0)
C7:C70	C7	=G6
E7:E70	E7	=C7*$B$2/12
A7:A70	A7	=EDATE(A6,1)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$B$3	D6:D70
solver_lhs1	=Sheet1!$G$65	C66
solver_opt	=Sheet1!$G$65	C66

 

[BadgerRoad]

Just go into the schedule and change the payment wherever it needs to change. Same with the loan amount if funds are added. Let me know if you have questions.

 

[Dave Patton]

T202211a.xlsm
	A	B	C	D	E	F	G	H
1	Date	Num	Payment	Interest	Additional	Principal	Interest	2.50%
2						6,000.00		0.21%
3		1	100.00	12.50		5,912.50
4		2	100.00	12.32		5,824.82
5		3	100.00	12.14		5,736.96
6		4	100.00	11.95		5,648.91
7		5	100.00	11.77		5,560.68
8		6	100.00	11.58		5,472.26
9		7	100.00	11.40		5,383.66
10		8	100.00	11.22		5,294.88
11		9	100.00	11.03		5,205.91
12		10	100.00	10.85	5,000.00	10,116.76
13		11	100.00	21.08		10,037.84
4b

Cell Formulas
Range	Formula
H2	H2	=H1/12
D3:D13	D3	=ROUND($H$2*F2,2)
F3:F13	F3	=F2+D3-C3+E3

Named Ranges
Name	Refers To	Cells
Principal	='4b'!$F$2	F3, D3