Amit Dixit's Blog: October 2011

Following article demonstrates an Financial IRR Calculator where I have implemented an Excel/OpenOffice like GoalSeek function.
Before going through the Calculator we'll first understand the requirement for such a calculator.

My user requirement was:

Base IRR Calculation date should start from 8 days from Date of disbursement (DOD).
If the DOD is less than equal to the 15th of the current month then the First EMI date should be 5th of the next month and subsequent EMI should be 5th of the every month.
If the DOD is greater than 15th of the current month then the First EMI date should be 5th of the alternate month and subsequent EMI should be 5th of every month.ie. if DOD is 17-Aug then the First EMI is 5-Oct
The cash flow can be structured or can be equated.
Equated means the customer will pay the same EMI amount throught his loan tenure.
Structured means the customer will have a variable EMI for different periods.

Also we need to keep in mind that for a financial organisation each day is very crucial as far as interest is concerned.

The normal IRR() function does not consider this. It only considers a series of cash flows and calculates the IRR.

Hence we'll have to consider every aspect ie.DOD, interest paid, Principal balance, principal repay, moratorium period etc.

This is how my calculator look like ie. the value which needs to be passed.

To Calculate the IRR following are the things we need to calculate First.

Date of First EMI and date Of last EMI.
EMI Amount(Only for the Equated Cases).
For structured cases EMI amount needs to be manually added in the Grid provided along with the terms for those EMI
Advance EMI Amount depending the no. of Advance EMI.

Following are the functions which calculates the various IRR.

#Region "Global Variable"

Dim EMI As Decimal = 0.2F

Dim Amount As Decimal = 0.2F

Dim Tenure As Int32

Dim NoAdvEMI As Int32

Dim AdvEMIAmt As Decimal = 0.2F

Dim ROI As Decimal = 1.0E-20F

Dim NoOfIterations As Integer

#End Region

#Region "Calculations OF EMI amount First EMI date and last EMI Date"

Private Sub dtpDisb_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpDisb.ValueChanged

Dim dy As String = "05"

Dim mt As String = dtpDisb.Value.Month

Dim yr As String = dtpDisb.Value.Year

Try

If dtpDisb.Value.Day > 15 Then

mt = mt + 2

Else

mt = mt + 1

End If

Dim i As Integer

Integer.TryParse(txtTenure.Text, i)

txtStdate.Text = Format(Convert.ToDateTime(dy & "-" & mt & "-" & yr), "dd-MMM-yyyy")

txtEnddate.Text = Format(Convert.ToDateTime(dy & "-" & mt & "-" & yr).AddMonths(i - 1), "dd-MMM-yyyy") '+ CInt(txtTenure.Text)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

Private Sub txtTenure_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenure.TextChanged

Dim dy As String = "05"

Dim mt As String = dtpDisb.Value.Month

Dim yr As String = dtpDisb.Value.Year

Try

If dtpDisb.Value.Day > 15 Then

mt = mt + 2

Else

mt = mt + 1

End If

Dim i As Integer

Integer.TryParse(txtTenure.Text, i)

txtStdate.Text = Format(Convert.ToDateTime(dy & "-" & mt & "-" & yr), "dd-MMM-yyyy")

txtEnddate.Text = Format(Convert.ToDateTime(dy & "-" & mt & "-" & yr).AddMonths(i - 1), "dd-MMM-yyyy") '+ CInt(txtTenure.Text)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

Private Sub txtRoi_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRoi.TextChanged

Try

ROI = Convert.ToDouble(Convert.ToDouble(txtRoi.Text) / 100)

Tenure = Convert.ToInt32(txtTenure.Text)

Amount = Convert.ToInt32(txtLoanAmount.Text)

EMI = (Amount + ((Amount * ROI * Tenure) / 12)) / Tenure

txtEmi.Text = Math.Round(EMI, MidpointRounding.ToEven)

txtAdvEMIAmt.Text = Math.Round(EMI * NoAdvEMI, MidpointRounding.ToEven)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

Private Sub ddmAdvEmi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddmAdvEmi.SelectedIndexChanged

Tenure = Convert.ToInt32(txtTenure.Text)

NoAdvEMI = Convert.ToInt32(ddmAdvEmi.Text)

txtAdvEMIAmt.Text = Math.Round(EMI * NoAdvEMI, MidpointRounding.ToEven)

