I am trying to produce a report in excel which will provide analysis on the drivers behind the overall change in weighted rate from one month to the next month.
This is for a bank portfolio so there are tens of thousands of records, however the math is the same regardless of scale. I have calculated the weighted average weight for the portfolio as of certain date and have also calcuated the weighted average rate for the portfolio for a separate date. At this point I am not concerned with changes in the interest rate, only balance changes. There is of course a difference that can then be calculated between the two monthly weighted rates. Once again holding the interest rate constant for now, the change in the weighted rate is a result of changes to the individual balances which occurs via loan payments, loan advances, etc. What I am trying to do is to provide some detail behind how the wieghted average moved from the initial date to the later date. For example if the overal weighted rate went down by .25%, how was that number derived from the payments and advances that occurred between those two dates. For example, the -0.25% in net movement from one month to the next could be from a negative impact of say -0.45% from loan payments and a positive impact of 0.20% from loan advances.
I am have not been able to determine the math to get at the -0.45% and 0.20% figures as provided in the examples above.
I have a sample spreadsheet that I have built to try and work out the math on just a few items, if that woudl help.