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

veryamusing

New Member
Joined
Jul 28, 2017
Messages
28
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.

79,392,71321.73.83%
79,595,40821.53.82%
79,660,47021.5113.8232%

<tbody>
</tbody>

Any ideas would be appreciated!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
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.

Code:
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.
 
Last edited:

veryamusing

New Member
Joined
Jul 28, 2017
Messages
28
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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.


A

B

C

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

<tbody>
</tbody>
Code:
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.
 
Last edited:

veryamusing

New Member
Joined
Jul 28, 2017
Messages
28
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.

Code:
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.

Code:
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
 

Forum statistics

Threads
1,141,299
Messages
5,705,573
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top