Loan amortization calculation for several years

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

0

0

0

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

0

0

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

Loan Calculation.xlsx

Aguisande

Hi @Aqureshi025 

I think a Batch macro IS the way to go.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/ta-p/488165

Since you are trying to avoid macros, maybe filtering each year individually and then union all, may give you what you need.