AdvEMIAmt = Math.Round(EMI * NoAdvEMI, MidpointRounding.ToEven)

Tenure = Tenure - NoAdvEMI

End Sub

Private Sub ddmIsStruct_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ddmIsStruct.Validating

'If ddmIsStruct.Text = "YES" And txtNoOfStruct.Text = "" Then

' ErrorProvider1.SetError(txtNoOfStruct, "Please Enter No Of Structure")

' e.Cancel = False

'Else

' ErrorProvider1.SetError(txtNoOfStruct, "")

'End If

End Sub

Private Sub ddmIsStruct_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddmIsStruct.SelectedIndexChanged

If ddmIsStruct.Text = "NO" Then

txtEmi.ReadOnly = True

txtNoOfStruct.Text = ""

txtNoOfStruct.ReadOnly = True

Else

txtEmi.ReadOnly = False

End If

End Sub

#End Region

Public Function EquatedIRR() As Double

Dim noEmi As Integer = 1

Dim stDate_Orginal As Date

Dim sCount As Integer

Dim interestPaid As Decimal = 1.0E-20F

Dim PrinBal As Decimal = 1.0E-20F

Dim PrinBal_old As Decimal = 1.0E-20F

Dim PrinRepay As Decimal = 1.0E-20F

ROI = Convert.ToDecimal(Convert.ToDouble(txtRoi.Text) / 100)

dgvLoanDetails.Rows.Clear()

Dim strDate As Date = dtpDisb.Value

Dim stDate As Date = Convert.ToDateTime(txtStdate.Text)

Dim cnt As Integer = 0

Try

Do Until PrinBal > -1 And PrinBal <= 0

cnt = cnt + 1

PrinBal_old = PrinBal

PrinBal = Amount - AdvEMIAmt

PrinRepay = Amount - AdvEMIAmt

For sCount = 1 To Tenure

If noEmi = 1 Then

stDate_Orginal = strDate.AddDays(8)

Else

stDate_Orginal = stDate

stDate = stDate.AddMonths(1)

End If

If Date.IsLeapYear(stDate.Year) = False Then

interestPaid = (PrinBal * (ROI * 100 / 365)) / 100 * (stDate.Subtract(stDate_Orginal).Days) ' - stDate_Orginal)

Else

interestPaid = (PrinBal * (ROI * 100 / 366)) / 100 * (stDate.Subtract(stDate_Orginal).Days) ' - stDate_Orginal)

End If

PrinRepay = EMI - interestPaid

PrinBal = PrinBal - PrinRepay

noEmi = noEmi + 1

sCount = 1

noEmi = 1

strDate = dtpDisb.Value

stDate = Convert.ToDateTime(txtStdate.Text)

If PrinBal >= 0 Then

Exit Do

Else

''Increasing no. of Zeroes gets even more closer where our principal balance is 0 but it slows down.

'If anyone can help me to make it faster after adding 2 more zeroes after decimal places.

ROI = ROI + 0.0001

End If

Loop

s1.Close()

fs1.Close()

NoOfIterations = cnt

'Populate Datagridview for the CashFlow

sCount = 1

noEmi = 1

PrinBal = Amount

PrinRepay = Amount

For sCount = 1 To Tenure

If noEmi = 1 Then

stDate_Orginal = strDate.AddDays(8)

Else

stDate_Orginal = stDate

stDate = stDate.AddMonths(1)

End If

If Date.IsLeapYear(stDate.Year) = False Then

interestPaid = (PrinBal * (ROI * 100 / 365)) / 100 * (stDate.Subtract(stDate_Orginal).Days) ' - stDate_Orginal)

Else

interestPaid = (PrinBal * (ROI * 100 / 366)) / 100 * (stDate.Subtract(stDate_Orginal).Days) ' - stDate_Orginal)

End If

PrinRepay = EMI - interestPaid

PrinBal = PrinBal - PrinRepay

noEmi = noEmi + 1

dgvLoanDetails.Rows.Add()

dgvLoanDetails.Item("Month", sCount - 1).Value = sCount

dgvLoanDetails.Item("EmiDate", sCount - 1).Value = FormatDateTime(stDate)

dgvLoanDetails.Item("Payment", sCount - 1).Value = Math.Round(EMI, 2)

