Calculating Loan Payoff and Loan Payment

[Sherry Johnson]

I have a question in another section of the Debt Class that is 3 weeks old. I have moved on and now I am in the Foles Apply what you have learned end of the course.  In order to Calculate my Loan Pymt and Payoff I need to know what the Loan Fee is and also the Loan Term.  Is the Term the 30 years like the Amortization and the fee 1% like in the practice spreadsheet?  Thanks.

[Spencer Burton]

Splitting this question into its own topic

Hi Sherry – happy to help clarify here.

To calculate loan payment and loan payoff, I’ll first refer you for review to this lesson.

Loan Payoff

Assuming the loan uses a 30/360 interest calculation method and has a fixed interest rate, which is common with commercial mortgages, calculating the loan payoff in Excel is quite easy:

1. Find the fixed annual interest rate

2. Find the monthly amortizing payment (i.e. principal + interest payment)

3. Determine how many months of amortization are remaining

So for instance, imagine a loan was originated exactly ten years ago. The loan has a fixed interest rate of 5.0%, has a principal and interest (i.e. amortizing) payment of 53,682.16, and the original loan assumed 360 month amortization.

To calculate the payoff of this loan today, we would use the following formula:

Remaining Loan Balance (e.g. Loan Payoff) = PV([Annual Interest Rate]/12,[Amortization Remaining],-[Principal + Interest Payment])

Or in other words:

1. Annual interest rate = 5.0%

2. Monthly amortizing payment = 53,682.16

3. Months of amortization remaining = 360 months minus 120 months (i.e. 10 years have passed) = 240 months

Remaining Loan Balance (e.g. Loan Payoff) = PV(5.0%/12, 360-120, -53,682.16)

Let me know if I can answer any other questions for you.

Spencer

 

1 user thanked author for this post.

Sherry Johnson

[Author]

Posts