Amortisation schedule.

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
Amortization schedule

Hello Friends,

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

Regards,
Prakash
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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.
 

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
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
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017

ADVERTISEMENT

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.
 

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
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
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017

ADVERTISEMENT

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
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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?

Disable AdBlock

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
Top