Hi everyone,

I have written a small program that calculates daily compounded interest and
keep having a problem with an overflow error. My interest rate variables get
too big and crash the program. Isn't there a way to limit how big the
numbers get??? Please advise...


Sub interestcalculation()


Dim t As Integer

Dim v_startingprinciple As Double
Dim v_rounddownmultiple As Double
Dim v_actualinterestrate As Single
Dim v_dailyinterest As Single
Dim v_initialprinint As Double
Dim v_accumprinint As Double
Dim v_previoustest As Variant
Dim v_rate As Single
Dim v_remainingdaysinperiod As Double




'NOTES:
'FY2006 = 0.0057
'FY2007 = 0.0107
'FY2008 = 0.0145
'FY2009 = 0.0119

'Manually set variables for execution:
v_startingprinciple = 200
v_rate = 0.000057
v_remainingdaysinperiod = 26


v_rounddownmultiple =
(Application.WorksheetFunction.RoundDown(v_startin gprinciple, -2) / 100)

v_actualinterestrate = v_rounddownmultiple * v_rate

v_dailyinterest = v_startingprinciple * v_actualinterestrate

v_initialprinint = v_startingprinciple + v_dailyinterest


v_accumprinint = 0

For t = 2 To v_remainingdaysinperiod

v_rounddownmultiple =
(Application.WorksheetFunction.RoundDown(v_initial prinint, -2) / 100)

v_actualinterestrate = v_rounddownmultiple *
v_rate

v_dailyinterest = v_initialprinint *
v_actualinterestrate

v_initialprinint = v_initialprinint +
v_dailyinterest



Next t

'select the cell you'd like to output the number in BEFORE you run
the analysis!
ActiveCell.Value = v_initialprinint




End Sub

For integral number (whole numbers, no fractional part), use Longs
which have a range of approximately +/- 2 billion. For fractional
numbers, use Doubles, which have a range of approx +/- 10^308,
although Doubles only support accuracy to 15 digits. Everything else
is rounded.

There is no advantage to using Integer or Single type variables.

Note also that the compiler uses the smallest variable types it can
when doing calculations. This can lead to strange errors. For example,

Debug.Print 10 * 3300 / 10

equals 3300, and both 10 and 3300 are well within the bounds of an
Integer type. But the running this will cause an overflow error. Why?
Because the intermediate result (10*3300) does exceed the limits of an
integer. You can force the compiler to use Longs by including the CLng
function on one variable:

Debug.Print CLng(10) * 3300 / 10

or by using the Long type declaration character on one variable:

Debug.Print 10& * 3300 / 10

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 20 Mar 2009 07:10:01 -0700, Steve P
wrote:

Hi everyone,

I have written a small program that calculates daily compounded interest and
keep having a problem with an overflow error. My interest rate variables get
too big and crash the program. Isn't there a way to limit how big the
numbers get??? Please advise...


Sub interestcalculation()


Dim t As Integer

Dim v_startingprinciple As Double
Dim v_rounddownmultiple As Double
Dim v_actualinterestrate As Single
Dim v_dailyinterest As Single
Dim v_initialprinint As Double
Dim v_accumprinint As Double
Dim v_previoustest As Variant
Dim v_rate As Single
Dim v_remainingdaysinperiod As Double




'NOTES:
'FY2006 = 0.0057
'FY2007 = 0.0107
'FY2008 = 0.0145
'FY2009 = 0.0119

'Manually set variables for execution:
v_startingprinciple = 200
v_rate = 0.000057
v_remainingdaysinperiod = 26


v_rounddownmultiple =
(Application.WorksheetFunction.RoundDown(v_starti ngprinciple, -2) / 100)

v_actualinterestrate = v_rounddownmultiple * v_rate

v_dailyinterest = v_startingprinciple * v_actualinterestrate

v_initialprinint = v_startingprinciple + v_dailyinterest


v_accumprinint = 0

For t = 2 To v_remainingdaysinperiod

v_rounddownmultiple =
(Application.WorksheetFunction.RoundDown(v_initia lprinint, -2) / 100)

v_actualinterestrate = v_rounddownmultiple *
v_rate

v_dailyinterest = v_initialprinint *
v_actualinterestrate

v_initialprinint = v_initialprinint +
v_dailyinterest



Next t

'select the cell you'd like to output the number in BEFORE you run
the analysis!
ActiveCell.Value = v_initialprinint




End Sub