Search:

# Any quick thoughts on how to make this work?

When I try to use the function below in my worksheet I get
a "#Value!". If I change it to a sub and define all of the inputs
it works great. I'm sure the problem is some goofy oversight on my
part, possibly how I specify the function output, so any help you
can provide will be greatly appreciated.

For any of you who are interested (finance function), the function
calculates cumulative interest payments or cumulative principal
payments, depending on the intOption chosen, through a specified
payment period during the life of a loan. Actual interest is
accrued using a simple accrual (e.g. auto loan) and projected total
payments are calculated using a monthly interest period.

---------------------------------

Public Function CumulativePmt(ByVal dblOrgBal As Double, _
ByVal OrgDate As Date, ByVal FirstPmtDate As Date, _
ByVal MatDate As Date, ByVal AnnIntRate As Double, _
ByVal SumPeriod As Double, ByVal IntPeriodsYr As Double, _
ByVal MatPeriods As Double, ByVal intOption As Integer)

Dim x, i As Double
x = SumPeriod

Dim dblProjPmt As Double
Dim dblPvFactor As Double
Dim dblPrinPmt(1 To 250) As Double
Dim dblIntPmt(1 To 250) As Double
Dim dblBalance(0 To 250) As Double
Dim dblPmtDate(0 To 250) As Date
Dim dblDaysYr As Double
Dim dblCumPrin As Double
Dim dblCumInt As Double
Dim dblIntCalc As Double
Dim dblYear As Double
Dim dblMonth As Double

dblBalance(0) = dblOrgBal
dblPmtDate(0) = OrgDate
dblPmtDate(1) = FirstPmtDate

i = 0

If Int((Year(dblPmtDate(i)) - 1968) / 4) = _
(Year(dblPmtDate(i)) - 1968) / 4 Then
dblDaysYr = 366
Else
dblDaysYr = 365
End If

dblPvFactor = ((1 - (1 / (1 + AnnIntRate / IntPeriodsYr) ^
(MatPeriods))) / _
(AnnIntRate / IntPeriodsYr))

dblPvFactor = dblPvFactor * (1 + AnnIntRate / IntPeriodsYr)

dblProjPmt = dblBalance(0) / dblPvFactor

dblCumInt = 0
dblCumPrin = 0

dblProjPmt = dblBalance(0) * (1 + (AnnIntRate / dblDaysYr) * _
(DateValue(dblPmtDate(1)) - DateValue(dblPmtDate(0)))) / dblPvFactor

For i = 1 To x

If Month(dblPmtDate(i - 1)) = 12 Then
dblYear = Year(dblPmtDate(i - 1)) + 1
Else
dblYear = Year(dblPmtDate(i - 1))
End If

If Month(dblPmtDate(i - 1)) = 12 Then
dblMonth = 1
Else
dblMonth = Month(dblPmtDate(i - 1)) + 1
End If

dblPmtDate(i) = dblMonth & "/" & Day(dblPmtDate(i - 1)) _
& "/" & dblYear

dblPmtDate(1) = FirstPmtDate

If Int((Year(dblPmtDate(i)) - 1968) / 4) = _
(Year(dblPmtDate(i)) - 1968) / 4 Then
dblDaysYr = 366
Else
dblDaysYr = 365
End If

dblIntPmt(i) = dblBalance(i - 1) * (AnnIntRate / dblDaysYr) * _
(DateValue(dblPmtDate(i)) - DateValue(dblPmtDate(i - 1)))

dblPrinPmt(i) = dblProjPmt - dblIntPmt(i)

dblBalance(i) = dblBalance(i - 1) - dblPrinPmt(i)

dblCumInt = dblCumInt + dblIntPmt(i)
dblCumPrin = dblCumPrin + dblPrinPmt(i)

Next i

If intOption = 1 Then
Cumulativepmt = dblCumInt
Else
Cumulativepmt = dblCumPrin
End If

End Function

Share:

I copied your code into a spreadsheet, typed in a bunch of values,
then called your function  directly from the worksheet, and it gave me
a value. Is it possible the #Value is caused by the values you're
passing in?
This is what I entered (in first row):

dblOrgBal 275000
OrgDate 1/1/1990
FirstPmtDate 2/1/1990
MatDate 12/31/2020
AnnIntRate 0.065
SumPeriod 30
IntPeriodsYr 360
MatPeriods 360
intOption 1

In a different cell, I entered:
=CumulativePmt(A1,B1,C1,D1,E1,F1,G1,H1,I1)

And I got back:
46,353.6

Didn't find what you were looking for? Find more on Any quick thoughts on how to make this work? Or get search suggestion and latest updates.