Asp Forum - Need help with pure VB algorithm for data table calcs

GS

5/10/2011 6:48:00 AM

I'm trying to devise a pure VB method of 'what if' analysis to process
calculations for 1 and 2 input data tables such as are available in MS
Excel. Unfortunately, Excel's Data Table functions are not externally
available for automation AND there's no documentation available that
shows what goes on under the hood.

Table example: 2 inputs

IRR ||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|
====||======|======|======|======|======|======|======|
85%|| | | | | | | |
----||------|------|------|------|------|------|------|
90%|| | | | | | | |
----||------|------|------|------|------|------|------|
95%|| | | | | | | |
----||------|------|------|------|------|------|------|
100%|| | | | | | | |
----||------|------|------|------|------|------|------|
105%|| | | | | | | |
----||------|------|------|------|------|------|------|
110%|| | | | | | | |
----||------|------|------|------|------|------|------|
115%|| | | | | | | |
----||------|------|------|------|------|------|------|

The top left cell is the base value. (IRR=52.7%)
The cells to the right and below are the inputs.
Calculated cells exceptions are 15 and 100%

The algorithm needs to fill in the blanks same as does a data table in
Excel, factoring the inputs against IRR.

Sample results:

IRR ||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|
=====||======|======|======|======|======|======|======|
85% || -9.2%| -5.7%| -0.7%| 3.3%| 8.4%| 19.4%| 27.4%|
-----||------|------|------|------|------|------|------|
90% || 3.6%| 8.0%| 14.4%| 19.9%| 27.1%| 41.3%| 53.3%|
-----||------|------|------|------|------|------|------|
95% || 15.7%| 21.3%| 29.1%| 36.4%| 45.9%| 63.4%| 79.8%|
-----||------|------|------|------|------|------|------|
100% || 27.5%| 34.3%| 43.5%| 52.7%| 64.6%| 85.8%|106.9%|
-----||------|------|------|------|------|------|------|
105% || 39.0%| 47.0%| 57.8%| 69.0%| 83.4%|108.4%|134.5%|
-----||------|------|------|------|------|------|------|
110% || 50.3%| 59.6%| 71.9%| 85.2%|102.2%|131.2%|162.5%|
-----||------|------|------|------|------|------|------|
115% || 61.5%| 72.0%| 86.0%|101.5%|121.0%|154.2%|191.0%|
-----||------|------|------|------|------|------|------|

Example table: 1 input

-- --||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|
======||======|======|======|======|======|======|======|
IRR || 27.5%| 34.3%| 43.5%| 52.7%| 64.6%| 85.8%|106.9%|
------||------|------|------|------|------|------|------|

OR

-- --||- 85%-|- 90%-|- 95%-|-100%-|-105%-|-110%-|-115%-|
======||======|======|======|======|======|======|======|
IRR || 3.3%| 19.9%| 36.4%| 52.7%| 69.0%| 85.2%|101.5%|
------||------|------|------|------|------|------|------|

Any suggestions would be greatly appreciated. Accuracy is most
important, regardless of methodology.

Thanks in advance...

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

4 Answers

(nobody)

5/10/2011 8:03:00 AM

"GS" <gs@somewhere.net> wrote in message news:iqan0m$pmo$1@dont-email.me...
> I'm trying to devise a pure VB method of 'what if' analysis to process
> calculations for 1 and 2 input data tables such as are available in MS
> Excel. Unfortunately, Excel's Data Table functions are not externally
> available for automation AND there's no documentation available that shows
> what goes on under the hood.
>
>
> Table example: 2 inputs
>
> IRR ||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|

http://en.wikipedia.org/wiki/Internal_rate...

GS

5/10/2011 4:08:00 PM

Nobody has brought this to us :
> "GS" <gs@somewhere.net> wrote in message news:iqan0m$pmo$1@dont-email.me...
>> I'm trying to devise a pure VB method of 'what if' analysis to process
>> calculations for 1 and 2 input data tables such as are available in MS
>> Excel. Unfortunately, Excel's Data Table functions are not externally
>> available for automation AND there's no documentation available that shows
>> what goes on under the hood.
>>
>>
>> Table example: 2 inputs
>>
>> IRR ||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|
>
>
> http://en.wikipedia.org/wiki/Internal_rate...

Thanks! That's a good place to start. Are you suggesting the Excel
table is just recalcing IRR for each input, row by row?

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS

5/10/2011 9:13:00 PM

Nobody was thinking very hard :
> "GS" <gs@somewhere.net> wrote in message news:iqan0m$pmo$1@dont-email.me...
>> I'm trying to devise a pure VB method of 'what if' analysis to process
>> calculations for 1 and 2 input data tables such as are available in MS
>> Excel. Unfortunately, Excel's Data Table functions are not externally
>> available for automation AND there's no documentation available that shows
>> what goes on under the hood.
>>
>>
>> Table example: 2 inputs
>>
>> IRR ||- 21 -|- 19 -|- 17 -|- 15 -|- 13 -|- 11 -|- 9 -|
>
>
> http://en.wikipedia.org/wiki/Internal_rate...

Getting IRR is not a problem. What I'm trying to do is calculate 'what
ifs' for a given IRR using the 7 inputs in top row AND 7 inputs in left
column, to generate the sample table. So for each %input in left column
I need to calc the what-if% for each of the 7 inputs in the top row.

The mean is 100$ and 15, and these contribute to how the IRR was
arrived at.

What I'm hoping is that someone has had need to do this in a grid
control at some time, and could/would help me be able to do the same.

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS

5/10/2011 9:15:00 PM

GS explained on 5/10/2011 :
> The mean is 100$ and 15...

Should read:

The mean is 100% and 15...

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Need help with pure VB algorithm for data table calcs

GS

(nobody)

GS

GS

GS

x Login to ForumsZone