Forum Discussion
Helper IHelp calculating Loans, Interests and Principals
Helper VI haven't worked with loan repoayments before but I have a bit of a background in financial maths. My first guess would be that you need to get hold of a text explaining the basic maths of the loan amortisation you're dealing with, or that in its general formulation exposes you to the type of information you will need. For example if the loan interest is calculated daily and compounded monthly, the oriingal term of the loans etc. Here is an example of a very basic explanation Financial Series - Loan Repayments (1 of 3: Unpacking the question) - YouTube. Its easy to mock up in excel. So for each loan, for each day, you will calculate the interest charge for that day (note there are different interest rate quoting conventions), and add it to the principal, and subtract any payment, plus in your case, the default charge. In practise interest is often only calculated on working days, and so the calc on the first day of the working week would cover the days of the weekend as well. Similiarly for public holidays. On the date that the principal's value goes negative, the loan is effectively repaid. I would think this would be a great use of a good date table, as a number of the Power BI time intelligence functions would eliminate some of the drudgery.
tzvetkov_b87Hi Walk, thanks for the reply.
For this purpose, I'm just assuming interest is daily (regardelss of weekends/public holidays/ etc..)
In Excel it would be easy to accomplish. I need to accomplish it in Power BI somehow.
I already have a date table in the model.My struggle is with the logic. I have one line for the loan, and then in the other table multiple lines for the payments against a loan.
However I cannot think of a way to manage the daily accrued interest on the loan so that I can quickly see at any point in time, how much has been accrued. Seeing how much has been paid off is easy - I just look at the Payment table.
Would creating another table called "Interest Accrued" work. And if so, what would the logic behind the table be?
- Walt1010
Ah ok. I think you would probably want a table to contain your key loan information, and then another table where for every day (and for every loan), you would calculate the remaining principal, by calculating the amount of interest incurred and payment received, and adding/subtracting that. I guess you know how to do the calc. Idenally you would have the interest rate loaded as a parameter. So eventually for each loan you would have multiple rows, one of each day, with the loan reference and the balance. This table would then have amany to one relationship with your loan reference table.