Investment Portfolio with variable GICs interest rate - Find Weighted Average Interest Rate

Hello,

Any help or lead is appreciated:

My company has some investments in bonds/GIC.

I have 2 tables: Bond Table and Transaction Table.

The Bond Table is built as follows:

Bond Name // Start Date // End Date // Interest Rate

Bond A // 2023-01-01 // 2023-06-30 // 0.045

Bond A // 2023-07-01 // 2023-12-31 // 0.049

Bond A // 2024-01-01 // 2024-08-15 // 0.035

Bond B // 2022-01-01 // 2024-06-30 // 0.08

The Transaction Table is built as follows:

Transaction Date // Bond Name (Description) // Operations // Amount

2023-01-01 // Bond A // Buy // (1,000)

2023-05-01 // Bond A // Buy // (5,000)

2023-06-01 // Bond A // Sell // 888

2023-11-01 // Bond A // Buy // (499)

2022-01-01 // Bond B // Buy // (1,000)

(The cardinality between Bond and Transaction Table = many to many based on the relationship of Bond Name and Bond Name (Description))

I was able to find the daily capital invested, which means how many $ is currently invested at a certain date. (Example, as of 2023-01-01, I have 2,000$ invested, but as of 2023-06-01, I have 6,112$ invested. Between 2023-01-01 & 2023-04-30, every day, I have 2,000$ in invested capital.)

Right now, My objective is to find the weighted average daily interest rate (Example, as of 2023-01-01, my W.A Interest = 1,000$*0.045 (Bond A) + 1,000$*0.08 (Bond B) / Invested Capital of 2,000$ = 6.25% // but on 2023-07-01, my W.A Interest = 5,112$*0.049 + 1,000$*0.08 (Bond B) / Invested Capital of 6,112$ = 5.407%)

Any leads on how can I fund the daily W.A Interest Rate?

It's a mix of date scaffold // event in progress, but I have multiple variables for each day (Bond Name, Bond Amount & Interest Rate)...and the fact that my cardinality is "Many to Many".