N
Nur Muhammad
Dear Friend:
I want to process the following repayment Schedule for loan
chequeId ClientId Period Principal Interest Rental Outstanding Balance
1,050,000
1175 53 1 11,534 14,000 25,535 1,038,466
1176 53 2 11,688 13,846 25,535 1,026,778
1177 53 3 11,844 13,690 25,535 1,014,935
1178 53 4 12,001 13,532 25,535 1,002,933
1179 53 5 12,162 13,372 25,535 990,772
1180 53 6 12,324 13,210 25,535 978,448
1181 53 7 12,488 13,046 25,535 965,960
1182 53 8 12,654 12,879 25,535 953,306
1183 53 9 12,823 12,711 25,535 940,482
1184 53 10 12,994 12,540 25,535 927,488
1185 53 11 13,167 12,367 25,535 914,321
1186 53 12 13,343 12,191 25,535 900,978
1187 53 13 13,521 12,013 25,535 887,457
1188 53 14 13,701 11,833 25,535 873,756
1189 53 15 13,884 11,650 25,535 859,872
1190 53 16 14,069 11,465 25,535 845,803
1191 53 17 14,257 11,277 25,535 831,546
1192 53 18 14,447 11,087 25,535 817,099
1193 53 19 14,639 10,895 25,535 802,460
1194 53 20 14,834 10,699 25,535 787,626
1195 53 21 15,032 10,502 25,535 772,593
1196 53 22 15,233 10,301 25,535 757,361
1197 53 23 15,436 10,098 25,535 741,925
1198 53 24 15,642 9,892 25,535 726,283
1199 53 25 15,850 9,684 25,535 710,433
1200 53 26 16,062 9,472 25,535 694,371
1201 53 27 16,276 9,258 25,535 678,096
1202 53 28 16,493 9,041 25,535 661,603
1203 53 29 16,713 8,821 25,535 644,890
1204 53 30 16,935 8,599 25,535 627,955
1205 53 31 17,161 8,373 25,535 610,794
1206 53 32 17,390 8,144 25,535 593,404
1207 53 33 17,622 7,912 25,535 575,782
1208 53 34 17,857 7,677 25,535 557,925
1209 53 35 18,095 7,439 25,535 539,830
1210 53 36 18,336 7,198 25,535 521,494
1211 53 37 18,581 6,953 25,535 502,913
1212 53 38 18,828 6,706 25,535 484,085
1213 53 39 19,079 6,454 25,535 465,005
1214 53 40 19,334 6,200 25,535 445,671
1215 53 41 19,592 5,942 25,535 426,080
1216 53 42 19,853 5,681 25,535 406,227
1217 53 43 20,118 5,416 25,535 386,109
1218 53 44 20,386 5,148 25,535 365,723
1219 53 45 20,658 4,876 25,535 345,066
1220 53 46 20,933 4,601 25,535 324,133
1221 53 47 21,212 4,322 25,535 302,920
1222 53 48 21,495 4,039 25,535 281,425
1223 53 49 21,782 3,752 25,535 259,644
1224 53 50 22,072 3,462 25,535 237,572
1225 53 51 22,366 3,168 25,535 215,205
1226 53 52 22,665 2,869 25,535 192,541
1227 53 53 22,967 2,567 25,535 169,574
1228 53 54 23,273 2,261 25,535 146,301
1229 53 55 23,583 1,951 25,535 122,718
1230 53 56 23,898 1,636 25,535 98,820
1231 53 57 24,216 1,318 25,535 74,604
1232 53 58 24,539 995 25,535 50,064
1233 53 59 24,866 668 25,535 25,198
1234 53 60 25,198 336 25,535 0
For the above Repayment Schedule i have used the following event procedure
and get principal, interest and Rental amount but I can not get outstanding
amount:
Dim ClientID, NL, TB, Fmt, fval, pval, apr, totpmts, paytype, payment, p, i
Dim numscrn, j As Integer, PERIOD As Integer
Fmt = "###,###,##0.00" ' Define money format.
fval = 0 ' Usually 0 for a loan.
pval = Forms!frm_disbursement!car_sanction_limit 'Sanction Amount
apr = Forms!frm_disbursement![Interest Rate] 'Interest Rate
If apr > 1 Then apr = apr / 100 ' Ensure proper form.
ClientID = Me.client_id
totpmts = Forms!frm_disbursement!car_sanction_tenor 'Tenor/ No of Installment
paytype = 0
payment = Abs(-Pmt(apr / 12, totpmts, pval, fval, paytype))
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from ZZtbl_MonitoringCheque"
For PERIOD = 1 To totpmts
p = PPmt(apr / 12, PERIOD, totpmts, -pval, fval, paytype)
p = (Int((p + 0.005) * 100) / 100) ' Round principal.
i = payment - p
i = (Int((i + 0.005) * 100) / 100) ' Round interest.
' DoCmd.RunSQL "Insert into ZZtbl_MonitoringCheque
(Period,payment,principal,interest) values (" & PERIOD & "," & payment & ","
& p & "," & i & ")"
If PERIOD > totpmts Then Exit For
DoCmd.RunSQL "Insert Into ZZtbl_MonitoringCheque (ClientId,
Period,payment,principal,interest) values ( " & ClientID & ", " & PERIOD &
"," & payment & "," & p & "," & i & " )"
Next PERIOD
' DoCmd.RunSQL "update tblamort set
payment=round(payment,2),principal=round(principal,2),interest=round(interest,2)"
DoCmd.SetWarnings True
DoCmd.OpenTable "ZZtbl_MonitoringCheque"
So, how can i get Outstandind Amount ? Pls give me any solution
Thanks
I want to process the following repayment Schedule for loan
chequeId ClientId Period Principal Interest Rental Outstanding Balance
1,050,000
1175 53 1 11,534 14,000 25,535 1,038,466
1176 53 2 11,688 13,846 25,535 1,026,778
1177 53 3 11,844 13,690 25,535 1,014,935
1178 53 4 12,001 13,532 25,535 1,002,933
1179 53 5 12,162 13,372 25,535 990,772
1180 53 6 12,324 13,210 25,535 978,448
1181 53 7 12,488 13,046 25,535 965,960
1182 53 8 12,654 12,879 25,535 953,306
1183 53 9 12,823 12,711 25,535 940,482
1184 53 10 12,994 12,540 25,535 927,488
1185 53 11 13,167 12,367 25,535 914,321
1186 53 12 13,343 12,191 25,535 900,978
1187 53 13 13,521 12,013 25,535 887,457
1188 53 14 13,701 11,833 25,535 873,756
1189 53 15 13,884 11,650 25,535 859,872
1190 53 16 14,069 11,465 25,535 845,803
1191 53 17 14,257 11,277 25,535 831,546
1192 53 18 14,447 11,087 25,535 817,099
1193 53 19 14,639 10,895 25,535 802,460
1194 53 20 14,834 10,699 25,535 787,626
1195 53 21 15,032 10,502 25,535 772,593
1196 53 22 15,233 10,301 25,535 757,361
1197 53 23 15,436 10,098 25,535 741,925
1198 53 24 15,642 9,892 25,535 726,283
1199 53 25 15,850 9,684 25,535 710,433
1200 53 26 16,062 9,472 25,535 694,371
1201 53 27 16,276 9,258 25,535 678,096
1202 53 28 16,493 9,041 25,535 661,603
1203 53 29 16,713 8,821 25,535 644,890
1204 53 30 16,935 8,599 25,535 627,955
1205 53 31 17,161 8,373 25,535 610,794
1206 53 32 17,390 8,144 25,535 593,404
1207 53 33 17,622 7,912 25,535 575,782
1208 53 34 17,857 7,677 25,535 557,925
1209 53 35 18,095 7,439 25,535 539,830
1210 53 36 18,336 7,198 25,535 521,494
1211 53 37 18,581 6,953 25,535 502,913
1212 53 38 18,828 6,706 25,535 484,085
1213 53 39 19,079 6,454 25,535 465,005
1214 53 40 19,334 6,200 25,535 445,671
1215 53 41 19,592 5,942 25,535 426,080
1216 53 42 19,853 5,681 25,535 406,227
1217 53 43 20,118 5,416 25,535 386,109
1218 53 44 20,386 5,148 25,535 365,723
1219 53 45 20,658 4,876 25,535 345,066
1220 53 46 20,933 4,601 25,535 324,133
1221 53 47 21,212 4,322 25,535 302,920
1222 53 48 21,495 4,039 25,535 281,425
1223 53 49 21,782 3,752 25,535 259,644
1224 53 50 22,072 3,462 25,535 237,572
1225 53 51 22,366 3,168 25,535 215,205
1226 53 52 22,665 2,869 25,535 192,541
1227 53 53 22,967 2,567 25,535 169,574
1228 53 54 23,273 2,261 25,535 146,301
1229 53 55 23,583 1,951 25,535 122,718
1230 53 56 23,898 1,636 25,535 98,820
1231 53 57 24,216 1,318 25,535 74,604
1232 53 58 24,539 995 25,535 50,064
1233 53 59 24,866 668 25,535 25,198
1234 53 60 25,198 336 25,535 0
For the above Repayment Schedule i have used the following event procedure
and get principal, interest and Rental amount but I can not get outstanding
amount:
Dim ClientID, NL, TB, Fmt, fval, pval, apr, totpmts, paytype, payment, p, i
Dim numscrn, j As Integer, PERIOD As Integer
Fmt = "###,###,##0.00" ' Define money format.
fval = 0 ' Usually 0 for a loan.
pval = Forms!frm_disbursement!car_sanction_limit 'Sanction Amount
apr = Forms!frm_disbursement![Interest Rate] 'Interest Rate
If apr > 1 Then apr = apr / 100 ' Ensure proper form.
ClientID = Me.client_id
totpmts = Forms!frm_disbursement!car_sanction_tenor 'Tenor/ No of Installment
paytype = 0
payment = Abs(-Pmt(apr / 12, totpmts, pval, fval, paytype))
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from ZZtbl_MonitoringCheque"
For PERIOD = 1 To totpmts
p = PPmt(apr / 12, PERIOD, totpmts, -pval, fval, paytype)
p = (Int((p + 0.005) * 100) / 100) ' Round principal.
i = payment - p
i = (Int((i + 0.005) * 100) / 100) ' Round interest.
' DoCmd.RunSQL "Insert into ZZtbl_MonitoringCheque
(Period,payment,principal,interest) values (" & PERIOD & "," & payment & ","
& p & "," & i & ")"
If PERIOD > totpmts Then Exit For
DoCmd.RunSQL "Insert Into ZZtbl_MonitoringCheque (ClientId,
Period,payment,principal,interest) values ( " & ClientID & ", " & PERIOD &
"," & payment & "," & p & "," & i & " )"
Next PERIOD
' DoCmd.RunSQL "update tblamort set
payment=round(payment,2),principal=round(principal,2),interest=round(interest,2)"
DoCmd.SetWarnings True
DoCmd.OpenTable "ZZtbl_MonitoringCheque"
So, how can i get Outstandind Amount ? Pls give me any solution
Thanks