Looping

[Fedahh]

Hi everyone

I'm new to Alteryx and need some help.

i have this snapshot data which contains loans and bonds data.  we need create card for each loan that count interest and paid amount.

1 1st need to count the loan based on Coupon Frequency = annual 1 time year - semi annual 2 times year and so on until the Balance of discount/ Premium gets = 0

need the card result shown like this:

No.	150
Face Value	400,000
Coupon Frequency	Semi Annual
Tenor_Frequency	60
Bond price	394,721
Coupon Rate	5.75%
Discount/ (Premium)	5,279
Interest_based on frequency	700,000
Issue Price	98.68
Discount/ (Premium)	Discount
Discount/ (Premium)_Based on frequency	88
Market rate (Yield)	5.84%
Amortization expense for 2022 and before	-
Amortization expense for 2023	-
Amortization expense for 2024	170
Carrying amount as of 30 December 2024	394,891	FALSE
Interest expense for 2022 and before	-
Interest expense as of 2023	-
Interest expense as of 2024	1,351,389
Period	Interest paid	Interest Expense	Discount/ Premium amortization	Balance of discount/ Premium (Balance of discount/ Premium - Discount/ Premium amortization)	Carrying value (Carrying value +Discount/ Premium amortization)
1/16/2024				5,279	394,721
7/15/2024	700,000	700,000	88	5,191	394,809
1/13/2025	700,000	700,000	88	5,103	394,897
7/13/2025	700,000	700,000	88	5,015	394,985
1/11/2026	700,000	700,000	88	4,927	395,073
7/11/2026	700,000	700,000	88	4,839	395,161
1/9/2027	700,000	700,000	88	4,751	395,249
7/9/2027	700,000	700,000	88	4,663	395,337
1/7/2028	700,000	700,000	88	4,575	395,425
7/6/2028	700,000	700,000	88	4,487	395,513
1/4/2029	700,000	700,000	88	4,399	395,601
7/4/2029	700,000	700,000	88	4,311	395,689
1/2/2030	700,000	700,000	88	4,223	395,777
7/2/2030	700,000	700,000	88	4,135	395,865
12/31/2030	700,000	700,000	88	4,047	395,953
6/30/2031	700,000	700,000	88	3,959	396,041
12/29/2031	700,000	700,000	88	3,871	396,129
6/27/2032	700,000	700,000	88	3,783	396,217
12/26/2032	700,000	700,000	88	3,695	396,305
6/25/2033	700,000	700,000	88	3,607	396,393
12/24/2033	700,000	700,000	88	3,519	396,481
6/23/2034	700,000	700,000	88	3,431	396,569
12/22/2034	700,000	700,000	88	3,343	396,657
6/21/2035	700,000	700,000	88	3,255	396,745
12/20/2035	700,000	700,000	88	3,167	396,833
6/18/2036	700,000	700,000	88	3,079	396,921
12/17/2036	700,000	700,000	88	2,991	397,009
6/16/2037	700,000	700,000	88	2,903	397,097
12/15/2037	700,000	700,000	88	2,815	397,185
6/14/2038	700,000	700,000	88	2,727	397,273
12/13/2038	700,000	700,000	88	2,639	397,361
6/12/2039	700,000	700,000	88	2,552	397,448
12/11/2039	700,000	700,000	88	2,464	397,536
6/9/2040	700,000	700,000	88	2,376	397,624
12/8/2040	700,000	700,000	88	2,288	397,712
6/7/2041	700,000	700,000	88	2,200	397,800
12/6/2041	700,000	700,000	88	2,112	397,888
6/5/2042	700,000	700,000	88	2,024	397,976
12/4/2042	700,000	700,000	88	1,936	398,064
6/3/2043	700,000	700,000	88	1,848	398,152
12/2/2043	700,000	700,000	88	1,760	398,240
5/31/2044	700,000	700,000	88	1,672	398,328
11/29/2044	700,000	700,000	88	1,584	398,416
5/29/2045	700,000	700,000	88	1,496	398,504
11/27/2045	700,000	700,000	88	1,408	398,592
5/27/2046	700,000	700,000	88	1,320	398,680
11/25/2046	700,000	700,000	88	1,232	398,768
5/25/2047	700,000	700,000	88	1,144	398,856
11/23/2047	700,000	700,000	88	1,056	398,944
5/22/2048	700,000	700,000	88	968	399,032
11/20/2048	700,000	700,000	88	880	399,120
5/20/2049	700,000	700,000	88	792	399,208
11/18/2049	700,000	700,000	88	704	399,296
5/18/2050	700,000	700,000	88	616	399,384
11/16/2050	700,000	700,000	88	528	399,472
5/16/2051	700,000	700,000	88	440	399,560
11/14/2051	700,000	700,000	88	352	399,648
5/13/2052	700,000	700,000	88	264	399,736
11/11/2052	700,000	700,000	88	176	399,824
5/11/2053	700,000	700,000	88	88	399,912
11/9/2053	700,000	700,000	88	0	400,000

[Gaurav_Dhama_]

You will need to use iterative macro only for the calculation piece, rest looks more or less static. So you can update these values using formula tool or append. Attached is the iterative macro that runs the loop. There is a column that is Iter, use that to construct your dates, if it is annual, each Iter will be a year, if it is bi-annual, then each Iter is 6 months, or 0.5 years and so on.

Output that is connected to Browse, is the output that gives the whole result. 

[Fedahh]

Thanks Gaurav!

 i tried the macro on real data but acutely no calculations happens!

also i need to create new column that generate date for  payment period as i explain above, if its bi-annual so there's 2 times payment a year.

hope its clear :)

[Gaurav_Dhama_]

That is because the changes in macro were not done right, attaching the updated macro, just map the columns to the macro input and it should run fine.

Result

[Gaurav_Dhama_]

And here is the workflow with dates.