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

Loan Number	Subject Address	Loan Amount	Interest Rate	Funding Date	Closing Date	Interest Paid-to	Daily Interest Rate
111223a	123 ABC Street	500,000.00	12.00%	09/01/19	09/01/19	10/02/19	166.67
111223b	123 ABC Street	100,000.00	12.00%	09/25/19	10/01/19	10/02/19	33.33
111223c	123 ABC Street	300,000.00	12.00%	10/25/19	11/01/19	11/01/19	100.00

<tbody>
</tbody>

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.