dgvLoanDetails.Item("Interest", sCount - 1).Value = Math.Round(interestPaid, 2)

dgvLoanDetails.Item("PrinRepay", sCount - 1).Value = Math.Round(PrinRepay, 2)

dgvLoanDetails.Item("PrinBal", sCount - 1).Value = Math.Round(PrinBal, 2)

Return Math.Round((ROI * 100), 2)

Catch ex As Exception

MessageBox.Show(ex.Message & " -- " & ROI & " -- " & PrinBal)

Finally

GC.Collect()

End Try

End Function

Private Function ValidateStructuredCases() As Boolean

Try

Dim structCount As Integer = 0

Dim colCount As Integer = 0

Dim dgKey As Integer = 0

Dim dgValue As Integer = 0

Dim structTenureSum As Integer = 0

If ddmIsStruct.Text = "NO" Then

MessageBox.Show("NA For Equated Cases")

Return False

Exit Function

ElseIf ddmIsStruct.Text = "YES" And IsNumeric(txtNoOfStruct.Text) = False Then

MessageBox.Show("Please Enter No of Structure")

Return False

Exit Function

ElseIf ddmIsStruct.Text = "YES" And IsNothing(dgViewStructured.Rows(Convert.ToInt16(txtNoOfStruct.Text) - 1).Cells(0).Value()) = True Then

MessageBox.Show("Exact Structure Term=")

Exit Function

End If

For structCount = 0 To Convert.ToInt16(txtNoOfStruct.Text) - 1

dgKey = dgViewStructured.Rows(structCount).Cells(0).Value()

dgValue = dgViewStructured.Rows(structCount).Cells(1).Value()

If dgKey.ToString = 0 Then

MessageBox.Show("Please Enter No of EMI for Structure No " & structCount + 1)

Return False

Exit Function

ElseIf dgValue.ToString = 0 Then

MessageBox.Show("Please Enter EMI Amount for Structure No " & structCount + 1)

Return False

Exit Function

Else

structTenureSum += dgKey

End If

If structTenureSum <> Convert.ToInt32(txtTenure.Text) Then

MessageBox.Show("Total Tenure=" & txtTenure.Text & " Not Equal To The Entered Structured Tenure=" & structTenureSum)

Return False

Exit Function

Else

Return True

End If

Catch ex As Exception

MessageBox.Show(ex.Message)

Return False

End Try

End Function

Public Function StructuredIRR() As Double

Dim noEmi As Integer = 1

Dim stDate_Orginal As Date

Dim sCount As Integer

Dim interestPaid As Decimal = 1.0E-20F

Dim PrinBal As Decimal = 1.0E-20F

Dim PrinBal_old As Decimal = 1.0E-20F

Dim PrinRepay As Decimal = 1.0E-20F

ROI = Convert.ToDecimal(Convert.ToDouble(txtRoi.Text) / 100)

dgvLoanDetails.Rows.Clear()

Dim strDate As Date = dtpDisb.Value

Dim stDate As Date = Convert.ToDateTime(txtStdate.Text)

Dim cnt As Integer = 0

Try

Do Until PrinBal > -1 And PrinBal <= 0

cnt = cnt + 1

PrinBal_old = PrinBal

PrinBal = Amount - AdvEMIAmt

PrinRepay = Amount - AdvEMIAmt

For sCount = 1 To Tenure

If sCount <= Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(0).Cells(1).Value())

ElseIf sCount <= Convert.ToInt32(dgViewStructured.Rows(1).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(1).Cells(1).Value())

ElseIf sCount <= Convert.ToInt32(dgViewStructured.Rows(2).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(1).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(2).Cells(1).Value())

ElseIf sCount <= Convert.ToInt32(dgViewStructured.Rows(3).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(2).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(1).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(3).Cells(1).Value())

ElseIf sCount <= Convert.ToInt32(dgViewStructured.Rows(4).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(3).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(2).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(1).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(4).Cells(1).Value())

ElseIf sCount <= Convert.ToInt32(dgViewStructured.Rows(5).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(4).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(3).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(2).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(1).Cells(0).Value()) + Convert.ToInt32(dgViewStructured.Rows(0).Cells(0).Value()) Then

EMI = Convert.ToInt32(dgViewStructured.Rows(5).Cells(1).Value())

End If

If noEmi = 1 Then

stDate_Orginal = strDate.AddDays(0)