# Amortisation schedule.

#### N Prakash

##### Active Member
Hi! friends

While calculating EMI (Amortization schedule) I want to calculate interest monthly on actual days in a month. I use PMT function. There will be a small residual at the end of the tenor. Is it possible to make zero at the end of the period, by distributing the amount to the full term and small difference should get added to the last installment. Can any body give me the formula. This is required to give repayment schedule to our banking clients.

Loan Amount -100000
Tenor 84 months
IRR 8.50%
EMI 1,584.00 Rs. 1,583.65
Starts on 08-01-03
Ends on 08-12-09
Residual at the end of 84 months will be Rs.926/-

Regards,
Prakash.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Amortization schedule

Hello Friends,

Any help from friends for my problem. I am stuck.

Regards,
Prakash

Long time no see my friend. You can use goal seek command. Click
tools, goal seek "set the cell to" the last cell where the residual amount is placed, then "to value box", put 0, then in the "by changing the cell" put the cell where the periodic payment is. I forgot to mention, you must remove the formula of the periodic payment and change it to value first before you type the goal seek. It cannot accept formula except for the set cell box which should be a formula. You must also have the amortization table made first as to know what is the residual value.

Hi friend,

Thanks for the solution, but I want to add it along with the formulae so as to make the functions dynamic. Is it possible.

Regards,
Prakash

I believe that is the limitation of excel standard function. You need a customized one but the formula may take me sometime to work with and may not give an accurate answer as i will use a trial and error process for this. I will give an answer next week or next, next week. I am on fieldwork until Saturday. For the meantime, you have to rely on goal seek command.

Hi,

Thank you very much. You can give me the formula at your leisure time. I will try to manage with Goal seek.

Regards,
Prakash

Function emirange(aarea As Range, brate As Single, cpv As Double, Optional rdg As Variant) As Double
'aarea=your range of dates
'brate=your rate per period
'cpv=your principal (must be positive value)
'rdg=round off if remove default no round off

Dim ctr As Integer, pctr As Integer
Dim per As Single
Dim rsulta As Double, ctrb As Double
Dim marka As Integer, markb As Integer
Dim emi As Double
emi = -Pmt(brate, aarea.Count - 1, cpv)
ctrb = emi * 0.25
marka = 0
pctr = 0
Do
rsulta = cpv
ctr = 0
pctr = pctr + 1
For Each sel In aarea
ctr = ctr + 1
If ctr > 1 Then
If Not IsMissing(rdg) Then
rsulta = rsulta + WorksheetFunction.Round(rsulta * brate * (sel - per), rdg) - emi
Else
rsulta = rsulta + (rsulta * brate * (sel - per)) - emi
End If
End If
per = sel
Next sel
If WorksheetFunction.Round(rsulta, 2) = 0 Then Exit Do
If rsulta > 0 Then
marka = 1
Else
marka = -1
End If
If marka <> markb Then
ctrb = marka * Abs(ctrb) * 0.5
If ctrb = 0 Then Exit Do
markb = marka
End If
emi = emi + ctrb
Loop Until pctr > 500
emirange = emi
End Function

This does not need a amortization table.Function emivalue(date1 As Date, date2 As Date, numb As Integer, brate As Single, cpv As Double, Optional rdg As Variant) As Double
'sdate1 = beginning date
'date2 = 2nd date
'numb = total number of period
'brate=rate
'cpv = principal (must be positive value)
'rdg=round off
Dim ctr As Integer, I As Integer, pctr As Integer
Dim per As Single
Dim rsulta As Double, ctrb As Double
Dim marka As Integer, markb As Integer
Dim emi As Double
Dim sel As Date
emi = -Pmt(brate, numb, cpv)
ctrb = emi * 0.25
marka = 0
pctr = 0
Do
rsulta = cpv
ctr = 0
pctr = pctr + 1
For I = 1 To numb
ctr = ctr + 1
If I = 1 Then
If Not IsMissing(rdg) Then
rsulta = rsulta + WorksheetFunction.Round(rsulta * brate * (date2 - date1), rdg) - emi
Else
rsulta = rsulta + (rsulta * brate * (date2 - date1)) - emi
End If
per = date2
sel = WorksheetFunction.Min(DateSerial(Year(date2), Month(date2) + 1, Day(date2)), DateSerial(Year(date2), Month(date2) + 2, 0))
Else

If Not IsMissing(rdg) Then
rsulta = rsulta + WorksheetFunction.Round(rsulta * brate * (sel - per), rdg) - emi
Else
rsulta = rsulta + (rsulta * brate * (sel - per)) - emi
End If
per = sel
sel = WorksheetFunction.Min(DateSerial(Year(sel), Month(sel) + 1, Day(sel)), DateSerial(Year(sel), Month(sel) + 2, 0))
End If
Next I
If WorksheetFunction.Round(rsulta, 2) = 0 Then Exit Do
If rsulta > 0 Then
marka = 1
Else
marka = -1
End If
If marka <> markb Then
ctrb = marka * Abs(ctrb) * 0.5
If ctrb = 0 Then Exit Do
markb = marka
End If
emi = emi + ctrb
Loop Until pctr > 500
emivalue = emi
End Function

This is the help vba

Const Lib = """c:\windows\system\user32.dll"""
Option Base 1

Sub Auto_open()

Register "emirange", 3, "range,rate,principal,rounding", 1, "Amortiz", _
"Get Periodic Payment", """scope area"",""rate per period"",""loan or PV"",""off""", _
"CharPrevA"
Register "emivalue", 3, "1st date,2nd date,period,interest,principal,rounding", 1, "Amortiz", _
"Shortcut Periodic Payment", """date 1"",""date 2"",""number"",""per period"",""loan "",""optional""", _
"CharNextA"

End Sub

Sub Register(FunctionName As String, NbArgs As Integer, _
Args As String, MacroType As Integer, Category As String, _
Descr As String, DescrArgs As String, FLib As String)

Application.ExecuteExcel4Macro _
"REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
& """,""" & FunctionName & """,""" & Args & """," & MacroType _
& ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"

End Sub

Sub Auto_close()

Dim FName, FLib
Dim I As Integer
FName = Array("emirange", "emivalue")
FLib = Array("CharPrevA", "CharNextA")
For I = 1 To 2
With Application
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & _
",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
End With
Next

End Sub

Replies
0
Views
534
Replies
9
Views
742
Replies
1
Views
674
Replies
2
Views
926
Replies
4
Views
1K

1,217,439
Messages
6,136,619
Members
450,022
Latest member
Joel1122331

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back