Solved

# Needed, code for amortization table calculation

Posted on 2004-11-22
190 Views
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()

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

Application.DoEvents()
Next

Me.dgAmortization.DataSource = objDT
End Sub
0
Question by:gmahler5th

LVL 25

Accepted Solution

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

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

Application.DoEvents()
Next

Me.dgAmortization.DataSource = objDT
End Sub
0

Author Comment

AWESOME!!!!!  THANK YOU!!!
0

## Featured Post

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…