Loan Payoff

[Sydney Smith]

Hello, is there a way to use Stata to figure out how many years it will take to payoff a loan? I am using a data set with total loan amount, year loan was taken out, monthly loan payments, and annual interest rate on the loan.

Example: If someone took out a $10,000 loan in 2001 with $100 monthly payments and 2% annual interest rate. When will/did they pay it off? I would like to use Stata to do this since I need to calculate it for thousands of people.

Thank you so much for any help you can provide with code to compute this!

[Øyvind Snilsberg]

maybe,

gen wanted = loan_year + ceil((-(ln(1-(interest/12)*loan_amount/monthly_payments)) / ln(1+(interest/12))) / 12)

[Sydney Smith]

Thank you so much! It works but there are many missing values due to the "ln" function's limits, specifically when the number inside parenthetical is negative. I assume there is no way around this?

[Øyvind Snilsberg]

missing means that they will never pay it off,

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input float(id loan_year loan_amount monthly_payments interest)

            id  loan_year  loan_am~t  monthly~s   interest
  1. 1 2001 10000 100  .02
  2. 2 2001 10000 100 .119
  3. 3 2001 10000 100  .12
  4. 4 2001 10000 100 .121
  5. end

. gen wanted = loan_year + ceil((-(ln(1-(interest/12)*loan_amount/monthly_payments)) / ln(1+(interest/12))) / 12)
(1 missing value generated)

. list, clean ab(50)

       id   loan_year   loan_amount   monthly_payments   interest   wanted  
  1.    1        2001         10000                100        .02     2011  
  2.    2        2001         10000                100       .119     2042  
  3.    3        2001         10000                100        .12     2149  
  4.    4        2001         10000                100       .121        .

[Sydney Smith]

Yes, that makes sense. Thank you!