Financial Model, General Excel Financial Models, Real Estate

Amortization, Calculator, Financial Debt, Financing

This financial model makes it easy to view the amortization of a loan.

There are some inputs that the user has to give, and the model will automatically calculate the scheduled payments for the user.

INPUTS:

1) Loan Amount: The user has to input the total loan amount in the E8 cell of the excel sheet.
2) Interest Rate: The interest rate that will be applicable on the loan is to be entered in cell E9.
3) Loan term in years: Total life of the loan is to be entered in cell E10
4) Payments made per year: Here, the user has to enter the number of payments they would like to make in a year in the cell E11
5) Loan repayment date start: By default, this cell will take today’s date whenever the user opens the excel sheet. It can also be changed to whatever date the user wants to enter.
6) Optional extra payments: (cell E14) This is an optional input if the user wants to calculate the effect of making extra payments on the total payment, total interest paid, and years saved off in the original loan term.

Under Loan Summary, the outputs are present such as:

1) Scheduled payment: The actual payment user has to make based on the loan conditions (inputs) entered by the user.
2) Scheduled number of payments: The number of payments the user has to make.
3) Actual number of payments: If the user chooses to pay extra payments (cell E14), then the actual number of payments is less than the scheduled number of payments.
4) Years saved off original term loan: Number of years a user will save in making loan term payments if they choose to make extra loan payments.
5) Total interest: Total amount of interest that the user had to pay on the original loan.

Now from row 16, the actual schedule is shown as output. It contains column items like:

1) Payment Date: The payment date of each payment.
2) Beginning Balance: The remaining amount of the loan after each payment.
3) Scheduled payment: The calculated scheduled payment for every payment time.
4) Extra payment: Optional extra payment chosen by the user.
5) Principal: Proportion of scheduled payment going to reduce the original principal.
6) Interest: Proportion of scheduled payment going towards paying the interest.
7) Ending balance: The remaining loan amount is to be paid after each installment.
8) Cumulative Interest: The cumulative interest the user has paid till now.

For sampling purposes, we have entered the temporary values in all inputs under “ENTER VALUES”

Loan amount= $2,00,000.00
Interest rate = 5.00%
Loan term in years= 10
Payments made per year= 12
Loan repayment start date= 14-10-2022
Optional extra payments = $100.00

Based on these values, the model calculates all the below output and generates a schedule for loan repayment.

LOAN SUMMARY

Scheduled payment = $2,121.31
Scheduled number of payments= 120
Actual number of payments= 114
Years saved off original loan term= 0.50
Total early payments= $11,300.00
Total interest= $51,219.39

Here you can see in inputs we have chosen the optional extra payment of $100, and this results in:
Actual number of payments= 114
Years saved off original loan term= 0.50

And below in the sheet, you will find the proper schedule of payments.

In case you have any queries, feel free to reach out to us.

You must log in to submit a review.