0

I have a circular reference with embedded VLOOKUP function which I solve using the iterative calculation. I have two adjacent columns that have same logic behind the circular reference and the iterative calculation is checked.

However the results that I get are not consistent: when I change the amount in the right column it gives different results depending on whether the previous value was higher or lower from the benchmark number.

Link to the workbook: https://www.dropbox.com/s/3ju59oknnerklf7/Circular_reference_with_VLOOKUP.xlsx

Any ideas as to why is that? Many thanks.

user3574471
  • 25
  • 4
  • The blog posting system identified as a link just a part of it due to space character in the file name. Replaced with another link without spaces: https://www.dropbox.com/s/0rpqnran0kyd7qz/circular_reference_with_VLOOKUP_non_consistent.png – user3574471 Apr 27 '14 at 07:21

1 Answers1

0

Very difficult to know without seeing the spreadsheet. The formulas you put for example doesn't seem to be consistent with the results you have in column D and E. The range of H5:K19, with a col_index_num of 3, should select the Spread, not the Rating.

Then because you haven't defined a TRUE or FALSE value in your Vlookup, it's defaulting to TRUE. Assigning the [range lookup] variable to TRUE will default to the closest lowest value it matches and in this case you should have a table_array of G5:J19.

Joel
  • 65
  • 1
  • 7
  • Thanks for the pointing out that I put the link to screenshot rather than to workbook. I fixed the top post. In any case, the link to workbook is as follows: https://www.dropbox.com/s/3ju59oknnerklf7/Circular_reference_with_VLOOKUP.xlsx – user3574471 Apr 29 '14 at 20:06
  • Which cell are you are you changing? The Level of Debt (E5), EBIT (D2), or RFR(D3). I think you need to explain this a little more. I'm getting the same results from both columns D & E, when I change the debt level. Can you post an example of it not working? – Joel Apr 30 '14 at 06:20
  • Column D, namely D5:D9 is a becnhmark. I change the E5. You can set for instance the E5 value to 30, then you get Implied rating A2/A and Kd = 5%. Then set the E5 to 55 (just like the value of D5, benchmark). Implied rating = B3/B-, kd= 11%. So far, so gut. D5 = E5 -> D9 = E9. Now, change the E5 to 70. As a result, E9 = Ca2/CC. NOW, the punchline. Set E5 back to 55 (the benchmark value). E9 (Implied rating) = Caa/CCC. – user3574471 May 01 '14 at 07:39
  • It looks like the calculation of E9 depends on previous calculations. If I change the value in E5 from below (30->55) then I get one result. If I change the value in E5 from above (70->55) then I get another one. – user3574471 May 01 '14 at 07:39