I have the following code that will create an amortization schedule, but I
need to do the following and can't figure it out.

1)  instead of creating an amortization schedule where you know exactly the
amounts the customer will be paying on a monthly basis, I need to figure out
what the current 'Payoff Amount' is for the loan.  I want to show a monthly
breakdown of what the customer has paid and how it affects the "Balance Due"
amount.

2) I put a 'TODO  in the spot where I need help below

3) Here is an example of what I want to show.
This is for a $150,000 loan at 6% for 180 months.  The scheduled amount due
each month is 1265.79

Amount Due        Amount Received        Principal Amt        Interest Amt
Balance Due
1265.79               1265.79                     515.79
750.00                149,484.21
1265.79                1265.79                    518.37
747.42               148,865.84
1265.79               1000.00                     ?????
???                      ???

4)  The question marks represent where I need help to figure the values.  If
I use the Ppmt function, I don't get the correct values... somehow I need to
know how to figure what the principal Amt and the Interest Amt are when
someone only pays a portion of the Amount Due (inmy example, the customer
only paid 1000 of the 1265.79 that was due

5) please help!  :-)

6) here is my code (I took out pieces that are not important to solving this
issue)

7) I REALLY appreciate anyone looking at this issue - I am stuck!

accountDataReader = New
dataccessLayerDAL().SelectAccountCenterRecord(variable1, variable2)
If Not accountDataReader Is Nothing Then
    accountDataReader.Read() 'read the 1 row of data

    Dim currentPaymentNumber As Integer
    Dim APR As Double
    Dim computedAPR As Double
    Dim numberOfPayments As Integer
    Dim futureValue As Double
    Dim scheduledPaymentAmount As Double
    Dim actualAmountReceived As Double
    Dim finalPaymentAmount As Double
    Dim principalPaid As Double
    Dim interestPaid As Double
    Dim principal As Double
    Dim paymentType As DueDate
    Dim purchasePrice As Double
    Dim salesTax As Double
    Dim tradeIn As Double
    Dim downPayment As Double
    Dim balanceDue As Double
    Dim firstPaymentDate As DateTime
    Dim currentPaymentDate As DateTime

    'Pull the follwing data from the database
    purchasePrice = accountDataReader("PURCHASE_PRICE")
    salesTax = accountDataReader("SALES_TAX")
    tradeIn = accountDataReader("TRADE_IN")
    downPayment = accountDataReader("DOWN_PAYMENT")
    firstPaymentDate = accountDataReader("FIRST_PAYMENT_DATE")
    currentPaymentDate = firstPaymentDate
    numberOfPayments = accountDataReader("NUMBER_OF_PAYMENTS")
    APR = CDbl(accountDataReader("INTEREST_RATE"))

    'set the values
    futureValue = 0 ' Usually 0 for a loan.
    paymentType = DueDate.EndOfPeriod

    If APR > 1 Then APR = APR / 100 ' Ensure proper form.

    computedAPR = APR / 12

    principal = (purchasePrice) + (salesTax) - (tradeIn) - (downPayment)

    balanceDue = principal

    scheduledPaymentAmount = accountDataReader("SCHEDULED_PAYMENT_AMOUNT")
    finalPaymentAmount = accountDataReader("FINAL_PAYMENT_AMOUNT")

' creating a list

Dim ListDataSource As New ArrayList

' here I am going to the database to get the actual payments the customer
mand

paymentsDataSet = New
AutoTrackerPlusDAL().SelectAccountCenterPayments(ACCOUNT_NUMBER)

_dataTable = paymentsDataSet.Tables(0)

Dim dataRow As DataRow

For Each dataRow In _dataTable.Rows
    currentPaymentNumber = currentPaymentNumber + 1

    If IsDBNull(dataRow("AMOUNT_RECEIVED")) Then
         'If the amount received is null, I assume this is the next payment
due, and I exit my logic
        Exit For
    Else
        actualAmountReceived = CDbl(dataRow("AMOUNT_RECEIVED"))
    End If

'TODO - I can't use the standard PPmt function provided by Visual Basic
because it calculates what the principal paid  is based on the  current
payment number and the actual principal... what I need is a function to
allow the scheduledPayment Amount to be higher or lower than what the
SCHEDULED payment is supposed to be (when calculating the principalPaid)


    principalPaid = PPmt(computedAPR, currentPaymentNumber,
numberOfPayments, -principal, futureValue, paymentType)
    principalPaid = (Int((principalPaid + 0.005) * 100) / 100) ' Round
principal.
    interestPaid = scheduledPaymentAmount - principalPaid
    interestPaid = (Int((interestPaid + 0.005) * 100) / 100) ' Round
interest.

    balanceDue = (balanceDue - principalPaid)

    'here I add the 3 values to an list, so later I can bind the list to a
dataSource

    ListDataSource.Add(New AccountPaymentRecord(currentPaymentNumber,
principalPaid, interestPaid))

Next

End If

Brian Cesafsky wrote:
Well I've got an idea, but I don't know enough accountancy to know
whether it's right or not. I suspect you need to talk to an accountant
to find out what the actual rules are, before you try and implement
anything.


You see, this is what gets called a 'business rule' - that is, there
isn't a definitive answer just from a code perspective. It *might* be
that for part payments, the principal and interest parts are pro rated
against what a full payment would have done - that would certainly be
easy to code, which in my experience suggests it won't be the right
answer :)


It looks like all the financial functions are geared towards handling
fixed payment schedules - this is probably because this is the only
case in which the maths is easy and generally-applicable enough to put
in a library function.

I've written parts of enormous accountancy applications, and I still
can't understand my mortgage statement. Talk to an accountant.

Close all forms but Main
Visual Basic .NET Compiler
converting string to image..??
Printer Command
ClickOnce
how to create shift-F2 function for Textbox - shortcutkey?
need Help Regex
Store encrypted password in my.settings?
VB.NET 2.0 & Application Settings..
Delete events/procedures of control if control is deleted at design time in VB.Net 2005 ?