IRR Calculator feeding from cashbook

[a.mack123]

Hi all,

I am in the process of trying to build an IRR calculator, that automatically sources its data from a detailed transaction statement from a cashbook. I have completed most of the coding but there are a few more hurdles that my current Excel skills do not conquer.

The first is that it is extremely slow in changing any variable. This is believe, is becasue of all the many sumproduct formulae. However I do not know another way to acheive what I have without it. I am open to all suggestions if it will fasten the calculations.

What I want the calculator to eventually do is calculate the IRR between any two months, this i am no sure how to achieve.


I will explain a little about how the coding is arranged so that it will be easier for anyone helping to navigate their way around the worksheet.

Firstly the employee will enter the inception date of the SMSF in cell C3
This will cause column L from row 12 onwards to be automatically populated with all the months from the inception date until the current month. (The formula used isnt perfect, depending on the date you put into C3, it sometimes skips months and also sometimes doesn't include the current month. Any help on this would be fantastic.

Secondly hidden columns J & K populated based on the data ouput in column L

Column J will show the first date of the month displayed in respective cell in column L and Column K will show the last date. This is the date range that all the respective sumproducts work from in the same row.

Thirdly in the Contribution, Withdrawl & Tax section there are rows for each member. The only way i knew how to discern between the cashbook entries was to include another array in the SUMPRODUCT formula that inlcudes each members GL code, which are entered manually into the table (L3:O6).

In column H the monthly values of the SMSF are listed, I know this has to come into play when calculating the IRR but wasnt sure of how to work it in when one would be changin the calculation dates.

Finally the Contribution, Withdrawl, Tax & Total section is conditionally formatted to only appear if the cell does not equal zero - its easier to read when only cells with figures show.

I have attached a copy of the xls.

If anyone can help me finish this off I would be enternally greatful.

Thanks