Real Tough Question - Goal Seek Type Function

[billybobb]

Hello,

I am wondering if there is a way to have excel run iterations to find out when one cell would be equal to another.

As an example:

Say I have an INPUT of 5% which generates an output of $10

I have another INPUT of 7% which generates an output of $20

I know these two eventually intersect, I have the data table to prove it but I wanted to know if there is a way excel can solve for the INPUT that sets their outputs equal to each other, Goal Seek falls short because you need to enter the value, I want this to be automatic...

Any ideas, I appreciate the help.

Thanks,

Billy

 

[shg]

You can use a third cell that calculates the difference between the two, and use Goal Seek to get that cell to zero.

 

[billybobb]

You cant in this case because then you would have to do a data table with every possible scenario, the idea is to have excel find the INPUT that sets the two outputs equal to each other...

 

[shg]

Where did a data table enter the discussion?

Maybe you could get a little more specific as to what's what.

 

[billybobb]

I am calculating two separate Net Present Values, those are my outputs. My inputs are the discount rate being used in each scenario. I want to find out what discount rate will make both Net Present Values equal to each other. I can plot this visually and get a rough idea of where the will meet but i'm wondering if excel will solve this for me and say at a discount rate of 8% both NPV's are the same..

 

[shg]

If one formula appears in A1 and the other in A2, and the common rate is in A3, then in B1 enter the formula =A1-A2

Then use Goal Seek to set B1 to zero by changing A3.

 

[Legacy 165240]

I am calculating two separate Net Present Values, those are my outputs. My inputs are the discount rate being used in each scenario. I want to find out what discount rate will make both Net Present Values equal to each other. I can plot this visually and get a rough idea of where the will meet but i'm wondering if excel will solve this for me and say at a discount rate of 8% both NPV's are the same..

What you are seeking is called Crossover rate where two projects have the same net present value thus making you indifferent in selecting either of the two projects.

Crossover rate is also referred to as incremental IRR, which is internal rate of return for the series of cash flows that result from difference of respective cash flows in each project

Say you have the cash flows for Project 1 in Column A from rows 1 to 3
And cash flows for Project 2 in Column B from rows 1 to 3

A1: -500
A2: 600
A3: 600

A4: =IRR(A1:A3)
84.90%

B1: -600
B2: 700
B3: 700
B4: =IRR(B1:B3)
81.09%

Crossover Rate
A5: =IRR(A1:A3-B1:B3)
61.80%

At 61.80% project 1 and project 2 have the same NPV

A6: = NPV(61.80%,A2:A3)+A1
$100.02
B6: = NPV(61.80%,B2:B3)+B1
$100.02