Calculating the weighted average profit rate on a monthly basis for several deposits

[mromar88]

Fellow Excelers,


My problem has vexed me for a while, so I'm hoping its not a simple one. I have several investment deposits, each one has a different currency, interest rate, start date, and maturity. I'm trying to calculate the monthly average profit rate for all investments in the same currency. So, what I've been trying to do for a while, and the part in which I need your help in, is come up with a formula to do the following:


Calculate the monthly profit amount for all investment deposit: for each investment deposits, I use this formula: Daily Profit*MAX(0,MIN(Maturity Date,Next Month)-MAX(Start Date,Current Month))
what this formula does is check whether the investment deposit has any days within the current month, if so, it calculates the days in the month and multiply it by the daily profit.

This is how the table that I'm trying to fill out looks like (not the whole table): Imgur: The most awesome images on the Internet



Here is a snapshot of some of the investment deposits that I have: Imgur: The most awesome images on the Internet


So, just to make things clearer, let's say I want to calculate the average profit rate for USD investments in February, 2016. The formula will check the table and see that investments number 3 to 9 have profit days in February, 2016. Then, it will multiply each investment daily profit by the number of days for the investment in February and sum them up. Finally, divide them by the sum of the investment deposits in question.
I know its not an easy task and I hope you guys can come up with a solution (hopefully not through a VBA code). I tried using SUMPRODUCT but the formula i mentioned above didn't work because of the MAX and MIN.

I hope I've clearly stated what I'm hoping for. If not, please ask away and I'll be happy to make things clearer.

Thanks!

 

[DRSteele]

If you could please post some sample data together with desired outcome I'd be glad to help you with your currency hedges. You can download a HTML add-in using the link in my signature below.