The context for this question is that say I have 200 dollars locked up that will be paid back to me each month for a varying amount of money with the final payment taking pace on the 4th year. I'm NOT trying to determine the present value of the 200 dollars BUT rather the weighted average "lockup period".
i.e.
1/31/2023 pay back $1 dollar 2/28/2023 pay back $0.5 ...... 1/31/2027 pay back remaining $0.3
How do I calculate the weighted average of the "lock-up period" because intuitively since the money is being paid back to me, the lock up time period is not 4 years but rather should be weighted by the $ amount being paid back.
Should it be simply be: =sumproduct(Every date: Every $amount)/ sum(Every $amount)?
I think what you are asking about is Weighted Average Life (WAL). See this link: https://www.investopedia.com/terms/w/weightedaveragelife.asp
The WAL is, loosely, the average time to maturity per unit money.
This being the case, your calculation is correct.
If you have the repayment amounts in percentages, you can simplify by just doing a SUMPRODUCT() over the dates and repayment percentages. (Interest is ignored.)
A couple of caveats:
Beware using Excel dates in calculations like this. In some cases, it is safer to convert to a time span in days (from a start date) before performing any calculations.
Be careful of UK/US date format confusion, especially if sharing this sheet internationally.