a sum of numbers should be equal to 0 (what is the closest percentage)

Hello,

I am working on an engineering economy problem (FV,PV) but could not figure out how to reach my goals without using VBA.

Please see the attachment for a sample cashflow workbook. I have 3 cash flows, and I need the sum of these cash flows to be equal to 0 (or as close as possible to 0). In other words, what would be the interest (in this case it is "x") that I would pick from the "interest rate" column so that the sum of these 3 cashflows can be equal to 0 (or closest to 0).

I attempted to list the interest rate starting from 0.0001 and let excel pick up using lookup function, but was not successful .

Any help is much appreciatedAttachment 699065

Interest rate = 26.3762485681608%

Select the Data tab then "What If Analysis"

Select "Goal Seek" then set these three variables:

Set Cell = D7

To Value = 0

By Changing Cell = G2

EDIT: NEVERMIND, I GOT IT. THANKS again

@fomenter23.... The IRR gives you that rate. For your example, =IRR({-3000,3000,1000}) in B6.

Note that cash flows must have opposite signs for inflows and outfllows. It would be better to enter -3000 into B2 and use =IRR(B2:B4).

The result from Excel IRR is 26.3762615824786% (+1.11E-16). With that rate, Excel NPV returns about 3.41E-9.

In contrast, if Goal Seek returns 26.3762485681608%, Excel NPV returns about 3.73E-4, which is not as close to zero.

FYI, the best IRR is 26.3762615825973% (+3.86E-16). Excel NPV returns 0.00E+0, which is exactly zero.

With Excel NPV, the formula should be =NPV(B6,{3000,1000})-3000, not =NPV(B6,{-3000,3000,1000}).

The latter should also return exactly zero, if B6 is the best IRR. Otherwise, it returns a different result because Excel NPV discounts the first cash flow, unlike most interpretations of the NPV calculation.

Arguably, the difference is too small to worry about, and NPV(B6,{-3000,3000,1000}) is more convenient to write and to remember.