Hello All,

Would greatly appreciate some help. I've currently built a loan schedule showing the flow of principal prepayments and how much interest is accrued net any prepayments. My goal is to get it all in one formula instead of a schedule. Ideally a formula that calculates the interest accrued between multiple dates, and then factoring in principal prepayments which then reduce the daily accrued interest.

This is how I have it broken out, but would love to be able to aggregate it into one formula:

Simple Scenario:
Principal: $10,000
Interest 10% (Daily interest = 10% / 360 = .03%)
Originated Date: 4/10/20
Today: 4/23/20

Interest Accrued is simply = (Principal*Rate*(today()-Originated Date)


Now we add principal prepayments:

Principal: $10,000
Interest 10% (Daily interest = 10% / 360 = .03%)
Originated Date: 4/10/20
Partial Pmt Payment 1: $1,000 on 4/14
Partial Pmt Payment 2: $2,000 on 4/17
Today: 4/23/20

Interest Interest in new scenario:
4/10 - 4/14: $10,000 * .03% * 4 days = $11.11
4/15 - 4/17: $9,000 * .03% * 2 days = $5
4/17 - 4/23: $7,000 * .03% * 6 days = $11.67
Total Interest = $27.78
Ending Principal Balance = $7,000
Payoff Amount = $7,027.78

I would like to have one formula for 20+ loans that vary between multiple principal prepayments (or not). Been very tough to be able create a formula that factors in what the int payment is, and if it is overpaid, to recalcuate the principal and interest accrued.

Appreciate the help!