Export and import data between Vensim and Excel
Export and import data between Vensim and Excel
Hell @all
I am having a question:
I want to calculate the internal rate of return(IRR). The cash flow and investment costs will be changed every year, so the IRR is different every year.
I try to use IRR function in Vensim, but it is not available. Then I find that Vensim and Excel could export and import data between each other.
So, I was wondering could I achieve this function?
When I run the model, Vensim export partial data (like cash flow and investment costs) to Excel. Then, Excel calculate the IRR and export the result to Vensim, which forms a loop. like the figure shows: Thank you!
I am having a question:
I want to calculate the internal rate of return(IRR). The cash flow and investment costs will be changed every year, so the IRR is different every year.
I try to use IRR function in Vensim, but it is not available. Then I find that Vensim and Excel could export and import data between each other.
So, I was wondering could I achieve this function?
When I run the model, Vensim export partial data (like cash flow and investment costs) to Excel. Then, Excel calculate the IRR and export the result to Vensim, which forms a loop. like the figure shows: Thank you!
Re: Export and import data between Vensim and Excel
why do you want to calculate the IRR out of Vensim?
Because the function exists in Excel?
It is much simpler to calculate the IRR in Vensim, than exchanging data between vensim and excel.
to calculate the IRR, you must find the zero of the NPV, that i think is calculated in Vensim.
to find the rate of interest that makes the NPV equal to zero, you use the find zero vensim function;
there is an internal rate of return in Vensim, not available in PLE and PLe plus.
if you have only the PLE or the PLE plus, it may be difficult to calculate the IRR in Vensim, but it may be too difficult to exchange data with Excel.
Regards.
JJ
Because the function exists in Excel?
It is much simpler to calculate the IRR in Vensim, than exchanging data between vensim and excel.
to calculate the IRR, you must find the zero of the NPV, that i think is calculated in Vensim.
to find the rate of interest that makes the NPV equal to zero, you use the find zero vensim function;
there is an internal rate of return in Vensim, not available in PLE and PLe plus.
if you have only the PLE or the PLE plus, it may be difficult to calculate the IRR in Vensim, but it may be too difficult to exchange data with Excel.
Regards.
JJ
Last edited by LAUJJL on Thu Aug 23, 2018 12:59 pm, edited 1 time in total.
Re: Export and import data between Vensim and Excel
If IRR is unavailable because you're using PLE, data i/o is also unavailable. However, you can use the table tool to copy/paste your output into Excel.
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Re: Export and import data between Vensim and Excel
I don't think you really want IRR anyway, because it operates over a fixed period. For adaptive behavior, you want some kind of moving-average perception of rate of return. That could be as simple as SMOOTH(cash flow)/(capital investment).
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Re: Export and import data between Vensim and Excel
Thank you for your reply.LAUJJL wrote: ↑Thu Aug 23, 2018 12:47 pm why do you want to calculate the IRR out of Vensim?
Because the function exists in Excel?
It is much simpler to calculate the IRR in Vensim, than exchanging data between vensim and excel.
to calculate the IRR, you must find the zero of the NPV, that i think is calculated in Vensim.
to find the rate of interest that makes the NPV equal to zero, you use the find zero vensim function;
there is an internal rate of return in Vensim, not available in PLE and PLe plus.
if you have only the PLE or the PLE plus, it may be difficult to calculate the IRR in Vensim, but it may be too difficult to exchange data with Excel.
Regards.
JJ
My version is PRO. I know the Internal rate of return function in Vensim, but I do not really understand from the Vensim HELP. I download the example from HELP. It looks like just a process of solving IRR (From the graph, it shows the IRR increases from negative to positive).
In my model, I need to get profitability index. IRR is very important value for that. According to the cash flow, investment cost and amortization, I want to get IRR then get profitability index. Through the profitability index, it will decide how many new production equipment will be invested. Then, the new investment will create new cash flow and investment costs.
I want to get the exact the exact calculated value of IRR. So, It might be a way to export and import data between Excel and Vesnim.
And thank you for your advise about Find zero function. I try to use the function. To be honest, it is not explained clearly in Vensim HELP and EXAMPLE, maybe this is because I am not smart.
Re: Export and import data between Vensim and Excel
In my model, I need to get profitability index. IRR is very important value for that. According to the cash flow, investment cost and amortization, I want to get IRR then get profitability index. Through the profitability index, it will decide how many new production equipment will be invested. Then, the new investment will create new cash flow and investment costs.
If I use SMOOTH(cash flow)/(capital investment) that you mentioned above, Does this mean cash flow is annual cash flow and capital investment is annual capital investment?
Thank you
Re: Export and import data between Vensim and Excel
IRR makes sense for one-time investments. I'm not sure how you would integrate that into a continuously-evolving control system.
Normally the cash flow would be the instantaneous cash flow; the purpose of smoothing is to convert the variable or stochastic flow into a more stable expectation. Capital investment is the cumulative (stock) value.
I think we'd need to know more about your particular situation to make reommendations.
Normally the cash flow would be the instantaneous cash flow; the purpose of smoothing is to convert the variable or stochastic flow into a more stable expectation. Capital investment is the cumulative (stock) value.
I think we'd need to know more about your particular situation to make reommendations.
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Re: Export and import data between Vensim and Excel
Hi
what is your problem?
Every year, I suppose that you have to take some décisions.
which décisions?
How is the cash flow of the year dependant from past Investments?
it would be useful to upload a very small model to illustrate your problem.
Regards.
JJ
what is your problem?
Every year, I suppose that you have to take some décisions.
which décisions?
How is the cash flow of the year dependant from past Investments?
it would be useful to upload a very small model to illustrate your problem.
Regards.
JJ
Re: Export and import data between Vensim and Excel
Thank you all for your replies. I am new to Vensim and would really appreciate your advice.tomfid wrote: ↑Thu Aug 23, 2018 5:23 pm IRR makes sense for one-time investments. I'm not sure how you would integrate that into a continuously-evolving control system.
Normally the cash flow would be the instantaneous cash flow; the purpose of smoothing is to convert the variable or stochastic flow into a more stable expectation. Capital investment is the cumulative (stock) value.
I think we'd need to know more about your particular situation to make reommendations.
My model is for a long-term (about 30 years) generation capacity investment decisions in electricity market.
The figure is a simple casual loop of my model. According to the structure, the generation capacity expansion decisions are made based on the profitability assessment of a particular investment. The profit is normalized and converted into investment decision through some multipliers. After some delays (due to construction time) new generation capacities will be available. The gap between electricity supply and demand caused by current capacity and demand affects the electricity price, which forms main feedback loop in this process.
The model needs to achieve equilibrium (i.e. supply = demand). But, the demand changes every year, the net cash flow varies from year to year. So, I use IRR to calculate the profitability index, then evaluate how much new capacity should be invested to achieve equilibrium. So, I need to calculate the IRR every year. It would be great, if there is a way to calculate IRR at every time step (i.e. year) within Vensim, otherwise, I am planning to import and export data between Vensim and Excel to solve it.
Thank you very much!
- Attachments
-
- Untitled.png (26.38 KiB) Viewed 6869 times
Re: Export and import data between Vensim and Excel
Thank you all for your replies. I am new to Vensim and would really appreciate your advice.
My model is for a long-term (about 30 years) generation capacity investment decisions in electricity market.
The figure is a simple casual loop of my model. According to the structure, the generation capacity expansion decisions are made based on the profitability assessment of a particular investment. The profit is normalized and converted into investment decision through some multipliers. After some delays (due to construction time) new generation capacities will be available. The gap between electricity supply and demand caused by current capacity and demand affects the electricity price, which forms main feedback loop in this process.
The model needs to achieve equilibrium (i.e. supply = demand). But, the demand changes every year, the net cash flow varies from year to year. So, I use IRR to calculate the profitability index, then evaluate how much new capacity should be invested to achieve equilibrium. So, I need to calculate the IRR every year. It would be great, if there is a way to calculate IRR at every time step (i.e. year) within Vensim, otherwise, I am planning to import and export data between Vensim and Excel to solve it.
Thank you very much!
- Attachments
-
- Untitled.png (26.38 KiB) Viewed 6868 times
Re: Export and import data between Vensim and Excel
HI
In which context are you building this model.
Will it be applied?
Regards.
JJ
In which context are you building this model.
Will it be applied?
Regards.
JJ
Re: Export and import data between Vensim and Excel
You don't really need IRR per se. The maximum IRR occurs at the same point as maximum return or NPV at a fixed rate.
/*
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/
Advice to posters (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391
Blog: http://blog.metasd.com
Model library: http://models.metasd.com
Bookmarks: http://delicious.com/tomfid/SystemDynamics
*/