Data Table

hmz

unread,

Jan 4, 2010, 8:13:01 PM1/4/10

to

Hi,

I am trying to create at data table with two inputs and one outcome.

The inputs are calculated: (1) An IRR and (2) a Debt Coverage Ratio. The
output is rent, which is not a formula. I want to be able to input any IRR
value and/or DCR, but the Data Table will not output my rental change that
would correspond to the changes in the IRR/DCR. Is Excel capable of doing
this, and if so, can someone send me a simple demonstration/calculation to
help me? I have been working on this for a bit and am frustrated. I am afraid
that I am not able to have a clear head since i have been working on this for
a couple of hours trying different scenarios.

Thank you so much,
--
hmz

Mike Middleton

unread,

Jan 5, 2010, 3:00:09 AM1/5/10

to

hmz -

For an example of a two-input data table, see my chapter "Sensitivity
Analysis Using Excel" file:

http://www.treeplan.com/chapters/02_decan_20071029_1042.pdf

specifically, section 2.4.

- Mike
http://www.MikeMiddleton.com

"hmz" <h...@discussions.microsoft.com> wrote in message
news:7C518A92-6EBB-4B11...@microsoft.com...

hmz

unread,

Jan 5, 2010, 11:59:01 AM1/5/10

to

Thank you. This information is helpful. I realize that data tables are only
for input sensitivities. What I need is to use an output to find my input. In
my case, I am trying to find out what (input) rent should be to produce an
IRR (outcome), but I need to use the IRR to find the rent. If I want a 25%
IRR, what rent should I have to get there. Since, IRR is an output
(calculated cell), the table will not work for me. Is there another area in
Excel that I can look? Goal Seek will work, but I was hoping to have
something more in a table format for my client.

Again, thank you for the helpful information. –

hmz
nyc

hmz

unread,

Jan 5, 2010, 12:00:01 PM1/5/10

to

Thank you. This information is helpful. I realize that data tables are only
for input sensitivities. What I need is to use an output to find my input. In
my case, I am trying to find out what (input) rent should be to produce an
IRR (outcome), but I need to use the IRR to find the rent. If I want a 25%
IRR, what rent should I have to get there. Since, IRR is an output
(calculated cell), the table will not work for me. Is there another area in
Excel that I can look? Goal Seek will work, but I was hoping to have
something more in a table format for my client.

Again, thank you for the helpful information. –

--
hmz
nyc

Mike Middleton

unread,

Jan 5, 2010, 1:50:35 PM1/5/10

to

hmz -

> Goal Seek will work, but I was hoping to have something more in a table
> format for my client. <

So, perform multiple "manual" goal seeks, and display the results in a
"table."

- Mike

"hmz" <h...@discussions.microsoft.com> wrote in message

news:BFB3D1FF-6CBE-41F6...@microsoft.com...