?
Solved

Needed, code for amortization table calculation

Posted on 2004-11-22
2
Medium Priority
?
212 Views
Last Modified: 2010-04-23
I am trying to create an amortization table in a mortgage calculator that I am creating in vb.net.  My math is wrong in the amoritization calculator.  Will someone please post some code that will enable me to create an accurate datalist for my amortization table.

My full code is here:  www.stevetout.com/code.txt

The sub routine is here.

  Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculate.Click
        Dim Fmt As String
        Dim TotPmts As Integer
        Dim PVal, APR, FVal, Payment, dblMonth, dblRateMonth As Double

        'assign the values from textboxes to the variables
        'Use case statement to determine which predefined reate was chosen

         PVal = Convert.ToDouble(txtPrincipal.Text)
        TotPmts = TotPmts * 12

        If APR > 1 Then APR = APR / 100 ' Ensure proper form.
        dblMonth = TotPmts * 12
        dblRateMonth = APR / 12

        'calculate the final amount as principal + interest
        Fmt = "###,###,##0.00"   ' Define money format.
        FVal = 0   ' Usually 0 for a loan.
        Payment = PVal / ((1 - (1 + dblRateMonth) ^ -dblMonth) / dblRateMonth)

        'put the calculated amount in Payment Amount textbox
        txtAmount.Text = FormatNumber(Payment, 2, TriState.True, TriState.True, TriState.False)

        Application.DoEvents()

        'Declare variable and initialize data for amoritization table Week 4
        Dim objDT As New DataTable()
        objDT.Columns.Add("Month", GetType(Integer))
        objDT.Columns.Add("Balance", GetType(Double))
        objDT.Columns.Add("Installment", GetType(Double))
        objDT.Columns.Add("Interest Paid", GetType(Double))
        objDT.Columns.Add("Principal Paid", GetType(Double))

        MyBase.Height = 536
        Application.DoEvents()

        'Declare variables used for amortization table calculation
        Dim balanceLeft As Double = PVal '- downPayment
        Dim interestPaid As Double
        Dim LoanDuration As Integer = TotPmts '* 12
        Dim i As Integer


        'Iterate through calculations for duration of loan
        For i = 1 To LoanDuration
            Dim strRow(4) As Object

            interestPaid = balanceLeft * APR / 100 / 12
            balanceLeft = balanceLeft + interestPaid - Payment

            'Initialize Array used to populate data in the amoritization table
            strRow(0) = i
            strRow(1) = Convert.ToDouble(FormatNumber(balanceLeft, 2))
            strRow(2) = Convert.ToDouble(FormatNumber(Payment, 2))
            strRow(3) = Convert.ToDouble(FormatNumber(interestPaid, 2))
            strRow(4) = Convert.ToDouble(FormatNumber(interestPaid - Payment, 2))

            'al.Add(New repayment(i, balanceLeft, monthlyInstallment, interestPaid, interestPaid - monthlyInstallment))

            objDT.Rows.Add(strRow)
            Application.DoEvents()
        Next

        Me.dgAmortization.DataSource = objDT
    End Sub
0
Comment
Question by:gmahler5th
2 Comments
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 2000 total points
ID: 12652558
try this

    Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculate.Click
        'TestPPMT()
        Dim Fmt As String
        Dim TotPmts As Integer
        Dim PVal, APR, FVal, Payment, dblMonth, dblRateMonth, P, intr As Double

        'assign the values from textboxes to the variables
        If CT1.Checked = True Then ' Choose own rates
            APR = Convert.ToDouble(txtRate.Text)
            TotPmts = Convert.ToInt32(txtYears.Text)
        ElseIf CT2.Checked = True Then ' Predefined
            If PDR1.Checked = True Then
                APR = iRates.Item(0).Num2
                TotPmts = Convert.ToInt32(iRates.Item(0).Num1)
            ElseIf PDR2.Checked = True Then
                APR = iRates.Item(1).Num2
                TotPmts = Convert.ToInt32(iRates.Item(1).Num1)
            ElseIf PDR3.Checked = True Then
                APR = iRates.Item(2).Num2
                TotPmts = Convert.ToInt32(iRates.Item(2).Num1)
            End If
        End If

        PVal = Convert.ToDouble(txtPrincipal.Text)
        TotPmts = TotPmts * 12

        If APR > 1 Then APR = APR / 100 ' Ensure proper form.
        dblMonth = TotPmts * 12
        dblRateMonth = APR / 12

        'calculate the final amount as principal + interest
        Fmt = "###,###,##0.00"   ' Define money format.
        FVal = 0   ' Usually 0 for a loan.

        'Payment = PVal / ((1 - (1 + dblRateMonth) ^ -dblMonth) / dblRateMonth)
        Payment = Math.Abs(-Pmt(APR / 12, TotPmts, PVal, FVal,DueDate.EndOfPeriod))

        'put the calculated amount in Payment Amount textbox
        txtAmount.Text = FormatNumber(Payment, 2, TriState.True, TriState.True, TriState.False)

        Application.DoEvents()

        Dim objDT As New DataTable
        objDT.Columns.Add("Month", GetType(Integer))
        objDT.Columns.Add("Balance", GetType(Double))
        objDT.Columns.Add("Installment", GetType(Double))
        objDT.Columns.Add("Interest Paid", GetType(Double))
        objDT.Columns.Add("Principal Paid", GetType(Double))

        MyBase.Height = 536
        Application.DoEvents()

        Dim balanceLeft As Double = PVal '- downPayment
        Dim interestPaid As Double
        Dim LoanDuration As Integer = TotPmts '* 12
        Dim i As Integer

        For i = 1 To LoanDuration
            Dim strRow(4) As Object
            P = PPmt(APR / 12, i, TotPmts, -PVal, FVal, DueDate.EndOfPeriod)
            P = (Int((P + 0.005) * 100) / 100)  ' Round principal.
            intr = Payment - P
            intr = (Int((intr + 0.005) * 100) / 100)  ' Round interest

            interestPaid = balanceLeft * APR / 100 / 12
            balanceLeft = balanceLeft - P

            strRow(0) = i
            strRow(1) = Convert.ToDouble(FormatNumber(balanceLeft, 2))
            strRow(2) = Convert.ToDouble(FormatNumber(Payment, 2))
            strRow(3) = Convert.ToDouble(FormatNumber(intr, 2))
            strRow(4) = Convert.ToDouble(FormatNumber(P, 2))

            'al.Add(New repayment(i, balanceLeft, monthlyInstallment, interestPaid, interestPaid - monthlyInstallment))

            objDT.Rows.Add(strRow)
            Application.DoEvents()
        Next

        Me.dgAmortization.DataSource = objDT
    End Sub
0
 

Author Comment

by:gmahler5th
ID: 12661721
AWESOME!!!!!  THANK YOU!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question