Excel Financial Function Cumprinc() In Access

[FARAZ QURESHI]

I have converted my databse from excel to access however upon designing a
form I am facing the following problem:

How can I readily / automatically calculate the cumulative principal that is
paid / matured within two dates? as Excel helps on this type with its
outstanding function =CumPrinc( )

 

[James A. Fortune]

I have converted my databse from excel to access however upon designing a
form I am facing the following problem:

How can I readily / automatically calculate the cumulative principal that is
paid / matured within two dates? as Excel helps on this type with its
outstanding function =CumPrinc( )

That's an interesting problem. I enjoy converting Excel worksheet
functions into Access functions. If no one posts a solution within the
next few days I'll give it a try so that if I'm successful you won't
need a reference to the Excel Object Library in order to be able to
calculate it.

James A. Fortune
(e-mail address removed)

In spite of Pope Silvester II's book (AD 999) and Fibonacci introducing
Arabic Numerals (a.k.a. Indian Numbers or Hindu-Arabic numerals) into
Europe in his Book of Calculation (Liber Abaci, AD 1202), Roman Numerals
were still used throughout Europe until after the Reformation when
suddenly the ability to charge interest on loans gave Arabic numerals an
edge over the abacus for financial calculations. -- PBS

 

[James A. Fortune]

I have converted my databse from excel to access however upon designing a
form I am facing the following problem:

How can I readily / automatically calculate the cumulative principal that is
paid / matured within two dates? as Excel helps on this type with its
outstanding function =CumPrinc( )

This is what I have tried so far. It work for the examples in the Excel
97 help file:

'--begin module code--
Public Function CumulativePrinciple(dblIntRatePerPeriod As Double,
NPeriods As Integer, PresentValue As Double, StartPeriodNumber As
Integer, EndPeriodNumber As Integer, intTimingType As Integer) As Double
Dim dblSum As Double
Dim intI As Integer

dblSum = CDbl(0)
For intI = StartPeriodNumber To EndPeriodNumber
dblSum = dblSum + PPmt(dblIntRatePerPeriod, intI, NPeriods,
PresentValue, , intTimingType)
Next intI
CumulativePrinciple = dblSum
End Function
'---end module code---

There may be an easier way to do this. PPmt() is one of the built-in
financial functions in Access. Also, I didn't put in the checks for
situations that cause #NUM! values. You'll need to correct the line
wrap also. Maybe I'll play with this some more to see how I would do it
without the PPmt() function.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

It work for the examples in the Excel

I must be tired .

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

wrap also. Maybe I'll play with this some more to see how I would do it
without the PPmt() function.

The problem with relying on the PPmt function is that it doesn't scale
upwards well (depending on what functions the upward platform has).
I'll explain what I did in a backwards fashion.

Here's a version (one-liner) that doesn't rely on the PPmt function:

Public Function CumulativePrinciple(dblIntRatePerPeriod As Double,
NPeriods As Integer, PresentValue As Double, StartPeriodNumber As
Integer, EndPeriodNumber As Integer, intTimingType As Integer) As Double
CumulativePrinciple = ((1 + dblIntRatePerPeriod) ^ (EndPeriodNumber + 1
- intTimingType) - (1 + dblIntRatePerPeriod) ^ (StartPeriodNumber -
intTimingType)) * (PresentValue - PresentValue * (dblIntRatePerPeriod *
(1 + dblIntRatePerPeriod) ^ (NPeriods - intTimingType)) / ((1 +
dblIntRatePerPeriod) ^ (NPeriods - intTimingType) - 1) /
dblIntRatePerPeriod)
End Function

Multiple line version:
Public Function CumulativePrinciple(dblIntRatePerPeriod As Double,
NPeriods As Integer, PresentValue As Double, StartPeriodNumber As
Integer, EndPeriodNumber As Integer, intTimingType As Integer) As Double
Dim dblNperMult As Double
Dim dblA As Double
Dim dblStartMult As Double
Dim dblEndMult As Double

dblNperMult = (1 + dblIntRatePerPeriod) ^ NPeriods
dblA = PresentValue * (dblIntRatePerPeriod * dblNperMult) / (dblNperMult
- 1) 'Annuity equivalent to PV
dblStartMult = (1 + dblIntRatePerPeriod) ^ (StartPeriodNumber -
intTimingType)
dblEndMult = (1 + dblIntRatePerPeriod) ^ (EndPeriodNumber + 1 -
intTimingType)
CumulativePrinciple = (dblEndMult - dblStartMult) * (PresentValue - dblA
/ dblIntRatePerPeriod)
End Function

I took a guess at how to apply the intTimingType. I can't run the
CUMPRINC function in Excel because I don't have the analysis pack so I
would love to see some sample outputs with intTimingType = 1 to see if
my guess was correct. I'd even be interested in seeing if the function
works on more examples than what are given in the Excel help when
intTimingType = 0. I started by using the following function:

Public Function PRj(PresentValue As Double, dblIntRatePerPeriod As
Double, NPeriods As Integer, PeriodNumber As Integer) As Double
Dim dblNperMult As Double
Dim dblPeriodNumberMult As Double
Dim dblA As Double

dblNperMult = (1 + dblIntRatePerPeriod) ^ NPeriods
dblA = PresentValue * (dblIntRatePerPeriod * dblNperMult) / (dblNperMult
- 1)
dblPeriodNumberMult = (1 + dblIntRatePerPeriod) ^ PeriodNumber
PRj = PresentValue * dblPeriodNumberMult + dblA * (1 -
dblPeriodNumberMult) / dblIntRatePerPeriod
End Function

which gives the Principle remaining to be paid after PeriodNumber
periods have passed. The difference, after adjusting to include the
entire ending period, gives the amount of principle paid within the
range of periods. That function was obtained by solving the following
recurrence relation:

PR(j+1) = PR(j) * (1 + i) - A

or, previously,

PR(j+1) = PR(j) - (A - PR(j) * i)

I.e., the monthly payment minus the interest on the prior Principle
remaining is the amount to subtract to get the new Principle remaining.

James A. Fortune
(e-mail address removed)