Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
Hello all!! Hopefully this will be an easy one. I have a sheet to calculate
IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
You should be able to do what you want with the Offset function. For example,
=irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
That sounds pretty close to what I want to do but each calculation will have
to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
I understand what your issue is, but solutions are difficult to come up with if
you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
The spreadsheet looks like this:
A B C Income (Expense) 1 Cash Flow This is repeated for 50 years. Column B contains only one number to represent the year. CashFlow is brought in from another sheet in the workbook titled "Profit Calcs". The range is C9:C59, C9 being the initial investment. The Final Year is found on a worksheet called "Forestry Provided Information" and is found in cell B14. As previously stated, I'm trying to keep the flexibility of the workbook open and adaptable to calculate as much as 50 years. If you would like to see the actual workbook, let me know where to send or post it. "Fred Smith" wrote: I understand what your issue is, but solutions are difficult to come up with if you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
I found the answer on my own. Thanks anyway. All I had to do was create a
hidden column that contained the following formula and it worked. =IF('Forestry Provided Info'!$B$14=5, G8=IRR(C9:C15)) "Mawaller" wrote: The spreadsheet looks like this: A B C Income (Expense) 1 Cash Flow This is repeated for 50 years. Column B contains only one number to represent the year. CashFlow is brought in from another sheet in the workbook titled "Profit Calcs". The range is C9:C59, C9 being the initial investment. The Final Year is found on a worksheet called "Forestry Provided Information" and is found in cell B14. As previously stated, I'm trying to keep the flexibility of the workbook open and adaptable to calculate as much as 50 years. If you would like to see the actual workbook, let me know where to send or post it. "Fred Smith" wrote: I understand what your issue is, but solutions are difficult to come up with if you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
I'm glad you're happy. But you might want to look into your formula, because
with the one you posted, there's not a chance it will provide a proper IRR. -- Regards, Fred "Mawaller" wrote in message ... I found the answer on my own. Thanks anyway. All I had to do was create a hidden column that contained the following formula and it worked. =IF('Forestry Provided Info'!$B$14=5, G8=IRR(C9:C15)) "Mawaller" wrote: The spreadsheet looks like this: A B C Income (Expense) 1 Cash Flow This is repeated for 50 years. Column B contains only one number to represent the year. CashFlow is brought in from another sheet in the workbook titled "Profit Calcs". The range is C9:C59, C9 being the initial investment. The Final Year is found on a worksheet called "Forestry Provided Information" and is found in cell B14. As previously stated, I'm trying to keep the flexibility of the workbook open and adaptable to calculate as much as 50 years. If you would like to see the actual workbook, let me know where to send or post it. "Fred Smith" wrote: I understand what your issue is, but solutions are difficult to come up with if you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
Fred Smith,
You are correct. I had to remove the G8=. Don't try to help me anymore. You are an abrasive individual. I consider you rude and not one single piece of advice you offered helped in any way, not that you really offered anything but criticizism. Your response was uncalled for and unnecessary. Correcting my mistake, offering an alternate, or pointing me towards more information would have been helpful but instead you chose to be a jerk. Waste someone else's time with your crap because I don't need it. "Fred Smith" wrote: I'm glad you're happy. But you might want to look into your formula, because with the one you posted, there's not a chance it will provide a proper IRR. -- Regards, Fred "Mawaller" wrote in message ... I found the answer on my own. Thanks anyway. All I had to do was create a hidden column that contained the following formula and it worked. =IF('Forestry Provided Info'!$B$14=5, G8=IRR(C9:C15)) "Mawaller" wrote: The spreadsheet looks like this: A B C Income (Expense) 1 Cash Flow This is repeated for 50 years. Column B contains only one number to represent the year. CashFlow is brought in from another sheet in the workbook titled "Profit Calcs". The range is C9:C59, C9 being the initial investment. The Final Year is found on a worksheet called "Forestry Provided Information" and is found in cell B14. As previously stated, I'm trying to keep the flexibility of the workbook open and adaptable to calculate as much as 50 years. If you would like to see the actual workbook, let me know where to send or post it. "Fred Smith" wrote: I understand what your issue is, but solutions are difficult to come up with if you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to stop IRR calculation at a particular year...
Have a nice day.
-- Regards, Fred "Mawaller" wrote in message ... Fred Smith, You are correct. I had to remove the G8=. Don't try to help me anymore. You are an abrasive individual. I consider you rude and not one single piece of advice you offered helped in any way, not that you really offered anything but criticizism. Your response was uncalled for and unnecessary. Correcting my mistake, offering an alternate, or pointing me towards more information would have been helpful but instead you chose to be a jerk. Waste someone else's time with your crap because I don't need it. "Fred Smith" wrote: I'm glad you're happy. But you might want to look into your formula, because with the one you posted, there's not a chance it will provide a proper IRR. -- Regards, Fred "Mawaller" wrote in message ... I found the answer on my own. Thanks anyway. All I had to do was create a hidden column that contained the following formula and it worked. =IF('Forestry Provided Info'!$B$14=5, G8=IRR(C9:C15)) "Mawaller" wrote: The spreadsheet looks like this: A B C Income (Expense) 1 Cash Flow This is repeated for 50 years. Column B contains only one number to represent the year. CashFlow is brought in from another sheet in the workbook titled "Profit Calcs". The range is C9:C59, C9 being the initial investment. The Final Year is found on a worksheet called "Forestry Provided Information" and is found in cell B14. As previously stated, I'm trying to keep the flexibility of the workbook open and adaptable to calculate as much as 50 years. If you would like to see the actual workbook, let me know where to send or post it. "Fred Smith" wrote: I understand what your issue is, but solutions are difficult to come up with if you keep us guessing as to what your spreadsheet looks like. Where is the cash flow information (ie, what range)? Where is the final year (what cell and worksheet)? What format is the year in -- two digits, four digits, or an Excel date? -- Regards, Fred "Mawaller" wrote in message ... That sounds pretty close to what I want to do but each calculation will have to start with the initial investment which will be the first cell used. It will then progress down a column until it reaches the final year which is provided on another worksheet. My issue is that Excel continues to calculate to the 50th year because I don't know how to tell it to stop where I want it to. "Fred Smith" wrote: You should be able to do what you want with the Offset function. For example, =irr(offset(b1:b50,30,0)) will do an IRR on the range B31:B50. 30 could be in a cell rather than hard coded into the formula. -- Regards, Fred "Mawaller" wrote in message ... Hello all!! Hopefully this will be an easy one. I have a sheet to calculate IRR. The sheet holds 50 years of information. The workbook I'm using will rarely require all 50 years worth of information. Is there a way to tell Excel, depending on the value of another cell, to stop the IRR calculation on a particular year? The sheet automatically calculates all 50 years. But most of the time we just need 10 or 20 years included in the calculation. Any help will be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Year Calculation | Excel Worksheet Functions | |||
how can I stop Excel Advancing the year by 1 when I enter a date? | Excel Discussion (Misc queries) | |||
Fiscal Year Calculation | Excel Worksheet Functions | |||
Calculation with Working day of the year | Excel Discussion (Misc queries) | |||
Is the IRR calculation based on cash flows at beginning of year? | Excel Worksheet Functions |