Have to repeatedly enter same value into cell to manually force convergence...

[veryamusing]

I have a custom function to calculate NPV, which is called every time one of several cells changes (to avoid circular references, since the user doesn't want to use iterative calculation). The initial result is always slightly off; however, if I reenter the same value in any of the target cells, the value converges to the correct value after five to ten tries.

The only fix I could thing of was looping the action of editing / entering the target cell, but doing this in VBA doesn't cause the sheet to recalculate. Initially I thought the issue was caused by setting enable events to false in the worksheet change sub, but that doesn't seem to make any difference. I think the problem is the fact that NPV is based on a rate of interest that changes based on the weighted average life, which is based on NPV! So, NPV >> WAL >> Rate, NPV >> WAL >> Rate, etc. That's why an earlier version of this used iterative calculation.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]79,392,713[/TD]
[TD]21.7[/TD]
[TD]3.83%[/TD]
[/TR]
[TR]
[TD]79,595,408[/TD]
[TD]21.5[/TD]
[TD]3.82%[/TD]
[/TR]
[TR]
[TD]79,660,470[/TD]
[TD]21.511[/TD]
[TD]3.8232%[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas would be appreciated!

 

[thisoldman]

Entering a value into a cell through VBA doesn't dirty the cell to force a recalculation.

There's a boolean worksheet property, EnableCalculation, that might help. If you change the value from False to True, all the worksheet cells are made dirty. In automatic calculation mode this seems to force a recalculation of the worksheet. Of course, if you're in manual mode you have to force the recalculation.

With Worksheets(1)
    For i = 1 To 5
        .EnableCalculation = False
        .EnableCalculation = True
        
        ' Uncomment if in manual mode to force recalculation.
        ' .Calculate
    Next i
End With

There's a Range.Calculate and a Range.CalculateRowMajorOrder, but unless the cells are made dirty, they don't appear to force a recalculation. I'd have to play with them before I'm sure of that.

 

[veryamusing]

Thanks for your reply! I will try that and report back.

 

[veryamusing]

Unfortunately, that wasn't the trick. Going to keep working on it. If anyone out there has an idea, I'm happy to try it. Thanks!

 

[joeu2004]

I have a custom function to calculate NPV [....] The initial result is always slightly off; however, if I reenter the same value in any of the target cells, the value converges to the correct value after five to ten tries. The only fix I could thing of was looping the action of editing / entering the target cell, but doing this in VBA doesn't cause the sheet to recalculate.

Normally, it does, if the Excel is in automatic calculation mode. Usually, our problem is just the opposite: how to avoid the recalculation.

In any case, it would help us help you if you shared the details of your iterative calculation and worksheet design. I prefer that you upload that an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. Test the download URL first, being careful to log out the file-sharing website. (With box.net/files, ignore any preview errors, and just download the file.)

Note: Some participants to object to downloading example Excel files. But in this case, the details might be too much to include in a posting. Suggestion: do both, doing the best you can with the latter.

If your cash flow model fits the requirements for Excel IRR (XIRR), you might consider the following alternative.

[TABLE="class: grid, width: 475"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD="align: right"]

B​

[/TD]
[TD="align: right"]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CF0
[/TD]
[TD="align: right"]-$100,000
[/TD]
[TD="align: right"]$70,000
[/TD]
[TD]target NPV (input)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CF1
[/TD]
[TD="align: right"]$14,124
[/TD]
[TD="align: right"]1.040357%
[/TD]
[TD]target discount rate (output)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CF2
[/TD]
[TD="align: right"]$11,891
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CF3
[/TD]
[TD="align: right"]$15,710
[/TD]
[TD="align: right"]-$170,000
[/TD]
[TD]IRR CF0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CF4
[/TD]
[TD="align: right"]$12,940
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]CF5
[/TD]
[TD="align: right"]$17,724
[/TD]
[TD][/TD]
[TD]PROOF OF CONCEPT:
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]CF6
[/TD]
[TD="align: right"]$16,671
[/TD]
[TD="align: right"]$70,000
[/TD]
[TD]calculated NPV(target rate)
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]CF7
[/TD]
[TD="align: right"]$15,171
[/TD]
[TD="align: right"]-5.82E-11
[/TD]
[TD]calculated NPV - target NPV
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]CF8
[/TD]
[TD="align: right"]$12,879
[/TD]
[TD="align: right"]8.32E-16
[/TD]
[TD]relative error
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]CF9
[/TD]
[TD="align: right"]$14,634
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]CF10
[/TD]
[TD="align: right"]$19,957
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]CF11
[/TD]
[TD="align: right"]$19,560
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]CF12
[/TD]
[TD="align: right"]$10,737
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
C2: =IRR([B][COLOR=#ff0000]([/COLOR][/B]C4,B2:B13[B][COLOR=#ff0000])[/COLOR][/B])
C4: =B1-C1
C7: =B1+NPV(C2,B2:B13)
C8: =C7-C1[B][COLOR=#ff0000]-0[/COLOR][/B]
C9: =ABS(C8/C1)

Note the extra set of parentheses in the IRR usage in C2. This is a special type of range (range union) that few Excel functions support; IRR is one of them. (But not XIRR.)

C7:C9 are not part of the solution. They simply demonstrate the correctness of the result.

The redundant -0 in C8 works around a dubious Excel feature that replaces the actual arithmetic result with exactly zero if the result of the subtraction is "close enough" to zero.

An infinitesimal difference is usually unavoidable. It is the result of anomalies of the binary floating-point representation of decimal fractions (usually not exact) and arithmetic.

Improvement.... The Excel IRR iterative algorithm is flawed, IMHO. I suspect it uses an approximate derivative instead of an exact derivative of NPV, which is computable. Consequently, Excel IRR often returns a #NUM error and requires a "guess" unnecessarily; and the resulting discount rate often results in a considerable relative error when calculating NPV. You might implement your own IRR algorithm in VBA.

 

[veryamusing]

Entering a value into a cell through VBA doesn't dirty the cell to force a recalculation.

There's a boolean worksheet property, EnableCalculation, that might help. If you change the value from False to True, all the worksheet cells are made dirty. In automatic calculation mode this seems to force a recalculation of the worksheet. Of course, if you're in manual mode you have to force the recalculation.

With Worksheets(1)
    For i = 1 To 5
        .EnableCalculation = False
        .EnableCalculation = True
        
        ' Uncomment if in manual mode to force recalculation.
        ' .Calculate
    Next i
End With

There's a Range.Calculate and a Range.CalculateRowMajorOrder, but unless the cells are made dirty, they don't appear to force a recalculation. I'd have to play with them before I'm sure of that.

I tried this on my home setup, and didn't get the results I was hoping for. But today, on my work setup, I used the below code and it functions exactly how I needed. Big thank you to @thisoldman for your help!

Since the client wished to avoid iterative calculation, an iterative solver function I wrote wasn't working quite right. So I re-wrote that today too, and the results are great.

Dim i As Long    With ThisWorkbook.Worksheets("Sheet1")
        For i = 1 To 10
            .EnableCalculation = False
            .EnableCalculation = True
            Range("NPV").Value = NetPresentValue(Range("SPREAD").Value, Range("ICUR").Value, Range("TERM").Value, STUB)
            .Calculate
            'Debug.Print i, Range("NPV").Value
        Next
    End With