Excel Formula help please

  Jackcoms 16:17 19 Feb 06
Locked

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 06

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?

=((CELL1/CELL2)*100))-100

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

Regards

Matt

  keith-236785 16:56 19 Feb 06

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 06

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 06

I don't think I can improve on that.

  Jackcoms 18:14 19 Feb 06

Thanks for your help. That seems to have solved it

  Simsy 21:30 19 Feb 06

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!

Regards,

Simsy

  VoG II 21:43 19 Feb 06

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.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5