Automating Solver, long data structure

[PassivRegression]

Hi everyone,

i have a dataset containing funds, their monthly returns and cashflows. It's a long data structure, so i have multiple rows for each fund. Then i have to solve an Equation with solver for each fund. I want to calculate an internal rate of return using the formla:

AssetatStart*(1+r)^T + Sum(cashflow*(1+r)^T-t) = AssetatEnd
T is the amount of periods for each fund, t is the time (the month).

Im able to do this (calculate r) for 1 fund, but im unable to automate it in VBA to run for all. The solutions i found on this forum all solved individually for each row, but i need to solve for each id (meaning ech fund containing multiple rows). In Addition to that, each fund has a different amount of rows, which makes it even more complicated (at least for me ).


[TABLE="width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]cash flow[/TD]
[TD]Assets[/TD]
[TD]time[/TD]
[TD]Asset at Start[/TD]
[TD]Asset at End[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30[/TD]
[TD]150[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]-10[/TD]
[TD]128[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-20[/TD]
[TD]80[/TD]
[TD]1[/TD]
[TD]80[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]95[/TD]
[TD]2[/TD]
[TD]80[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]50[/TD]
[TD]120[/TD]
[TD]1[/TD]
[TD]120[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-60[/TD]
[TD]110[/TD]
[TD]2[/TD]
[TD]120[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD]160[/TD]
[TD]3[/TD]
[TD]120[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30[/TD]
[TD]150[/TD]
[TD]4[/TD]
[TD]120[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-5[/TD]
[TD]..[/TD]
[TD]1[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-10[/TD]
[TD]..[/TD]
[TD]2[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-20[/TD]
[TD]..[/TD]
[TD]3[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]30[/TD]
[TD]..[/TD]
[TD]1[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-10[/TD]
[TD]..[/TD]
[TD]2[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your time.

Best regards,

PassivRegression

 

[BlueHornet]

Welcome to the board.

I don't have a full answer worked out, but it seems to me that the first order of business is to obtain a T value for each ID.

You can do that quite easily with another column, in which you include the formula (assuming that the ID values are in column A:A):
=MAXIFS( D:D, A:A, A2)

That will look at all of the D:D values (t) to find the max (t) for each ID.

Do you still want help running the Solver in VBA once you have the varying T values? I think I have some code for that that I can look up from a project I ran once, and if you need that then I'll go and get it.

 

[BlueHornet]

Found it and modified it for this application:

1. Assuming your data are laid out starting with Column A:A, and
2. Adding the formula in a new column H:H to show the T values for each row (unvarying within each ID), and
3. Adding your formula into column I:I, with
4. Blank column G:G to contain the various r values:

Sub FindRValue()


Sheets("Sheet2").Activate


For RowCount = 2 To 10


    SetRValue = Range("I" & RowCount).Address
    Range(SetRValue).Select
    GoalSought = Range("F" & RowCount).Value


    Range(SetRValue).GoalSeek Goal:=GoalSought, ChangingCell:=Range("G" & RowCount)


Next RowCount
    
    Range("G2").Select


End Sub

 

[PassivRegression]

Thank you so much for your Answer, you already helped me a lot. I managed to get a Column with the T for every row, same for every ID.
Using your code, i get results but i think they might be wrong, also they differ for every row. I think the problem is how i typed in my formula (your step 3)
I typed:

E1*(1+G1)^H1+SUM(B1:B320000*((1+G1)^H1-D1))-F1
With E = Assets at Start G = empty for R B = cashflows H = T D = t
and just dragged it down.

heres the original formula.

I should just use the sum from the start of the ID to the T row, then start a new formula with a new sum for each ID, but dont really know how to do that.

 

[BlueHornet]

Ah, I see one of the problems. In your formula you have summed the cash flow from every line in the table ... on each line. Is that intentional? It seems that if you're talking about different investments or assets (whatever), then that's not quite right, is it? (I had overlooked that in my testing; all I looked at was the cash flow value on each line.)

It should be an easy thing to correct, if you want to sum the cash flows from each separate ID onto each line. That is, if you want all of the cash flow values from ID = 1 to appear on each line where ID = 1 then you could accomplish that with a SUMPRODUCT formula: + sumproduct(( b1:b320000) * ( a1:a320000 = a1) ... for example.

See how this works for the formula, in that case:
=E2 * ( 1 + G2) ^ H2 + SUMPRODUCT(( $B$2:$B$320000) * ( $A$2:$A$320000 = A2)) *(( 1 + G2) ^ H2 - D2) - F2

 

[PassivRegression]

I used your formula and I think I'm almost there. The way you formulated the formula, it sums every row for each ID and then multiplies with my factor (1+R)^T-t. What I need is that it multiplies with that within the sum. So I have (Cashflow1 * (1+R)^T-t + Cashflow2 * (1+R)^T-t) ....
This must be the same in each row for each ID as well! So it computes just one R for each ID

 

[BlueHornet]

Try this formula instead, then:
=E2 * ( 1 + G2) ^ H2 + SUMPRODUCT(( $B$2:$B$320000) * ( $A$2:$A$320000 = A2) * (( 1 + G2) ^ ( $H$2:$H$320000) - $D$2:$D$320000)) - F2

Unfortunately, I don't know finance well enough to know whether the values that the formula produces (when run with the macro) are accurate or not, but at least it settles to a single value of R for each ID.

Incidentally, when the formula and macro are working properly, you can comment out the "Range(SetRValue).Select" statement. I only had that included so that I could watch the macro step through the values it was changing as it did it. There's no need to retain that capability, as it only slows processing slightly as the cell is selected. (For additional speed, if there will be a lot of rows to calculate, you can include Application.ScreenUpdating = False at the start of the processing loop, and Application.ScreenUpdating = True at the end.)