Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR Problem
I have an IRR that seemingly cannot be solved by Excel.
I have a cash flow of $20,000 for two years. The value at the end of the 2nd year is $43. On my HP 12C it calculates the IRR as -99.79. So I know there is a answer. I cannot get Excel to give my any answer other than an error such as #Value. =IF(ISERROR(IRR((R3C20:RC20,RC38),-0.9)),IRR((R3C20:RC20,RC38),R[-1]C),IRR((R3C20:RC20,RC38),-0.9)) Basically the first portion says if you can't get an answer with the IRR Guess of -0.9, then try using the IRR from the previous year, else use the -0.9. The context is this is a life insurance illustration. I am calculating the IRR on the Cash Value each year. In the first year I have a cash flow of $20,000 with a return of zero. I do not use the IRR function but take the first year CV / Premium and subract. Result is -100%. Next year another $20,000 is paid as premium and the CV is $43. -20,000 -20,000 +43 Excel returns #Value using my formula. If I strip down my formula by removing the IF condition and only use the -0.9 guess, Excel returns "#Num". The only way I can get Excel to calculate the IRR is to manually type in the -0.9979. BUT Excel returns -99.68% How can I persuade Excel to return -99.79% instead of an error message? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR Problem
From your formula I cannot see if you address the right number of cells; it should be OK if the formula itself is on Row 4.
If I use your data and a guess of -0.99999 I get 99.7854602729448% -- Kind regards, Niek Otten Microsoft MVP - Excel "Dkline" wrote in message ... |I have an IRR that seemingly cannot be solved by Excel. | | I have a cash flow of $20,000 for two years. The value at the end of the 2nd | year is $43. On my HP 12C it calculates the IRR as -99.79. So I know there is | a answer. | | I cannot get Excel to give my any answer other than an error such as #Value. | | =IF(ISERROR(IRR((R3C20:RC20,RC38),-0.9)),IRR((R3C20:RC20,RC38),R[-1]C),IRR((R3C20:RC20,RC38),-0.9)) | | Basically the first portion says if you can't get an answer with the IRR | Guess of -0.9, then try using the IRR from the previous year, else use the | -0.9. | | The context is this is a life insurance illustration. I am calculating the | IRR on the Cash Value each year. | | In the first year I have a cash flow of $20,000 with a return of zero. I do | not use the IRR function but take the first year CV / Premium and subract. | Result is -100%. | | Next year another $20,000 is paid as premium and the CV is $43. | | -20,000 | -20,000 +43 | Excel returns #Value using my formula. | | If I strip down my formula by removing the IF condition and only use the | -0.9 guess, Excel returns "#Num". | | The only way I can get Excel to calculate the IRR is to manually type in the | -0.9979. BUT Excel returns -99.68% | | How can I persuade Excel to return -99.79% instead of an error message? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR Problem
Year Cell Value
1 R3C20 20,000 2 R4C20 20,000 2 R4C38 -43 The R3C20:RC20 is locking in the starting row and letting the end row be the current row as you go down the column. My HP 12C agrees with your number. I'm trying to avoid hard coding a number in there as eventually the IRR will reach almost 9% in the 121 years I potentially have to calculate the value. With that many durations, I frequently have a problem in later years where a flat guess of -0.9 fails but can be solved by using the prior year result. What is odd to me in this example is that it fails in year 2. This particular case has 80 years of IRR to solve. "Niek Otten" wrote: From your formula I cannot see if you address the right number of cells; it should be OK if the formula itself is on Row 4. If I use your data and a guess of -0.99999 I get 99.7854602729448% -- Kind regards, Niek Otten Microsoft MVP - Excel "Dkline" wrote in message ... |I have an IRR that seemingly cannot be solved by Excel. | | I have a cash flow of $20,000 for two years. The value at the end of the 2nd | year is $43. On my HP 12C it calculates the IRR as -99.79. So I know there is | a answer. | | I cannot get Excel to give my any answer other than an error such as #Value. | | =IF(ISERROR(IRR((R3C20:RC20,RC38),-0.9)),IRR((R3C20:RC20,RC38),R[-1]C),IRR((R3C20:RC20,RC38),-0.9)) | | Basically the first portion says if you can't get an answer with the IRR | Guess of -0.9, then try using the IRR from the previous year, else use the | -0.9. | | The context is this is a life insurance illustration. I am calculating the | IRR on the Cash Value each year. | | In the first year I have a cash flow of $20,000 with a return of zero. I do | not use the IRR function but take the first year CV / Premium and subract. | Result is -100%. | | Next year another $20,000 is paid as premium and the CV is $43. | | -20,000 | -20,000 +43 | Excel returns #Value using my formula. | | If I strip down my formula by removing the IF condition and only use the | -0.9 guess, Excel returns "#Num". | | The only way I can get Excel to calculate the IRR is to manually type in the | -0.9979. BUT Excel returns -99.68% | | How can I persuade Excel to return -99.79% instead of an error message? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |