INTRODUCTION
In a mortgage loan with decreasing payments, the amount of each installment varies for each subsequent payment.
The amount of this installment depends on the remaining amount to be paid and the number of remaining payments. The installment consists of two parts: the principal and the interest. The interest part is calculated based on the current amount to be paid and the interest rate, while the principal part is calculated as the difference between the total installment and the interest part.
As time passes, the interest part decreases and the principal part increases, causing the installment to become smaller and smaller.
We calculate current payment with the formula: $R=K \cdot \frac{p(1+p)^n}{(1+p)^n-1}$ gdzie:
- R – current payment,
- K – amount of a loan,
- p – percent,
- n – number of payments.
PYTHON CODE
!pip install xlwt
import xlwt
loan_amount = float(input("Enter the loan amount: "))
num_of_payments = int(input("Enter the number of payments: "))
interest_rate = float(input("Enter the interest rate (e.g. If the rate equals 9% than write 0.09): "))
payment = loan_amount * ((interest_rate/12) * (1 + (interest_rate/12))**num_of_payments) / ((1 + (interest_rate/12))**num_of_payments - 1)
payments_table = []
remaining_balance = loan_amount
for i in range(1, num_of_payments+1):
interest_amount = remaining_balance * interest_rate / 12
principal_amount = payment - interest_amount
remaining_balance -= principal_amount
payments_table.append((i, round(payment, 2), round(principal_amount, 2), round(interest_amount, 2), round(remaining_balance, 2)))
print("Payment No. | Payment Amount | Principal Amount | Interest Amount | Remaining Balance")
for payment_no, payment_amount, principal_amount, interest_amount, remaining_balance in payments_table:
print(f"{payment_no:>11} | {payment_amount:>14} | {principal_amount:>16} | {interest_amount:>15} | {remaining_balance:>16}")
book = xlwt.Workbook(encoding="utf-8")
sheet1 = book.add_sheet("Payments Table")
sheet1.write(0, 0, "Payment No.")
sheet1.write(0, 1, "Payment Amount")
sheet1.write(0, 2, "Principal Amount")
sheet1.write(0, 3, "Interest Amount")
sheet1.write(0, 4, "Remaining Balance")
for row, (payment_no, payment_amount, principal_amount, interest_amount, remaining_balance) in enumerate(payments_table, start=1):
sheet1.write(row, 0, payment_no)
sheet1.write(row, 1, payment_amount)
sheet1.write(row, 2, principal_amount)
sheet1.write(row, 3, interest_amount)
sheet1.write(row, 4, remaining_balance)
book.save("payments_table.xls")
print("The payments table has been saved to payments_table.xls.")
⬆️⬆️⬆️ See in Google Colaboratory
HOW THE CODE WORKS?
- Installation of an additional module from which the xlwt library will be downloaded
- Import of the xlwt library for exporting data to an .xls file
- Getting data from the user – loan amount, number of installments and interest rate.
- Calculation of the value of the monthly loan installment based on the given data.
- Create an empty installment table and variable the remaining amount with the same amount of credit.
- Generating in a loop for a table containing the installment number, installment amount, capital part, interest part and the remaining amount to be repaid after each installment.
- Displaying the table of installments in the console.
- Creating a spreadsheet file, descending the sheet to the file, writing column headers to the spreadsheet, writing values from the table of installments to the spreadsheet.
- Saving the .xls file
- A message about saving the .xls file is displayed.
Dodaj komentarz