If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Offset function gives different answer in columns v Rows
I have a set of numbers by year and am trying to get the NPV on forward
basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#2
|
|||
|
|||
Offset function gives different answer in columns v Rows
Try removing the dollar sign in front of the 1st $D65.
"Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#3
|
|||
|
|||
Offset function gives different answer in columns v Rows
Hi Joel
No that did not work. I removed all $ signs and that did not help. I found that using different discount rates gives a different constant error but cannot figure out the correlation between discount rate and the constant. The table below shows the discount rate v constant error 0% 1.0000 5% 0.2371 8% 0.1033 10% 0.0601 12% 0.0353 15% 0.0162 Maybe you can help. Many Thanks "joel" wrote: Try removing the dollar sign in front of the 1st $D65. "Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#4
|
|||
|
|||
Offset function gives different answer in columns v Rows
I like using the evaluate formula feature in exel to find problems. click on
cell where you are getting the wrong answer. then from menu tools - formula auditing - evaluate formula. If you didn't get enough information the 1st time you go throughthe evaluate formula start again by pressing Evalute. The 2nd time excel will give you more useful information. "Jolly" wrote: Hi Joel No that did not work. I removed all $ signs and that did not help. I found that using different discount rates gives a different constant error but cannot figure out the correlation between discount rate and the constant. The table below shows the discount rate v constant error 0% 1.0000 5% 0.2371 8% 0.1033 10% 0.0601 12% 0.0353 15% 0.0162 Maybe you can help. Many Thanks "joel" wrote: Try removing the dollar sign in front of the 1st $D65. "Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#5
|
|||
|
|||
Offset function gives different answer in columns v Rows
Joel
Thanks for the tip on "Evaluate" feature. However, going through the formual it seems to have the correct input in the formula at each stage but the result is incorrect. There is obviously a relationship between discount rate and the constsant but it is the offset function that gives the error as the NPV function on its own works fine between rows and columns. Mind blowing!! I do not understand. Stuckand needing help!! "joel" wrote: I like using the evaluate formula feature in exel to find problems. click on cell where you are getting the wrong answer. then from menu tools - formula auditing - evaluate formula. If you didn't get enough information the 1st time you go throughthe evaluate formula start again by pressing Evalute. The 2nd time excel will give you more useful information. "Jolly" wrote: Hi Joel No that did not work. I removed all $ signs and that did not help. I found that using different discount rates gives a different constant error but cannot figure out the correlation between discount rate and the constant. The table below shows the discount rate v constant error 0% 1.0000 5% 0.2371 8% 0.1033 10% 0.0601 12% 0.0353 15% 0.0162 Maybe you can help. Many Thanks "joel" wrote: Try removing the dollar sign in front of the 1st $D65. "Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#6
|
|||
|
|||
Offset function gives different answer in columns v Rows
NPV is very sensitive to the number of decimal place accuracy of the numbers.
If hte input numbers aren't exactly the same I would expect a small difference in the results. I don't think the inputs numbers you are using is exactly the same. I've also seen round problems out the the last deciomal place. Last week I had two numbers that were exactly the same out to 16 decimal places but when I subtracted the tow number I didn't get zero I had =if(A1 = A2,"Equal", "Not Equal") I look at A1 and A2 out to 16 decimal palces and they were exactly the same value yet the formula gave me "Not Equal". The microprocessor in the PC has a carrry bit and an overflow bit that doesn't appear on the spreasheet that gave different results. I think your error is just a small rounding error when mutiplied a large number of times is producing a small difference in you answer. "Jolly" wrote: Joel Thanks for the tip on "Evaluate" feature. However, going through the formual it seems to have the correct input in the formula at each stage but the result is incorrect. There is obviously a relationship between discount rate and the constsant but it is the offset function that gives the error as the NPV function on its own works fine between rows and columns. Mind blowing!! I do not understand. Stuckand needing help!! "joel" wrote: I like using the evaluate formula feature in exel to find problems. click on cell where you are getting the wrong answer. then from menu tools - formula auditing - evaluate formula. If you didn't get enough information the 1st time you go throughthe evaluate formula start again by pressing Evalute. The 2nd time excel will give you more useful information. "Jolly" wrote: Hi Joel No that did not work. I removed all $ signs and that did not help. I found that using different discount rates gives a different constant error but cannot figure out the correlation between discount rate and the constant. The table below shows the discount rate v constant error 0% 1.0000 5% 0.2371 8% 0.1033 10% 0.0601 12% 0.0353 15% 0.0162 Maybe you can help. Many Thanks "joel" wrote: Try removing the dollar sign in front of the 1st $D65. "Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
#7
|
|||
|
|||
Offset function gives different answer in columns v Rows
Joel
I copied pasted values in both rows and columns so the nimbers should be identical. It is not the NPV calc. I get the in accuracy when I introduce offset function in the formula. Rita "joel" wrote: NPV is very sensitive to the number of decimal place accuracy of the numbers. If hte input numbers aren't exactly the same I would expect a small difference in the results. I don't think the inputs numbers you are using is exactly the same. I've also seen round problems out the the last deciomal place. Last week I had two numbers that were exactly the same out to 16 decimal places but when I subtracted the tow number I didn't get zero I had =if(A1 = A2,"Equal", "Not Equal") I look at A1 and A2 out to 16 decimal palces and they were exactly the same value yet the formula gave me "Not Equal". The microprocessor in the PC has a carrry bit and an overflow bit that doesn't appear on the spreasheet that gave different results. I think your error is just a small rounding error when mutiplied a large number of times is producing a small difference in you answer. "Jolly" wrote: Joel Thanks for the tip on "Evaluate" feature. However, going through the formual it seems to have the correct input in the formula at each stage but the result is incorrect. There is obviously a relationship between discount rate and the constsant but it is the offset function that gives the error as the NPV function on its own works fine between rows and columns. Mind blowing!! I do not understand. Stuckand needing help!! "joel" wrote: I like using the evaluate formula feature in exel to find problems. click on cell where you are getting the wrong answer. then from menu tools - formula auditing - evaluate formula. If you didn't get enough information the 1st time you go throughthe evaluate formula start again by pressing Evalute. The 2nd time excel will give you more useful information. "Jolly" wrote: Hi Joel No that did not work. I removed all $ signs and that did not help. I found that using different discount rates gives a different constant error but cannot figure out the correlation between discount rate and the constant. The table below shows the discount rate v constant error 0% 1.0000 5% 0.2371 8% 0.1033 10% 0.0601 12% 0.0353 15% 0.0162 Maybe you can help. Many Thanks "joel" wrote: Try removing the dollar sign in front of the 1st $D65. "Jolly" wrote: I have a set of numbers by year and am trying to get the NPV on forward basis. I used the Offset function to allow for NPV on different dates. However, the same set of numbers gives a different result if entered in columns v rows. The difference is always out by .06001. Could someone explain why or what I am doing wrong? Data in a ROW =NPV(U$64,OFFSET($D65,0,Lookforward,1,COLUMNS($D65 :$R65)-Lookforward))*(1+U$64)^0.5 Data in a Column =NPV($U58,OFFSET($Q$11,Lookforward,0,ROWS($Q$11:$Q $40)-Lookforward),1)*(1+$U58)^0.5 |
Thread Tools | |
Display Modes | |
|
|