EntropySink

Technical & Scientific => Software => Topic started by: Jake on February 23, 2015, 10:44:46 PM

Title: Excel Help
Post by: Jake on February 23, 2015, 10:44:46 PM
I want to create a 3 year pro-forma return on investment line graph with a clear break even point. Here is the data I have:

x: Annual ROI
y: Total investment

from these two values I calculate the break even point (z) with this calculation where J47 is y and J42 is x

z=INT(J47/J42) & " years  " & INT(((J47/J42)-INT(J47/J42))*12) & " months"

So, here are some real numbers

x: 292,500
y: 250,000
z: 10 months

I want to generate a graph with 36 months on the x-axis and $$$ on the y-axis. I haven't played around with this too much yet, but if anyone can help I would appreciate it!
Title: Re: Excel Help
Post by: Jake on February 23, 2015, 11:30:22 PM
I just set up a table like pictured below and then made a graph. Their seems to be an easier solution, but you guys are way smarter than me and maybe you can come up with it
Title: Re: Excel Help
Post by: JaWiB on February 23, 2015, 11:45:07 PM
I feel like I'm missing something about what you want to do, but it sounds like you already figured it out. How exactly could it be easier?
Title: Re: Excel Help
Post by: Jake on February 24, 2015, 12:00:47 AM
Basically it is a graph of a payback period on an investment along with a 3 year ROI.

Title: Re: Excel Help
Post by: kermi3 on February 26, 2015, 06:16:41 PM
I think what you're trying to do is plotting - and I'm not sure excel is the best software for that - but it looks like you have a good solution!