Help calculating Loans, Interests and Principals

[tzvetkov_b87]

Hello community!

I'm a pretty decent developer, however I have come across a problem that has left me scratching my head.

I have two tables:

Loans:

LoanID | Amount loaned | InterestRate | DurationInWeeks | Date signed

Payments
LoanID | PaymentDate | Amount

The Interest Rate is MONTHLY.

These are the questions I'm facing:
when did customer break even/pay back their principal

when did they pay back double their principal

what % of principal has been repaid on a given date

For some reason, I just can't see the logic how to calculate it. I want to be able to select "jan 2024", and see that a certain loan at that point had only £x.xx paid, and by then had accrued £y.yy interest.

There is no end date, so the loan only ends once it's fully paid off.

Need to also add a status that it's been fully paid off, but can only do that once I've been able to calculate the interest accured taking into account payments made.

Thank you very much for the help!

My challenge is....I need to take into account the payments being made.
For example, if I have a loan of £1,000 take out on 01/01/2024 at 10% monthly.

There is a default charge of 10%, so even if you repay the loan 5mins later, you will need to pay back £1,100.

Now the payments can be made at any time.

Any help would be welcomed!

[Anonymous]

Hi, @tzvetkov_b87 

Thanks for Walt1010's reply. It's not clear what you're ultimately trying to accomplish. If someone takes out a loan of $1,000 on 1/5/2024 at an interest rate of 0.1 and a DurationInWeeks of 4 weeks, what should be the minimum weekly payment and what is the additional interest incurred if the payment is late the following week. I think you need to provide some tabular data without sensitive information to express all the possibilities to help me better understand your needs. Feel free to help you.

Best Regards,
Yang
Community Support Team

[tzvetkov_b87]

Hi Yang,

You are correct. After a lot of fiddling about, and simply trying to come up with the logic....I figured out its impossible since I was not given clear instructions on how the loan interests are applied.

I have gone back to the client to ask for further clarifications.

Thank you though for responding 🙂

[Walt1010]

I 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_b87]

Hi 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.