Excel problem

[Ivaya]

Hello everyone!
I am a student and now I am learning about excel functions. I have a problem and I don't know which formula I should use to solve it.
It is like this:
You have a loan - 100 000
interest rate - 5% (for year)
term - 20 years
initial instalment - 500, but it increases with 3% every year, so the second year it will be 515, and so on.
I have to make a table and it has to represent interest and principal payments every month.

I am not sure if I have to use one formula or to do it in other way, so I will be very thankful if you can give me some guidance.

Kind regards,
Ivaya

 

[earthworm]

T

Hello everyone!
I am a student and now I am learning about excel functions. I have a problem and I don't know which formula I should use to solve it.
It is like this:
You have a loan - 100 000
interest rate - 5% (for year)
term - 20 years
initial instalment - 500, but it increases with 3% every year, so the second year it will be 515, and so on.
I have to make a table and it has to represent interest and principal payments every month.

I am not sure if I have to use one formula or to do it in other way, so I will be very thankful if you can give me some guidance.

Kind regards,
Ivaya

Try below and extend the date range as more cannot be pasted here due to size limitation

Book1
	A	B	C	D	E	F	G	H	I
2	Loan	100,000.00
3	Loan Start Date	1-Jan-21
4	Interest Rate	5%
5	Term	20 Years
6	Initial Installment	500
7
8	Date From	Date To	Days	Interest Rate	Loan Amount	Interest	Installment	Principal Amount	Remaining Amount
9	1-Jan-21	31-Jan-21	31	5%	100,000.00	424.66	500.00	75.34	99,924.66
10	1-Feb-21	28-Feb-21	28	5%	99,924.66	383.27	500.00	116.73	99,807.93
11	1-Mar-21	31-Mar-21	31	5%	99,807.93	423.84	500.00	76.16	99,731.77
12	1-Apr-21	30-Apr-21	30	5%	99,731.77	409.86	500.00	90.14	99,641.63
13	1-May-21	31-May-21	31	5%	99,641.63	423.14	500.00	76.86	99,564.76
14	1-Jun-21	30-Jun-21	30	5%	99,564.76	409.17	500.00	90.83	99,473.93
15	1-Jul-21	31-Jul-21	31	5%	99,473.93	422.42	500.00	77.58	99,396.36
16	1-Aug-21	31-Aug-21	31	5%	99,396.36	422.09	500.00	77.91	99,318.45
17	1-Sep-21	30-Sep-21	30	5%	99,318.45	408.16	500.00	91.84	99,226.61
18	1-Oct-21	31-Oct-21	31	5%	99,226.61	421.37	500.00	78.63	99,147.98
19	1-Nov-21	30-Nov-21	30	5%	99,147.98	407.46	500.00	92.54	99,055.44
20	1-Dec-21	31-Dec-21	31	5%	99,055.44	420.65	500.00	79.35	98,976.09
21	1-Jan-22	31-Jan-22	31	5%	98,976.09	420.31	515.00	94.69	98,881.40
22	1-Feb-22	28-Feb-22	28	5%	98,881.40	379.27	515.00	135.73	98,745.67
23	1-Mar-22	31-Mar-22	31	5%	98,745.67	419.33	515.00	95.67	98,650.00
24	1-Apr-22	30-Apr-22	30	5%	98,650.00	405.41	515.00	109.59	98,540.41
25	1-May-22	31-May-22	31	5%	98,540.41	418.46	515.00	96.54	98,443.87
26	1-Jun-22	30-Jun-22	30	5%	98,443.87	404.56	515.00	110.44	98,333.43
27	1-Jul-22	31-Jul-22	31	5%	98,333.43	417.58	515.00	97.42	98,236.01
28	1-Aug-22	31-Aug-22	31	5%	98,236.01	417.17	515.00	97.83	98,138.18
29	1-Sep-22	30-Sep-22	30	5%	98,138.18	403.31	515.00	111.69	98,026.49
30	1-Oct-22	31-Oct-22	31	5%	98,026.49	416.28	515.00	98.72	97,927.76
31	1-Nov-22	30-Nov-22	30	5%	97,927.76	402.44	515.00	112.56	97,815.21
32	1-Dec-22	31-Dec-22	31	5%	97,815.21	415.38	515.00	99.62	97,715.59
33	1-Jan-23	31-Jan-23	31	5%	97,715.59	414.96	530.45	115.49	97,600.09
34	1-Feb-23	28-Feb-23	28	5%	97,600.09	374.36	530.45	156.09	97,444.00
35	1-Mar-23	31-Mar-23	31	5%	97,444.00	413.80	530.45	116.65	97,327.35
36	1-Apr-23	30-Apr-23	30	5%	97,327.35	399.98	530.45	130.47	97,196.88
37	1-May-23	31-May-23	31	5%	97,196.88	412.75	530.45	117.70	97,079.18
38	1-Jun-23	30-Jun-23	30	5%	97,079.18	398.96	530.45	131.49	96,947.69
39	1-Jul-23	31-Jul-23	31	5%	96,947.69	411.70	530.45	118.75	96,828.93
40	1-Aug-23	31-Aug-23	31	5%	96,828.93	411.19	530.45	119.26	96,709.68
41	1-Sep-23	30-Sep-23	30	5%	96,709.68	397.44	530.45	133.01	96,576.66
42	1-Oct-23	31-Oct-23	31	5%	96,576.66	410.12	530.45	120.33	96,456.33
43	1-Nov-23	30-Nov-23	30	5%	96,456.33	396.40	530.45	134.05	96,322.28
44	1-Dec-23	31-Dec-23	31	5%	96,322.28	409.04	530.45	121.41	96,200.87
45	1-Jan-24	31-Jan-24	31	5%	96,200.87	408.52	546.36	137.84	96,063.03
46	1-Feb-24	29-Feb-24	29	5%	96,063.03	381.62	546.36	164.74	95,898.29
47	1-Mar-24	31-Mar-24	31	5%	95,898.29	407.24	546.36	139.12	95,759.16
48	1-Apr-24	30-Apr-24	30	5%	95,759.16	393.53	546.36	152.83	95,606.33
49	1-May-24	31-May-24	31	5%	95,606.33	406.00	546.36	140.36	95,465.96
50	1-Jun-24	30-Jun-24	30	5%	95,465.96	392.33	546.36	154.04	95,311.93
51	1-Jul-24	31-Jul-24	31	5%	95,311.93	404.75	546.36	141.61	95,170.31
52	1-Aug-24	31-Aug-24	31	5%	95,170.31	404.15	546.36	142.22	95,028.10
53	1-Sep-24	30-Sep-24	30	5%	95,028.10	390.53	546.36	155.84	94,872.26
54	1-Oct-24	31-Oct-24	31	5%	94,872.26	402.88	546.36	143.48	94,728.78
55	1-Nov-24	30-Nov-24	30	5%	94,728.78	389.30	546.36	157.07	94,571.71
56	1-Dec-24	31-Dec-24	31	5%	94,571.71	401.61	546.36	144.76	94,426.95
57	1-Jan-25	31-Jan-25	31	5%	94,426.95	400.99	562.75	161.76	94,265.19
Sheet1

Cell Formulas
Range	Formula
A9	A9	=B3
B9:B57	B9	=EOMONTH(A9,0)
C9:C57	C9	=B9-A9+1
A10:A57	A10	=B9+1
E9	E9	=$B$2
F9:F57	F9	=D9/365*C9*E9
G9	G9	=B6
H9:H57	H9	=G9-F9
I9:I57	I9	=E9-H9
E10:E57	E10	=I9
G10:G57	G10	=IF(YEAR(A9)+1=YEAR(A10),G9+(G9*3%),G9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I9:I260	Cell Value	<0	text	NO

 

[Ivaya]

Thank you so much!