Working back from IRR

[Guest]

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

 

[William]

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 "Tools>Goal 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
(e-mail address removed)

 

[Guest]

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%

 

[Guest]

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

 

[Guest]

Tks very much - the same method as Alok below.

Mark

 

[Guest]

William,

Tks very much but I have to avoid the Goal Seeker route since there are many
other factors that affect the net figure and my chaps won't be able to goal
seek all the time. It seems that the next two answers have given me what I
need.

M

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 "Tools>Goal 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
(e-mail address removed)

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

 

[Guest]

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