# How can I make a diagram like this?

1. ## How can I make a diagram like this?

Hi, I liked this diagram a lot which I have found on a article.

I was wondering if it is possible to create such a chart in excel. Or maybe other programs were used for this purpose?
Perhaps anybody can say my if this diagram was created by excel. Thanks

Unbenannt.JPG

2. ## Re: How can I make a diagram like this?

Who knows where it was created....

but... it's pretty much just a semi-logarithmic graph.

see file

3. ## Re: How can I make a diagram like this?

Yeah it look quiet nice. But how can i calculate the data between year 1956 and 2020( For example for Harley Davidson)? If I know the value 1000 Dollars at beginning of the year 1956 and at the end date I had a value of 114000 dollar. Do I have to calculate the internal rate of return for this first?

4. ## Re: How can I make a diagram like this?

You have not told us what financial/mathematical model you intend to use for this. Your picture shows curves that have different start and end points and are concave down on a semi-log plot. The steps I would expect to go through to generate data for this:

1) Choose your financial/mathematical model. I think a simple present value - future value model (so PV(), FV(), and related functions in Excel see help file for PV() function: https://support.microsoft.com/en-us/...1-da16e8168cbd ) can be used if you have no other idea what financial model to use.
2) Come up with suitable values for the input parameters for the financial model. If you choose a simple pv model, this is mostly choosing appropriate values for rate and payment.
3) Use your financial model to calculate the future value of \$1000 at your different choices for the input parameters and at multiple years (arranged as Glenn's example shows where you have year in one column and future value in a second column).
4) Insert an XY scatter chart and format/edit different elements until you get the look you want.

It appears to me that you are currently "stuck" on steps 1 and 2 (choosing a financial model and/or coming up with input arguments for that model). I'm not sure how much we can help with those steps without more information from you. Help us understand exactly what you need to do here, and we should be able to help you program that into a spreadsheet.

5. ## Re: How can I make a diagram like this?

Unfortunately, I don't know what calculation method is beeing used to make such a graph.

Probably it should be a simple pv model. Probably I need to somehow simulate logarithmized returns from the start and end times themselves.

1.JPG

6. ## Re: How can I make a diagram like this?

I'm not sure what to recommend, then. The short and useless answer is, yes, you can create a chart like that in Excel.

If the chart's author did not specify the financial/mathematical model they used, does it matter what financial/mathematical model you choose? A simple present/future value model (using Excel's built in FV() function) seems like the easiest model to use. Enter a compounding interest rate in E1, an annual contribution/payment in E2, a starting year in E3, your initial (present) value in E4, then enter your future value function in B1 =FV(\$E\$1,A1-\$E\$3,\$E\$2,\$E\$4) and copy down to the bottom of the list. Then adjust E1 and E2 until you get the curve you want. Repeat for the other auto companies and add those data to the chart. If you don't know the financial model and it doesn't really matter what you use, then this should work.

7. ## Re: How can I make a diagram like this?

Ok thanks I have implemented this.

test.JPG

But the diagram from Glenn Kennedy, looked more like what I want to have. Unfortunately I can't upload the file here directly.

Can I edit the data to get a better result? So I mean because at the end I have a straight line. But I want to have an arc like in the photo of Glenn:
2.JPG

8. ## Re: How can I make a diagram like this?

I'm still confident it is possible, but it is still not clear to me how you would like to calculate these values. The chart title in your picture in the OP talks about investment growth, and the FV() function (a basic present/future value compound interest model) was only the first guess at a possible model. If, after trying different values for rate and pmt, you decide that a simple present/future value model doesn't have the right shape, then simply pick a model that will have the right shape. My knowledge of financial models is rather limited, so I cannot recommend anything, but once we choose a suitable financial model, then it should be a matter of entering the financial model and a suitable set of input parameters for that model.

If you really don't care at all what kind of model you use -- only that it can have a shape like Glenn's data -- I observe that I can approximate Glenn's data using a log-normal distribution. Something like =LOGNORMDIST(year-startyear+1,5.5,1)*1E6 is a reasonable approximation of Glenn's hand entered data. I have no idea what a log-normal distribution means as a financial model.

Ok thank you

10. ## Re: How can I make a diagram like this?

Hi, it's me again. It won't let me go and I tried to plot the curve with a logarithmic trend line. I like the diagram very much. I just select the start and end date and the corresponding value. Then I enter the log trendline and delete the even.

Now I have the problem that I have to create the data series manually. And I don't feel like doing this 350 times (my data size). How can I display this all at once?
1.png

11. ## Re: How can I make a diagram like this?

I get a "file already deleted" error from that link. Is the new file too large to upload directly to the forum, or is there something else preventing you from uploading the file? If it's too large, we shouldn't need all of the data for all 350 data series in order to see what you are currently trying to do and come up with a different approach that won't be so tedious.

12. ## Re: How can I make a diagram like this?

When I would upload the file I can't select this button.

1.png

13. ## Re: How can I make a diagram like this?

That paper clip icon thing has not worked for years (don't ask why, because none of us knows). There's a yellow banner at the top of the page describing the working procedure for attaching files.

14. ## Re: How can I make a diagram like this?

Ah ok. I think now it should work to upload the file. I have to select each dataset, like my picture before. Maybe this could be done even faster.

15. ## Re: How can I make a diagram like this?

The way you have arranged the data in the spreadsheet is awkward to work with when creating charts. It can work, but, as you've already discovered, it's tedious to work with data arranged like this. If you can rearrange the data with less tedium (you have not really said where the data are coming from, so I don't know if there is something in getting from the source into the spreadsheet that you could do differently), then the chart will be easier to create. Since there is no built in way to add trendlines to multiple series, I would probably calculate the data for the trendlines in the spreadsheet and not use the trendline feature.

In the attached sheet, not knowing where the data came from, I started with the "awkward" table in Chart. Then I:

1) Built a list of company names without the blank rows between. I used autofilter then copy/paste for this, but I'm sure there are other ways as well.
2) Added a column for row #, which is simply a list of odd numbers.
3) Using INDEX() functions, I extract start year, end year, start value, and end value for each company from the list into columns.
4) With the years and values in adjacent columns (see spreadsheet), I can use a LINEST() function to find "slope" and "intercept" for any simple linear function. Your chart shows some linear and some logarithmic, but I wasn't sure how you determined which to use, so I just used a logarithmic (y=m*log(x)+b) for all companies.
5) With slope and intercept, I can compute the value at any year between start and end with a simple formula (see columns J and to the right).
5a) Enter years in row2
5b) Enter the formula in rows 3+. If you pay attention to relative and absolute references, you should be able to enter the formula once, then copy/paste/fill into the rest of the block.
6) I add a copy of column A in column I for convenience in creating the chart.
7) Select I2:end and insert scatter chart. Format chart elements as desired.

My older version cannot show all the data series on the chart, so my chart only shows a few of the data series. I would note that 350 data series is going to be a very busy chart, and I am pessimistic that it will be very useful. Perhaps you intend to include a filter step so you can limit the chart to a few data series or something. That's how I would arrange the data in order to minimize the tedium needed to create the chart.

Of course, all of that assumes that you are allowed to rearrange the data and are not artificially required to work with the data as you show it in Chart.

Does that help any?

16. ## Re: How can I make a diagram like this?

Yes, thank you very much, that helps me a lot. I like the created chart very much

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1