# Improving Calculation Performance in Excel–part 1

We can build sophisticated and CPU intensive models in Excel and some of them can take hours or even days to complete.  We can speed up the model by upgrading our computer, overclocking our CPU or optimizing our models.  Some of the optimization methods can be very simple and surprisingly effectively.  Let me use an example to illustrate.

The following VBA routine runs some dump calculation for 100,000,000 times.

Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
For run = 1 To runs
Results = run * (AnnIntRate ^ (1 / 365) )
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub

It took 34 seconds to complete. I could optimize the code and reduced the processing time to 8 seconds, i.e. roughly over 400% improvement.  How? Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim DailyIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
DailyIntRate = AnnIntRate ^ (1 / 365)
For run = 1 To runs
Results = run * DailyIntRate
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub

Power ^ and Division / require more CPU cycles than Multiply * .  Since AnnualRate ^ (1 / 365) is a constant within the for loop, we can replace the Power and Division with a double variable DailyIntRate and a Multiply.

There are many ways that we can optimize our Excel models; why do we want to wait for hours if we can get it done within minutes!

My computer has a Intel Q9400 CPU inside and I am using Excel 2010 64 bit.  You may find difference results on your computer because CPU can have much different architecture.

p.s. Thank squiggler to point out that my original post has a bug in it that didn’t return the same results.

Andrew Chan is the owner and founder of ALG Inc. About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

### 3 Responses to Improving Calculation Performance in Excel–part 1

1. Squiggler says:

With Excel reliant on FPU, there is hardly any difference between Divide and multiply :-

Public Sub Main()
Dim startTime As Double
Dim endTime As Double
Dim startTime1 As Double
Dim endTime1 As Double

Dim run As Long
Dim runs As Long
Dim Results As Double

runs = 1000000

startTime = Timer
For run = 1 To runs
Results = run ^ (1 / 365)
Next
endTime = Timer

startTime1 = Timer
For run = 1 To runs
Results = run ^ (1 * 2.73972602739726E-03)
Next
endTime1 = Timer

Debug.Print ((endTime – startTime))
Debug.Print ((endTime1 – startTime1))
End Sub

I used 1* to make it fair and 1/365 pre-calculated, most of the speed saving in the example above is by removing the ^ from the loop, and since 1^(1/365) is always 1 both examples calculate different results, therefore its not really an optimization!

startTime3 = Timer
DailyRate = 1/365
For run = 1 To runs
Results = run ^ (DailyRate)
Next
endTime3 = Timer

Would be an optimization of that routine, but not much of a saving!

Debug.Print ((endTime2 – startTime2))

• Andrew Chan says:

Squiggler,

Thank you for finding the bug for me. You are right that it didn’t return the same results. Now I fixed it. But the point I want to make is still valid that we can improve Excel calculation performance with some simple but effective was.

I would do another blog to compare Division and Multiply but I believe you are right that we would not see as much improve as ^.

Thanks again.

2. GaryFPatton says: