Tks very much - the same method as Alok below.
Mark
"N Harkawat" wrote:
Mark
If you do not want to use Goal seek or solver the only formula based
approach I can comprehend is only if the cash flows for year 2 3 and 4 are
identical.
So in your example if your retun (IRR) you are looking for is 4% then the
discount values for $1 for years 1 thru 4 will be =
0 1.000
1 0.962 (1/1.04)
2 0.925 (1/(1.04^2)
3 0.889
4 0.855
given year 1 and year 2 cash flows the Present value total = -10000 * 1 +
3000 * .962
= -7,115
the sum of discount values for year 2 thru 4 = 0.925+0.889+0.855 = 2.668
Divide 7115 / 2.668 = $ 2,666.58
Hence you cash flows for year 2 thru 4 = 2,666.58 per year and that way your
Irr = 4%
"MarkT" wrote:
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
|