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
<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!
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!