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".
