VBA in Access code for Mortgage repayment Calculator - hickups!

Question

Access07-Jon 0 Newbie Poster

14 Years Ago

Hello , I am working on getting this Mortgage repayment calculation code to work for my Access07DB, the code for was copied from some 2006 post on a vb forum. But some things I cant get to work
I will put the bad lines in red

Option Compare Database

Private Sub CmdMortgageCalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdMortgageCalc.Click
'Calculate the Monthly Payment'
Dim dblAmount, dblMonthlyRate, dblMonths, dblMonthlyPayment As Double
'Convert input values to numeric values'
dblAmount = CDbl(txtAmount.Text)
dblMonthlyRate = CDbl(txtRate.Text) / 100 'allows interest rate to be entered whole number'
dblMonths = CDbl(txtYears.Text)
'Format input values'
txtAmount.Text = FormatCurrency(dblAmount)
txtRate.Text = FormatPercent(dblMonthlyRate)
txtYears.Text = FormatNumber(dblMonths)
'Calculate Payment '
'Results for listbox should be new loan balance and interest rate'
Dim PVal, FVal, mPayments As Integer
Dim APR, iPayment, TotInt As Double
Dim pPayment, TotPrincipal, dblBalance As Double
PVal = dblAmount
FVal = 0
APR = dblMonthlyRate / 12
mPayments = dblMonths * 12
dblBalance = dblAmount
For period As Integer = 1 To mPaymentsiPayment = IPmt(APR, period, mPayments, -PVal, FVal, 1)
pPayment = PPmt(APR, period, mPayments, -PVal, FVal, 1)
lstLoanInterest.Items.Add (FormatCurrency(TotPrincipal).PadRight(25) & FormatCurrency(iPayment).PadRight(25) & FormatCurrency(dblBalance).PadLeft(25))
Debug.WriteLine(" Pmnt #"  & period & " -> Principle ="  & FormatCurrency(TotPrincipal).PadRight(14) & " Int Paid for Payment #"  & period & "  is "  & FormatCurrency(iPayment) & "  Bal. ="  & FormatCurrency(dblBalance))
TotInt = TotInt + iPayment
TotPrincipal = TotPrincipal + pPayment
Dim monthlyPayment As Double
monthlyPayment = CDbl(iPayment + pPayment)
dblBalance = dblBalance - (monthlyPayment - iPayment)
Next
lstLoanInterest.Items.Add (" _______________________________")
lstLoanInterest.Items.Add (" Total interest paid: " & FormatCurrency(TotInt))
lstLoanInterest.Items.Add (" _______________________________")
lstLoanInterest.Items.Add (" Total Paid after: " & mPayments & "  Payments = " & FormatCurrency(TotInt + TotPrincipal))
lstLoanInterest.Items.Add (" _______________________________")
lstLoanInterest.Items.Add (" Last Payment is an interest payment of course, and it is " & FormatCurrency(iPayment))
'Format answer '
dblMonthlyPayment = CDbl(iPayment + pPayment)
txtMonthlyPayment.Text = FormatCurrency(dblMonthlyPayment)
End Sub

(bad rolf harris impersination) "Cenya tell wat it is yet?"

would really really apreciate if someone can see the glitches
thanks
Jon

microsoft-access

2 Contributors
9 Replies
1K Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by Access07-Jon

Dan08 8 Junior Poster

14 Years Ago

Can you give us the link to the post on that vb forum which you found the code? Can you tell us what the code is supposed to do?

Dan08 8 Junior Poster

14 Years Ago

In the link that you gave us above, it apparently has the answer, have you tried it out? by the way the code that wa there is:

Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculate.Click
        ’Calculate the Monthly Payment'
        Dim dblAmount, dblMonthlyRate, dblMonths, dblMonthlyPayment As Double
        ’Convert input values to numeric values'
        dblAmount = CDbl(txtAmount.Text)
        dblMonthlyRate = CDbl(txtRate.Text) / 100 ’allows interest rate to be entered whole number'
        dblMonths = CDbl(txtYears.Text)
        ’Format input values'
        txtAmount.Text = FormatCurrency(dblAmount)
        txtRate.Text = FormatPercent(dblMonthlyRate)
        txtYears.Text = FormatNumber(dblMonths)
        ’Calculate payment'
        ’Results for listbox should be new loan balance and interest rate'
        Dim PVal, FVal, mPayments As Integer
        Dim APR, iPayment, TotInt As Double
        Dim pPayment, TotPrincipal, dblBalance As Double
        PVal = dblAmount
        FVal = 0
        APR = dblMonthlyRate / 12
        mPayments = dblMonths * 12
        dblBalance = dblAmount
        For period As Integer = 1 To mPayments
            iPayment = IPmt(APR, period, mPayments, -PVal, FVal, 1)
            pPayment = PPmt(APR, period, mPayments, -PVal, FVal, 1)
            lstLoanInterest.Items.Add(FormatCurrency(TotPrincipal).PadRight(25) & FormatCurrency(iPayment).PadRight(25) & FormatCurrency(dblBalance).PadLeft(25))
            Debug.WriteLine(" Pmnt #"  & period & " -> Principle ="  & FormatCurrency(TotPrincipal).PadRight(14) & " Int Paid for Payment #"  & period & "  is "  & FormatCurrency(iPayment) & "  Bal. ="  & FormatCurrency(dblBalance))
            TotInt = TotInt + iPayment
            TotPrincipal = TotPrincipal + pPayment
            Dim monthlyPayment As Double = CDbl(iPayment + pPayment)
            dblBalance = dblBalance - (monthlyPayment - iPayment)
        Next
        lstLoanInterest.Items.Add(" _______________________________" )
        lstLoanInterest.Items.Add(" Total interest paid: "  & FormatCurrency(TotInt))
        lstLoanInterest.Items.Add(" _______________________________" )
        lstLoanInterest.Items.Add(" Total Paid after: "  & mPayments & "  Payments = "  & FormatCurrency(TotInt + TotPrincipal))
        lstLoanInterest.Items.Add(" _______________________________" )
        lstLoanInterest.Items.Add(" Last Payment is an interest payment of course, and it is "  & FormatCurrency(iPayment))
        ’Format answer'
        dblMonthlyPayment = CDbl(iPayment + pPayment)
        txtMonthlyPayment.Text = FormatCurrency(dblMonthlyPayment)
