Export and import data between Vensim and Excel

Use this forum to post Vensim related questions.
Post Reply
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Export and import data between Vensim and Excel

Post by kong17 »

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:
Capture.JPG
Capture.JPG (46.33 KiB) Viewed 6860 times
Thank you!
LAUJJL
Senior Member
Posts: 1421
Joined: Fri May 23, 2003 10:09 am
Vensim version: DSS

Re: Export and import data between Vensim and Excel

Post by LAUJJL »

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
Last edited by LAUJJL on Thu Aug 23, 2018 12:59 pm, edited 1 time in total.
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Export and import data between Vensim and Excel

Post by tomfid »

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.
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Export and import data between Vensim and Excel

Post by tomfid »

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).
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: Export and import data between Vensim and Excel

Post by kong17 »

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
Thank you for your reply.
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.
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: Export and import data between Vensim and Excel

Post by kong17 »

tomfid wrote: Thu Aug 23, 2018 12:57 pm 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).
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
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Export and import data between Vensim and Excel

Post by tomfid »

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.
LAUJJL
Senior Member
Posts: 1421
Joined: Fri May 23, 2003 10:09 am
Vensim version: DSS

Re: Export and import data between Vensim and Excel

Post by LAUJJL »

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
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: Export and import data between Vensim and Excel

Post by kong17 »

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.
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
Untitled.png (26.38 KiB) Viewed 6818 times
kong17
Senior Member
Posts: 60
Joined: Wed May 30, 2018 1:12 pm
Vensim version: PRO

Re: Export and import data between Vensim and Excel

Post by kong17 »

LAUJJL wrote: Thu Aug 23, 2018 7:05 pm 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
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
Untitled.png (26.38 KiB) Viewed 6817 times
LAUJJL
Senior Member
Posts: 1421
Joined: Fri May 23, 2003 10:09 am
Vensim version: DSS

Re: Export and import data between Vensim and Excel

Post by LAUJJL »

HI

In which context are you building this model.

Will it be applied?

Regards.

JJ
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: Export and import data between Vensim and Excel

Post by tomfid »

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.
Post Reply