Weighted Average in Annual Basis (Revised)

[wilchong]

Hello AliGW,
Because I need to attach a new Excel file in this thread, so I use a new thread to communicate with you.

I have spent some time to confirm the calculation with some notes in the spreadsheet. It is extremely challenging to compose a formula which need to calculate the length of period of outstanding balance. The length of outstanding balance period can be in irregular pattern, depending when there is additional capital flow in. For my example, there is four time capital injection on 1st Jan 2016, 1st Apr 2016, 1st Jul 2016, 1st Oct 2016, therefore, the length of each outstanding balance change is exactly one quarter, which 0.25 year. In this case, it is quite straight forward. But in some cases, the length of outstanding period can be 52 weeks in uneven distribution. Of course, the weighted average of invested capital will be equal to the outstanding balance times 1 if NO additional capital flow in during the past 4 quarters.

I have worked out the expected outcome in cell F11 to F15. And the working of the outcome also can be found in the same worksheet. The worksheet name Note will give you more explanation.

Many thanks for your appreciation.
Wilchong