How to calculate annual return

H

henry

Registered User
Messages
70
Hello,

I need some help with my maths.

I starting putting € 250 per month into a fund. I have been doing this for 13 months - € 3,250 in total. The fund is now worth € 4,100. I am trying to work out the annualised return.
I realise the fund has grown by 26% in simple terms but this does not take into account the timing of the monthly contributions.
Any answers ?
 
Could you work it backwards using this calculator - by tweaking the interest rate until you hit the current value of the fund? It's giving me c. 47% as the annualised return!
 
Thanks, could do that alright but there must be some method of calculating it exactly. Its curiosity more than anything.
 
Do you know how to use the 'Goal Seek' function of MS Excel?

It would be very simple to set up an equation and use the goal seek to solve for this - the answer is 56% (assuming contributions paid monthly in arrears).
 
CapitalCCC said:
It would be very simple to set up an equation and use the goal seek to solve for this - the answer is 56% (assuming contributions paid monthly in arrears).
Click to expand...
Using the XIRR function in excel I got a value of 47.0115%.

I'm open to correction as I'm basing my result on an unseen calculation (dependant on excel) whereas CapitalCCC is using a visible calculation based upon personal input. I would have assumed that the two calculations would have provided the same result.

For the sake of clarity, should the op wish to have a go themselves...

Date Payment Note
01-Jan-06 -250 Initial investment
01-Feb-06 -250 Monthly investment
01-Mar-06 -250 Monthly investment
01-Apr-06 -250 Monthly investment
01-May-06 -250 Monthly investment
01-Jun-06 -250 Monthly investment
01-Jul-06 -250 Monthly investment
01-Aug-06 -250 Monthly investment
01-Sep-06 -250 Monthly investment
01-Oct-06 -250 Monthly investment
01-Nov-06 -250 Monthly investment
01-Dec-06 -250 Monthly investment
01-Jan-07 -250 Monthly investment
31-Jan-07 4100 Investment return
850 Profit
47.01152211% IRR

Profit = Sum of payment column
IRR = "=XIRR(Payment Column,Date Column,Guess)" - I set the guess as 0.45 but it provided the same answer when set to random figures miles from the true value (e.g. 0.045)
 
Well I get 47% if assume contributions paid monthly in advance, so that is what the excel function XIRR is doing for you Satanta, I guess only the OP knows which one of these is the actual result then!
 
CapitalCCC said:
Well I get 47% if assume contributions paid monthly in advance, so that is what the excel function XIRR is doing for you Satanta, I guess only the OP knows which one of these is the actual result then!
Click to expand...
I presume that the function would provide the correct answer if the investment dates were input specific to the OPs case. Would any other change be needed to clarify the in advance / in arrears scenario for the OP or is this simply based on my inputting the date as being in advance?


Note: the XIRR calculation isn't a standard calc. To use it you may have to go to "Tools", "Add ins", "Analysis ToolPak" in Excel to load the calculation.
 
Thanks again.

Monthly in advance would be the correct position so I guess 47% is the answer I am looking for. And now I have the methodology.
 
The only other change could be an allowance for any lag between the last contribution and the actual date of the fund value that the OP is using.
 
Well, the fund is still going - I am still paying in € 250 per month. So the time lag is not a big issue yet.
 
Yes, but in the example that we have used here, we have assumed that the fund value of €4,100 is at a date exactly one month after you have paid the 13th contribution of €250.

When I calculated the return of 56% it was on the basis that the fund value was on the exact same day that the 13th contribution was paid.

In reality, the fund value is probably at a date somewhere between these two extremes!
 
You must log in or register to reply here.
Back
Top