# Thread: Weighted Average Loan Payment Formula Thanks: 0 Likes: 0

1. ## Weighted Average Loan Payment Formula

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

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!

2. ## Re: Weighted Average Loan Payment Formula

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•