2

My retirement fund [Australian Superannuation fund] can be checked at any time. The fund invests in shares, which can go up - or down. Money is going IN regularly [monthly].

At any given time, how do I calculate the interest rate? I think this becomes a Compound interest calculation, but I'm not sure if I should specify calculating interest daily, weekly, or other.

I've found a few calculators for determining a final value based on a compound interest rate: I'm still looking for how to calculate the rate when given the final value.

Edit: I found formulas for Compound Interest, and Regular Deposits, here. For deposits, the site states that interest "can't be solved for algebraically, and must be found numerically." That makes for fun.

|improve this question
  • It's correct - usually Newton's Method is used to approximate the rate of return to within 0.0001%. The XIRR function in excel is exactly what you're looking for. – David Rice Jun 22 '15 at 19:29
3

It's not compound interest. It is internal rate of return. If you have access to Excel look up the XIRR built-in function.

|improve this answer
  • Thanks, I looked that up. It expects the first value to be negative, so feeding it a list of balances [and dates] just gave an error. – Alan Campbell Jun 22 '15 at 17:58
  • 1
    @AlanCampbell Did you read the XIRR documentation? It's supposed to work that way. The amounts you contribute to your account would be entered as the negative cash flows; negative implying => money out of your pocket at that point in time. Then, you would put the current account balance as the final, positive value with today's date; positive implying => money back into your pocket. Because it works this way, it can also correctly account for premature fund withdrawals, not just deposits. – Chris W. Rea Jun 22 '15 at 18:36
  • 1
    @AlanCampbell ... so, you can't just give XIRR a list of balances. You need to give it a list of cash flows; i.e. your deposits/contributions. The only "balance" that needs to be in the list of cash flows passed to XIRR is the last one, being an imaginary complete withdrawal of your current balance as of the current date. If you would calculate your return over a time period other than since inception (which would require all deposit data), then the first negative entry would model the balance at the starting date for the time period. – Chris W. Rea Jun 22 '15 at 18:40
  • The help provided by Microsoft on XIRR is - not very helpful. So I looked up several web pages for instructions. In the end, I created a row for the negative deposit, each [future] negative payment, and today's positive balance. XIRR() is on the same row as TODAY(). When sorted by date, the positive balance is last - as required. After that, a mix of absolute and relative cell addresses did the trick. Thanks! – Alan Campbell Jun 26 '15 at 23:59
0

If you didn't have deposits, then the growth rate is simply ((p1/p0)^(1/t))-1, where p0 is the initial balance, p1 is the current balance, and t is the number of periods.

For example, suppose you started the account with $100,000 in 2000. It's now 2015 -- 15 years later -- and the balance is $240,000. So the growth is:

(240000/100000)^(1/15)-1
=.06, or 6%

If you're making regular deposits, especially if you're making deposits of unequal amounts, the problem becomes much more complex. The easiest thing to do is probably to create a spreadsheet. Make a column for principle, a column for deposit amount, and a column for percent growth that period. Assuming A is principle and B is deposit, then the formula for growth C is =((A2-A1-B1)/A1-1)*100. Copy this formula down the column and Excel should automatically adjust the row numbers. Assuming you put one row for each month you can see the growth (or loss) month by month. You can get a general idea of what your overall growth rate has been by taking the average of the monthly amounts, but this would not be a truly accurate measure of your total growth because presumably (hopefully) you have more money in the later months than the earlier months. But it would be a good measure of how your investments are doing.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.