Using Lambda to create a Loan Amortization Table

[aryan1997]

Hi all,

I have created a partial formula that calculates the amount of EMI going towards interest and principal. I am, however, looking to calculate the outstanding balance after each period as well. The formula for outstanding balance for each period would be - Example

Period 1 ost bal = Loan value - Principal in Period 1
Period 2 ost bal = Period 1 ost bal - Principal in Period 2

Would appreciate any help or ideas on how to go about referring to previous periods in my formulas. Bonus would be if I can combine all of these columns together to show EMI, principal, interest and ost bal all at once.

Thanks.

Note: I have access to Office 365 version of excel.

Book5
	A	B	C	D	E
1					Loan Amort Formula
2	Loan Value	10,000,000			70,833
3	Tenure (Yrs)	15			70,638
4	Interest Rate	8.50%			70,440
5	Start Date	1/1/2016			70,242
6					70,042
7					69,840
8					69,638
9					69,433
10					69,228
11					69,020
12					68,812
13					68,602
14					68,390
15					68,177
16					67,962
17					67,746
18					67,529
19					67,309
20					67,089
21					66,866
22					66,643
23					66,417
24					66,190
25					65,961
26					65,731
27					65,499
28					65,266
29					65,030
30					64,793
31					64,555
32					64,315
33					64,073
34					63,829
35					63,584
36					63,336
37					63,087
38					62,837
39					62,584
40					62,330
41					62,074
42					61,816
43					61,557
44					61,295
45					61,032
46					60,767
47					60,500
48					60,231
49					59,960
50					59,687
51					59,412
52					59,135
53					58,857
54					58,576
55					58,294
56					58,009
57					57,722
58					57,434
59					57,143
60					56,850
61					56,555
62					56,258
63					55,959
64					55,658
65					55,355
66					55,050
67					54,742
68					54,432
69					54,120
70					53,806
71					53,490
72					53,171
73					52,850
74					52,527
75					52,201
76					51,874
77					51,544
78					51,211
79					50,876
80					50,539
81					50,200
82					49,858
83					49,513
84					49,167
85					48,817
86					48,466
87					48,111
88					47,755
89					47,395
90					47,034
91					46,669
92					46,302
93					45,933
94					45,561
95					45,186
96					44,808
97					44,428
98					44,045
99					43,660
100					43,272
101					42,881
102					42,487
103					42,090
104					41,691
105					41,289
106					40,884
107					40,476
108					40,065
109					39,651
110					39,234
111					38,815
112					38,392
113					37,967
114					37,538
115					37,106
116					36,672
117					36,234
118					35,793
119					35,349
120					34,902
121					34,452
122					33,998
123					33,541
124					33,081
125					32,618
126					32,152
127					31,682
128					31,209
129					30,732
130					30,253
131					29,769
132					29,283
133					28,793
134					28,299
135					27,802
136					27,301
137					26,797
138					26,290
139					25,778
140					25,263
141					24,745
142					24,222
143					23,697
144					23,167
145					22,633
146					22,096
147					21,555
148					21,010
149					20,462
150					19,909
151					19,353
152					18,792
153					18,228
154					17,659
155					17,087
156					16,510
157					15,930
158					15,345
159					14,756
160					14,163
161					13,566
162					12,965
163					12,359
164					11,749
165					11,135
166					10,516
167					9,893
168					9,266
169					8,634
170					7,997
171					7,356
172					6,711
173					6,061
174					5,406
175					4,747
176					4,083
177					3,415
178					2,741
179					2,063
180					1,380
181					693
Sheet1

Cell Formulas
Range	Formula
E2:E181	E2	=LET(LV,B2,tenure,B3,interestrate,B4,startdate,B5, emi,-PMT(interestrate/12,tenure*12,LV), periodseq,SEQUENCE(180,1,1,1), interest,-IPMT(interestrate/12,periodseq,COUNT(periodseq),LV), Principal,emi-interest, interest )
Dynamic array formulas.

 

[aryan1997]

Upon further research on the lambda forums page, I found exactly what I was looking for. This is great!

Here is the link - AMORTIZE