Goal Seek, Solver, and an IRR Workaround

AYData,

I've attached a sample, but I probably have to explain. This is an analysis of Social Security.

1st Table: Cash Flows

There are 47 negative cash flows, followed by 37 positive cash flows. There are three columns and the positive cash flows vary for all.

2nd Table: NPV

This table shows the NPV for the Social Security inflows / outflows given the age the individual dies. For instance, in Column H, Cell 51, it assumes that the individual only collected Social Security for one year before passing away. The next cell down shows two years, etc.

3rd Table: IRR

Finally, the orange area is the table I'm trying to create. I can find the IRRs using Solver. For instance, in cell H87, you can see the NPV for a person living to the end of the time horizon. You can set H87 as the target cell, equal the value to 0, and change cell H89 to obtain the IRR ("rate"). In this instance, the IRR is 3.96%, which I have manually input into the IRR table.


What I'm trying to do is find a way to create an entire IRR table this way, without manually inputting the entire thing. Solver will obviously give me the answers, but it only does so one at a time, and alters my NPV table.

Should I post to the VBA forum?

Still haven't found a good solution. Essentially, I need something that has the functionality of Solver, but that I can use to create an entirely new table of data (based on an existing table).

Found a program called "Solver Table", but it doesn't seem to do what I need it to do. Not sure if there are any other add-ons out there that would expand upon Solver.

In good news, I did find a way to get the Excel IRR function to work properly, but it's a bit labor intensive. The IRR function includes a "guess" feature. I can insert guesses for all my cells and IRR works so long as the guess is close. But obviously, this can be quite laborious when I'm doing this for 10 different scenarios, all of which will have about 960 cells of data to calculate IRR for. The guesses can carry over for a few cells normally, but still ... it would likely mean I'd have to manually edit the formula about 1,000 times.

Dosen't this formula work

=IFERROR(IRR(C$4:C4),"")

It does for me

What's your Excel version?

Strangely, I always get blank cells using that exact formula. Which version of Excel do you use?

Though, the "IFERROR" is a great idea that I hadn't thought of using before. Maybe I can rig that formula to continually test different "guesses" for IRR. That way, I don't have to manually edit the "guess" values for each cell till Excel gives me a proper result.

Thanks for the tip!

Strangely, I always get blank cells using that exact formula. Which version of Excel do you use?

You'd get blank cells only if all your values are negative. Copy the formula down and you'll see the results pop up.

I am on Excel 2010 as my profile suggests