End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Access07-Jon 0 Newbie Poster · Answer 1 · 2010-03-25T15:20:32+00:00

Hi Dan, the code calculates mortgage loan repayments with capital and interest repayments paid over the term of the mortgage reducing the total amount owed to zero over the term of the mortgage.

Input variables:
Interest Rate (percent)
Mortgage Term (years)
Principle Amount Borrowed (Currency)

should output the following
Monthly loan repayment figure (capital and interest)
Reducing balance of the loan over the term

here is a working calculator spreadsheet in excell
http://www.vertex42.com/Calculators/home-mortgage-calculator.html

I can seem to find the page where i got the vba from just now, will post it again when i find it.

but what I want to do is build in a mortgage repayment calculator into my access DB. dont want to use excell, most of these calculators on the web are in excell, nicely done, but I want it in access.

Access07-Jon 0 Newbie Poster · Answer 2 · 2010-03-25T15:26:58+00:00

Found the link, (a 2006 post)

http://www.a1vbcode.com/a1vbcode/vbforums/Topic11120-9-1.aspx

Access07-Jon 0 Newbie Poster · Answer 3 · 2010-03-26T15:11:20+00:00

hi Dan, Yes I am using this code, I was trying to get it to run last night on the laptop at home,

(also , I think this code im using from the other post Was the solution to the posed question at the time - cant see further answers on that post )

I made a form with the fields:
TxtAmount:
TxtYears:
TxtMonthlyPayment:
1stLoanInterest: (list box)

Im wondering is this code for VBA ? as the post said the project was vb.net - as compile error finds a few Invalid qualifiers. I really dont know the difference as have only started in VBA for the past couple of months

the code in visual basic window I have made all the 'comments correct as had wrong '. but the bits I cant figure out such as

btnCalculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculate.Click

access vb does not seem to like this in the () or the Handles BtnCalculate.click

or the line begining
For period As Integer - 1 To mPayments

or the line
Debug.WriteLine(" Pmnt #" .......
the rest of the code shows no error

i tried googling Debug.WriteLine to no avail and just downloaded a access vba for dummies book, yet its quite basic does not cover this stuff. although a pretty good reference, I want to get my hands on the VBA Developers handbook - but last time i looked in amazon its about £100 !

Access07-Jon 0 Newbie Poster · Answer 4 · 2010-03-26T15:20:42+00:00

found something on Items.Add

How to: Add and Remove Items with the Windows Forms ListView Control
The process of adding an item to a Windows Forms ListView control consists primarily of specifying the item and assigning properties to it. Adding or removing list items can be done at any time.

To add items programmatically
Use the Add method of the Items property.

Visual Basic Copy Code ' Adds a new item with ImageIndex 3
ListView1.Items.Add("List item text", 3)

side thought:
maybe this would work better than vbnewline in my RTF combo problem ??

on the WriteLine Method in this code
.NET Framework Class LibraryDebug..::.WriteLine Method
Writes information about the debug to the trace listeners in the Listeners collection.

.net ? so is this method in VBA ? help :0|

Access07-Jon 0 Newbie Poster · Answer 5 · 2010-03-30T14:21:27+00:00

I have figured out this now. you dont need all this code to calculate a mortgage repayment, within access objects there are a set of financial functions built in. Use the pmt() function to calc loan or mortgage repayment. really easy, case closed

Dan08 8 Junior Poster · Answer 6 · 2010-04-01T18:17:51+00:00

Im sorry I am in holidays and I just saw the message from daniweb on my iPhone. Well, I'm glad you made by yourself, sometimes pushing ourselves harder, doing things by ourself, help us to understand how things work. Anyway, you wanted to know the difference between VB and VBA. VB stands for Visual Basics, and is basically Visual Basics in it self. VBA stands for Visual Basics (for) Applications, and the code has to be slightly different because it will control an application. The applications are meant to have all fuctionality, with all the buttons and everything, but because that would cost more money and time to do, they allow a modified version of Visual Basics to help. The main applications I am talking about are Access and Excel, because they're one of the most used programs to make all sorts of things, on the otherhand, to get more from it, we can use VBA. The difference between VB and VBA is really hard to explain, but hope it helps you to understand in someway, and I just will say this one more time, I'm glad you made it yourself. Dan08.

Access07-Jon 0 Newbie Poster · Answer 7 · 2010-04-01T22:29:06+00:00

yep, got it working and works well. heres a pic of it
http://i39.tinypic.com/289vekp.png