Alok,
Brilliant - it works perfectly. I'll get back to gross by adding back the
costs in each column.
Thanks very much indeed - you've saved me a box of aspirins
Mark
"Alok" wrote:
Hi Mark
Use the following formula
=(10000*(1+B8)^4-3000*(1+B8)^3)/((1+B8)^2+(1+B8)+1)
where B8 is the Expected IRR (one you have as 4%). This will provide the
cash flow for years 2,3 and 4 assuming ofcourse that they are equal. The
answer I get is $2666.579. Hope this helps
Alok Joshi
"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
|