Calculating Interest Accrued

[mikeymay]

I am building a loan calculator where the loan can be repaid with ad-hoc payments at any point within the loan term.

E.g.
A loan of 617,889.95
The interest rate of 0.872% per month
Interest is not compounding and is added to the loan at the point of the loan being repaid

The loan is issued on 16th May

A repayment is made on 7th Jun of 175,684.00

Whilst the interest is not compounding, when an ad-hoc repayment is made during the term, the interest accrued on the amount repaid is paid from the ad-hoc repayment.

So in the above example, the loan balance does not reduce by the amount of the repayment as an element of the repayment is paying the interest accrued on the capital amount of the repayment.

Using Goal Seek will return the capital and interest amounts of the gross amount but I need to use formulas to perform the goal see and I'm not sure exactly how to do this.

A simplified version of what I need to do is below
A 4 month loan of 100,000
Interest rate of 1.25% a month
Interest will be added to the loan at the end of month 4
At the end of month 1 a 20,000 payment is made

Using Goal Seek, the capital and interest elements of the payment are calculated as
Capital 19,753.09
Interest 246.91

19,753.09 x 1.25% = 246.91

Therefore, the loan balance is reduced to 100,000 - 19,753.09 = 80,246.91

Interest for the month is 100,000 x 1.25% = 1,250
In the above, 246.91 of the 1,250 has been paid and future monthly interest is based on the balance of 80,246.91

Form month 2-4, the interest is 80,246.91 x 1.25%.

Basically, I need a formula/formulae to establish work out the 19,753.09 capital amount of the repayment and then I can simply multiply that by 1.25% to return the interest element.


TIA

 

[BadgerRoad]

It is not clear to me how you are using Goal Seek to calculate the capital amount. Unless I'm missing something or my thought process is totally off, it seems that the capital amount could be any amount up to the payment amount. Can you further explain how you are using Goal Seek to arrive at that amount?

 

[mikeymay]

It is not clear to me how you are using Goal Seek to calculate the capital amount. Unless I'm missing something or my thought process is totally off, it seems that the capital amount could be any amount up to the payment amount. Can you further explain how you are using Goal Seek to arrive at that amount?

I Managed to establish some formulae to work out what I needed to do. A bit complicated but returns exactly what what the Goal Seek returned and with some further validation it works.