Loan calculation sheet for several loans

[Aqureshi025]

Hi All

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 - See attached file

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

[jdunkerley79]

I suggest something like:

- Use a generate rows to make the set of year ends for each row

- You can then compute the days in each year like:

DateTimeDiff(
Min([Maturity Date of loan],[YearEnd]),
Max([Start date of loan],DateTimeTrim([YearEnd],"year")),
"days")

- After that you can compute the interest for that year:

Round([Nominal Rate]/100*[Amount]*[Days]/ToNumber(Right([Date Basis],3)),0.01)

(I rounded to nearest 0.01 but change as you need)

- Finally cross tab to get the correct layout

Sample attached

[Aqureshi025]

Thanks. But in your workflow the loan in which they are matured no interest is calculated for that year. for e.g loan A is matured in 29/4/2020 so I would expect some interest for 4 months of 2020 aswell

[jdunkerley79]

Sorry there was a bug in the generate rows tool.

Attached fixed version.