Hi Mark
1) I am assuming your current net income cells are in the range B8:F8
2) Enter (without inverted commas) "1" in cell H14
3) Enter the formula =B$8 in cell B14
4) Enter the formula =C$8*$H14 in cell C14
5) Copy cell C14 to D14:H14
6) Enter the formula =IRR(B14:F14) in cell A14
Once you have set this up....
1) Select "ToolsGoal Seek"
2) In the "Set Cell" box enter (without inverteed commas) "A14"
3) In the "To value" box enter the IRR you wish to achieve, say, 0.04 (which
is 4%)
4) In the "By Changing cell" box enter (without inverteed commas) "H14"
5) Click OK.
[You could then copy the range A14:H14 to, say, A15:H20, enter a range of
desired IRRs in cells A15:A20 and repeat the above "goal seek" process for
each row thereby constructing a table showing different cash flows for
different IRRs.]
--
XL2003
Regards
William
"MarkT" wrote in message
...
Dear clever people,
I have reached the limits of my brain (doesn't take much nowadays!) and
it's
time to call for some help.
I am writing a spreadsheet for management which determines investment
returns on purchases. The management want to know not only what is the IRR
having entered estimated Gross Incomes over a period of years (that's the
easy bit which I've done), BUT ALSO work it the other way around and get
the
sheet to show what the balance of yers Gross Incomes should be to return
an
entered IRR after estimating the first years income. (They can reasonably
judge the first years gross income and want to see annually what the
balance
years have to generate against a given IRR to see if the investment is
worth
the risk). Goal seek works of course, but they want it shown with a
formula
so that they can play around more easily. Hereunder is a simplified
version
of the spreadsheet.
Year0 year1 year2 year3 year4
Intial Investment -10,000
Gross Annual Income 3,000 4,000 5,000 3,000
5 pct commission 150 200 250 150
EBITDA -10,000 2,850 3,800 4,750 2,850
Annual Costs 250 263 276 289
Net Income -10,000 2,600 3,538 4,474 2,561
IRR for these gross incomes is 12% =IRR("above row")
For IRR to be 4%
year1 year2
year3 year4
Gross Income for balance years shud be 3,000 ???? ???? ????
Tks anyone.
BTW, I used to have an excellent little DOS program called Math that I
downloaded from ZDNet about 5 years ago. It was an algebraic solver and
allowed you to enter for instance:
a=b/c. Yu then asked 'solve for b' and it would return b=a x c . It worked
brilliantly for complex algebra and I have not be able to find it again
after
a virus wipeout. Many programs solve equations for you but only when yu
enter
values, not multiple variables. Anyone still got this 'math.exe' or know
of
an alternative?
Mark