How to calculate mortgage with a floating rate that compounds monthly-CodePudding

I am solving this problem where im given a floating rate of 5.1 that increases .1 every month. (5.1, 5.2 ... 5.9, 6%) It also compounds monthly. Im given an initial loan of 200,000. Monthly payments of 1000 and im trying to solve how much they owe every month.

I am using Pandas Series to hold the increasing rate. Im having difficulty creating a function that will help. Any suggestions would be appreciated.

This is what I have.

df = pd.DataFrame(51*np.ones(100)    np.arange(100))
df = df.rename(columns={0:'monthly rate'})
df['monthly rate'] = df['monthly rate']  /10/100 /12
df['monthly payment'] = 1000
df['interest due'] = df['monthly rate'] * 200000
df['mortgage decreasing'] = df['interest due'] - df['monthly payment']

` This is where I get confused. So we start with 200,000. And it decreases each month, and then that decrease we calculate the new interest due using that new amount. So its like one involved the other and im not sure how to put that into code.

I think where im going wrong is in the calculating interest due portion. Since in that code I am multiplying the rate by the initial loan value, instead of the values of each month. Im just unsure how to solve that.

CodePudding user response：

Just in plain Python you can simulate it like this:

loan = 200000
interest = 0.051
payment = 1000
interest_change = 0.001
month = 1

while month < 37:
    # In real life banks calculates interest per day, not 1/12 of year
    month_interest = loan * interest/12
    new_loan = loan month_interest-payment
    print ("%s: %.2f \t  %.2f (%.2f %%) \t-%s \t -> %.2f  " % (month,loan,month_interest, interest*100, payment, new_loan))
    loan = new_loan
    interest  = interest_change
    month  = 1
    if loan < 0:
       break