473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

amortization... kinda

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(vari able1, 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(A CCOUNT_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


Jun 30 '06 #1
1 1576

Brian Cesafsky wrote:
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.
--
Larry Lard
Replies to group please
When starting a new topic, please mention which version of VB/C# you
are using

Jul 3 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: ChocoboMog123 | last post by:
What's wrong with line 8 in this code? x=1 while 1==1: x=x+1 y=range(1,x) z=0 q=9 for count in y: q=x%y if q==0:
6
by: Dave Benjamin | last post by:
Hey good people, I've been doing a lot of simultaneous Jython and CPython programming lately, and just wanted to say, with no intended ill will toward any of the individuals who have been...
2
by: Ville Vainio | last post by:
Came across this via daily python url (which everybody reads anyway :) http://boo.codehaus.org/ Read the manifesto at http://boo.codehaus.org/BooManifesto.pdf Perhaps that .NET / Mono...
1
by: F. GEIGER | last post by:
I've a task writing huge amounts of data into a file. I want to display them in my wxPython GUI now. As there are really many data, loading them into mem is not an option. Therefore I'd like to...
10
by: David P. Jessup | last post by:
Lets see if I can explain this so I can get a good answer =) I'm trying to speed up a file search via FSO. I have a database that contains the exact path where files can be found and an "index"...
1
by: middletree | last post by:
I've been using CDO to send an email with an Intranet app I have. Can't for the life of me remember where I got this code: .Importance = 1 ' Normal .BodyFormat = 1 ' Plain text .MailFormat =...
4
by: Dan | last post by:
I've run into an interesting problem, and seemed to have stumped 3 newsgroups and 2 other forums. For some reason when I try to insert a record into a SQL table that has a Text column, the...
1
by: Kelvin | last post by:
hi everyone... i discover this accentally when i was debugging a program... here is the problem( or maybe not :) ) *************CODE******************* #include <iostream> using namespace...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.