How to get equal results when doing arithmetic operations vba/excel [Double variable precision]

Question

I am trying to get equal result of two exact calculations which are computed in a cell formula and the other one with a UDF:

Function calc()
  Dim num as Double
  num = 30000000 * ((1 + 8 / 100 / 365) ^ 125)
  calc = num
End Function

Result of the calculation is different

A1 = 30000000 * ((1 + 8 / 100 / 365) ^ 125) not equal to A2 = calc()

We can test it with =if(A1=A2, TRUE, FALSE) which is false. I do understand that it has something to do with data types in vba and executing cell formula. Do you know how to make calculations to from vba function(s) and excel cell field(s) to render same result?

Answer 1

So, the calculation in application excel and the calculation in vba are presenting different outputs (what you've presented, with format displaying 20 decimal places):

As such, you would see false when comparing them. You will need to round() or format() to truncate the calculation at a level that is appropriate. E.g.:

calc = round(num,4)
calc = format(num,"0.###0")

The reason this is occurring is because of the inherent math you're using, specifically, ((1 + 8 / 100 / 365) ^ 125), and how that is being truncated/rounded in the allocated memory to each part of the calculation, which differs in VBA and in-application Excel.

---

Edit: Final image with the VBA changes I'd suggested:

Answer 2

Explanation
Double Data type seems to have flaws being "precise" after the "nth" digit. This is stated as well in the documentation
Precision. When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and the Mod operator.
Troubleshooting
It seems that is the case here: I set up the value from the division on a cell and the division as formula in another one, although excel interface says there are not differences, when computing that value again, the formula on the sheet seems to be more precise.

Actual result

Further thoughts
It seems that is limited by the data type itself, if precision is not an issue, you may try to round it. If it is critical to be as precise as possible, I would suggest you to connect with an API to something that is able to handle more precision. In this scenario, I would use xlwings to use python.