If I use 03/01/05 as the start date the function works. However, if I
use 02/01/05 or 01/01/05 I get #Value!

What am I missing?

stan


Public Function TRM(compYear As Integer, sDate As Date, numUnits As
Integer, trans As Currency) As Double

Const yearAmt = 36000
Const monthAmt = 3000
Const PRN = 2

Dim tAmt As Currency

Dim calcYear As Integer
calcYear = Year(sDate)

Dim eDate As Date
eDate = CDate("12/31/" & CStr(calcYear))

Dim pAmt As Currency
Dim AllAmt As Currency

Dim numMonths As Integer
numMonths = CInt(DateDiff("m", sDate, eDate)) + 1


tAmt = numMonths * monthAmt
If compYear = calcYear Then
pAmt = numUnits * tAmt
Else
pAmt = yearAmt * numUnits

End If
If trans < pAmt Then
TRM = 0

Else
TRM = (trans - pAmt) * PRN
End If

End Function

I am getting an overflow error on the line tAmt = numMonths * monthAmt.
NumMonths is Dim'ed as an integer and monthAmt is a constant, so Excel is
using integer multiplication before trying to store the result in your tAmt
(Currency) variable - leading to an overflow when the result is 32767,
which happens when numMonths is 11 (so it bombs for Jan, Feb).

Solution: use a Long or Currency variable for numMonths, or write your
formula as tAmt = CCur(numMonths) * monthAmt

" wrote:

If I use 03/01/05 as the start date the function works. However, if I
use 02/01/05 or 01/01/05 I get #Value!

What am I missing?

stan


Public Function TRM(compYear As Integer, sDate As Date, numUnits As
Integer, trans As Currency) As Double

Const yearAmt = 36000
Const monthAmt = 3000
Const PRN = 2

Dim tAmt As Currency

Dim calcYear As Integer
calcYear = Year(sDate)

Dim eDate As Date
eDate = CDate("12/31/" & CStr(calcYear))

Dim pAmt As Currency
Dim AllAmt As Currency

Dim numMonths As Integer
numMonths = CInt(DateDiff("m", sDate, eDate)) + 1


tAmt = numMonths * monthAmt
If compYear = calcYear Then
pAmt = numUnits * tAmt
Else
pAmt = yearAmt * numUnits

End If
If trans < pAmt Then
TRM = 0

Else
TRM = (trans - pAmt) * PRN
End If

End Function