Excel Formula help please

  Jackcoms 16:17 19 Feb 2006

I'm looking for an Excel formula which will calculate the 'yield' or 'profit margin' as a percentage of my original investment amount.

To explain:

Say I invested £1,000 on 1 January 2005 and today is 19 Feb 2006 - which means my £1,000 has been invested for 414 days.

I also know that the original £1,000 is now worth £1,200.

I need a formula to calculate the average annual yield which the £200 increase represents over the 414 days of investment.

I appreciate that in my example the original investment has increased by 20% but I need to know what that represents as an annual yield given that my money has been invested for more than 12 months?

  welshwizard712 16:51 19 Feb 2006

Right lol, this is touch confusing but from what i'm reading you want to find out the Average Yield you have recieved in a month/day?

In that case you could use simple Mathematic Formulae.

Just divide the Amount you have by the Original Amount invested , multiply by 100 and minus a hundred that should give you the Percentage increase?


Im sorry if I have not understood this right and It seems like I am picking at Math Skills.



  keith-236785 16:56 19 Feb 2006

surely this is a simple maths equation ie 200/414 * 365 to find the yearly yeald, or am i missing the point here.

if this is what you require, then a formula such as

Ammount input 1000
Total for period 1200

Yeald 200 (formula) =C4-C3
Period 414
Yearly yeald 176.33 (formula) =(C6/C7)*365

that looks a bit messy on the forum page but works in excel.

wait a while and VoG should come along and put me to shame LoL.....he is a star at excel.

good luck, at least this will bump you up

  Jackcoms 17:21 19 Feb 2006

Yes - I'm looking for a formula to calculate my average yearly/annual yield.

I don't think my original question explained it very clearly! ;-((

  VoG II 17:58 19 Feb 2006

I don't think I can improve on that.

  Jackcoms 18:14 19 Feb 2006

Thanks for your help. That seems to have solved it

  Simsy 21:30 19 Feb 2006

but it's possible an element has been missed here...

Without knowing the circumstances that lead to the figure of 414 days, it's possible that there may be an element of compund interest that has been ommitted...

I'm suppossing that the account has been closed on day 414, (or else how would the amount of interest be known?)...

On the day account is closed it is probable that interest due up until that date, (since interest was last added), is also included. This would mean that interest has been added twice in the 414 days, the first time on (suppose), day 365, then again on day 414.

If this is so then the interest paid on day 414 included interest on the first lot of interest...

That would mean that the interest worked out as indicated above, 200/414 * 365 , is not quite correct.

To find the absoulute value one would need to know how much, and when, with regard to day 1, the interest was added.

I'm not sure I can easily work out the answer! It's just that the element I suggest seems to be missing from previous calculations!

With apologies if I'm barking up the wrong tree!



  VoG II 21:43 19 Feb 2006

You are right, of course, but I had the impression that Jackcoms was after a simple RoI calculation.

To take account of the timing of interest payments etc. (let alone variable interest rates) would, I think, necessitate the use of Excel's financial functions. I freely confess to knowing little about these except that they conform to American accounting practices that may be at variance with those used in the UK.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Microsoft Surface Book 2 hands-on review – bigger and 5x faster

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?