PassivRegression
New Member
- Joined
- Mar 28, 2019
- Messages
- 3
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 ).
<tbody>
</tbody>
Thank you for your time.
Best regards,
PassivRegression
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 ).
id | cash flow | Assets | time | Asset at Start | Asset at End |
1 | 50 | 100 | 1 | 100 | 128 |
1 | 30 | 150 | 2 | 100 | 128 |
1 | -10 | 128 | 3 | 100 | 128 |
2 | -20 | 80 | 1 | 80 | 95 |
2 | 30 | 95 | 2 | 80 | 95 |
3 | 50 | 120 | 1 | 120 | 150 |
3 | -60 | 110 | 2 | 120 | 150 |
3 | 20 | 160 | 3 | 120 | 150 |
3 | 30 | 150 | 4 | 120 | 150 |
4 | -5 | .. | 1 | .. | .. |
4 | -10 | .. | 2 | .. | .. |
4 | -20 | .. | 3 | .. | .. |
5 | 30 | .. | 1 | .. | .. |
5 | -10 | .. | 2 | .. | .. |
<tbody>
</tbody>
Thank you for your time.
Best regards,
PassivRegression