Hi all:
I have a dataset with thousands of loans and I would like use a code to cycle through each loan to calculate the loan balance based on a desired month. Attached is a sample of 3 observations of 2 loan types for illustration. The last column “bal” is the ending loan balance – this is what I need.
Startdate is the date when the amortization begins for a loan unless a loan has an interest-only period or “io”>0 in which case the amortization begins after the expiration of the “io” period, i.e. from the “amortstart” date
If amort>0 and io>0, then “term” minus “io” equals “whichmth” and converted to date in “amortstart”
If amort>0 and io=0, then “term” = “whichmth”
FYI, basedays is the number of days in year, rate is the annual interest rate, and the rest are in months.
I have included a partial amortization schedule of the first loan. There is no amortization for the first 6 months since it is an interest-only period. Amortization begins in the 7th month or “amortstart” date. At the end of 6 months, the balance is 1986552.
Sample computations for month 1 and so on till end of amortization (1 – 360):
Interest =(8/2/2018 – 7/2/2018)*(.05/360)*2000000 = 8611.11
Principal=10736.43 – 8,611.11 = 2125.32
Bal = 2000000 – 2125.32 = 1997875
I would appreciate any help in writing a code to extract the loan balances. Thanks
Best
Amrik
I have a dataset with thousands of loans and I would like use a code to cycle through each loan to calculate the loan balance based on a desired month. Attached is a sample of 3 observations of 2 loan types for illustration. The last column “bal” is the ending loan balance – this is what I need.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input byte id long amt int(basedays amort) double(rate pmt) int(start term) byte io int(whichmth amortstart) long bal 1 2000000 360 360 .05 10736.43 21186 12 6 6 21367 1986552 2 3000000 360 360 .045 15200.56 21186 6 0 6 . 2976448 3 5400000 360 240 .04787 35009.25 21154 120 0 120 . 3373218 end format %td start format %td amortstart
If amort>0 and io>0, then “term” minus “io” equals “whichmth” and converted to date in “amortstart”
If amort>0 and io=0, then “term” = “whichmth”
FYI, basedays is the number of days in year, rate is the annual interest rate, and the rest are in months.
I have included a partial amortization schedule of the first loan. There is no amortization for the first 6 months since it is an interest-only period. Amortization begins in the 7th month or “amortstart” date. At the end of 6 months, the balance is 1986552.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long date byte month double payment int(interest principal) long endbal 21367 0 . . . 2000000 21398 1 10736.43 8611 2125 1997875 21429 2 10736.43 8602 2134 1995740 21459 3 10736.43 8316 2421 1993319 21490 4 10736.43 8582 2154 1991165 21520 5 10736.43 8297 2440 1988725 21551 6 10736.43 8563 2174 1986552 end format %td date
Interest =(8/2/2018 – 7/2/2018)*(.05/360)*2000000 = 8611.11
Principal=10736.43 – 8,611.11 = 2125.32
Bal = 2000000 – 2125.32 = 1997875
I would appreciate any help in writing a code to extract the loan balances. Thanks
Best
Amrik
