Diff of dates over a range, lookup, sumproduct

[redchimney]

A certain company has taken a loan in installments. Depending on the number of years that have passed since the installment was taken, the company has to pay a certain multiple on it. The loan installment that was taken first also gets paid out first. It is possible to make partial repayments. How do I formulate this in excel so that, one can just enter the loan and repayments dates and the amount outstanding gets calculated (without VBA)



E.g.

Date of installment	Amount of loan​	Repayment​
9/1/2021	$10​	​
11/15/2022	$2​	​
9/1/2023	$4​	​
12/31/2023	​	$5​
12/31/2024	$4​	​
12/31/2025	​	$6​
12/31/2026	​	$10​
12/31/2027	​	$30​
​	​	​
​	​	​
Multiple to payback	​	​
Until 1 year of installment	1.1x	​
Between 1 to 2 years	1.25x	​
Over 2 years	1.5x	​

So, on 12/31/23 the total amount due is $21.9 ($10 * 1.5 + $2 * 1.25 + $4*1.1). After receiving $5 on 12/31/23, the amount outstanding is $14.4 ($5 * 1.5 + $2 * 1.25 + $4*1.1)