Weighted Average Loan Payment Formula

[gjeberly]

Hi everyone,

I'm attempting to create a formula that calculates a "paid-to date" for a loan that has multiple principal balances. I want to be able to plug in a payment amount and have a formula in the Interest Paid-to column determine the paid-to date by applying the payment to the loan with the oldest closing date until it is caught up to the next one.

For example, if a payment of $5,200 was made in October, the paid-to dates would move forward as follows:

111223a: 10/02/19 ($5,166.67/$166.67 = 31 days)
111223b: 10/02/19 ($33.33/$33.33 = 1 day)
111223c: 11/01/19
[TABLE="width: 812"]
<tbody>[TR]
[TD]Loan Number[/TD]
[TD]Subject Address[/TD]
[TD]Loan Amount[/TD]
[TD] Interest Rate[/TD]
[TD]Funding Date[/TD]
[TD]Closing Date[/TD]
[TD]Interest Paid-to[/TD]
[TD]Daily Interest Rate[/TD]
[/TR]
[TR]
[TD]111223a[/TD]
[TD]123 ABC Street[/TD]
[TD] 500,000.00[/TD]
[TD]12.00%[/TD]
[TD]09/01/19[/TD]
[TD]09/01/19[/TD]
[TD]10/02/19 [/TD]
[TD] 166.67[/TD]
[/TR]
[TR]
[TD]111223b[/TD]
[TD]123 ABC Street[/TD]
[TD] 100,000.00[/TD]
[TD]12.00%[/TD]
[TD]09/25/19[/TD]
[TD]10/01/19[/TD]
[TD]10/02/19[/TD]
[TD] 33.33[/TD]
[/TR]
[TR]
[TD]111223c[/TD]
[TD]123 ABC Street[/TD]
[TD] 300,000.00[/TD]
[TD]12.00%[/TD]
[TD]10/25/19[/TD]
[TD]11/01/19[/TD]
[TD]11/01/19[/TD]
[TD] 100.00[/TD]
[/TR]
</tbody>[/TABLE]

My initial thought was to figure out a weighted average system to apply the daily interest rate weighted by the closing date using SUMPRODUCT, however I can't seem to work anything out. Ultimately I need the formula to turn the payment into a number of days based on the daily interest rate, and keep the paid-to dates even for all three principal balances once it passes the closing date for each.

Any input would be appreciated, thank you!

 

[gjeberly]

If there's anything I can do to help clarify feel free to ask.