| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Working back from IRR
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 "William" wrote: 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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Working time and days | Excel Worksheet Functions | |||
| Working time and days | Excel Discussion (Misc queries) | |||
| how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
| Trace Dependents and Precedents not working | Excel Worksheet Functions | |||
| Roll back to previous date | Excel Worksheet Functions | |||
Linear Mode
