Loan calculation sheet for several loans
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Input
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
