Complex real-world annuity-type calculation problem?

Question

I'm trying to calculate APY on an investment portfolio, and all of the formulas I am familiar with deal with constant contributions and intervals.

Here is a simplified version of a sample portfolio:

Contribution A: $3000 made 1157 days ago

Contribution B: $4000 made 786 days ago

Contribution C: $4000 made 426 days ago

Current Portfolio Value: $21,388.30

Another way to read the problem:

21388.30 = 3000(1+r)^(1157/365) + 4000(1+r)^(786/365) + 4000(1+r)^(426/365)

I'd like to turn this into a formula to find 'r'

Someone tackled this with a TI89 a couple of months ago, and got the correct result (35.873%), but I'm more interested in the formula; so the result can be found as the number of contributions increases, and the value of the portfolio fluctuates. Right now, I've got the formula above in excel, and plug random values into r until I get the right answer, but there should be a way to have it find 'r' for me, since I have all the other variables.

What formula can I use to determine the missing variable (the average annual rate of return)? I'm fully expecting this to require the use of logarithms.

Thanks to anyone who tries to tackle this!

Answer 1

It is impossible to solve the equation algebraically for r.

In Excel, one can use the NPV and IRR functions to solve these kind of problems. Or you can write a small macro of your own.

Another way, is to calculate the r for each investment and then add them  weighted for the  time and the invested capital. This doesn't give the correct answer but is close enough, when the r values are small.