Loan Amortization schedule - Calculation

[Aqureshi025]

Hi Experts,

I am looking for the help to prepare a loan amortization. I have the data  in the following format

Number	Amount	Nominal Rate	Start date of loan	Maturity Date of loan	Date Basis
A	700,000,000	2	20/04/2017	29/04/2020	Actual/360
B	500,000,000	3	11/01/2018	11/01/2021	Actual/365
C	10,000,000	6	31/05/2019	30/04/2020	Actual/360
D	450,000,000	8	04/10/2019	30/09/2024	Actual/360
E	7,000,000	2	27/04/2020	28/08/2023	Actual/365

I need to calculate the interest for each year. For e.g Loan A starting in 2017, so the column came in and shows me the result from 2017 then 2018 then 2019 and till 2020 until the maturity date. Means for the 2017 the interest will start from the date of loan start and for 2020 the interest will be until the maturity date. Same for all other loans. Looking something output like this

Number	Amount	Nominal Rate	Start date of loan	Maturity Date of loan	Date Basis	Interest for 2017	Interest for 2018	Interest for 2019	Interest for 2020	Interest for 2021	Interest for 2022	Interest for 2023	Interest for 2024
A	700,000,000	2	20/04/2017	29/04/2020	Actual/360	9,916,667	14,194,444	14,194,444	4,666,667	0.00	0.00	0.00	0.00
B	500,000,000	3	11/01/2018	11/01/2021	Actual/365	0	14,547,945	15,000,000	15,041,096	452,055	0.00	0.00	0.00
C	10,000,000	6	31/05/2019	30/04/2020	Actual/360	0	0
D	450,000,000	8	04/10/2019	30/09/2024	Actual/360
E	7,000,000	2	27/04/2020	28/08/2023	Actual/365

Please suggest how to do it. I would prefer to do without any macros, just by using the standard tools

Your support will be really appreciated

Regards

[joshuaburkhow]

This looks a lot like this post here. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Loan-amortization-calculation-for-seve...

Does this help you?