Offset function gives different answer in columns v Rows

[Jolly]

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

 

[joel]

Try removing the dollar sign in front of the 1st $D65.

 

[Jolly]

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]

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]

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]

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]

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