amortization... kinda

[Brian Cesafsky]

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

 

[Larry Lard]

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

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.

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

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

'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)

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.