Loan Calculator and Remaining Loan Balance

When it comes to finance, if you understand time value of money, you can literally work magic with Excel. If you are going to borrow money for buying a home, car, or for any other purpose, you would typically look for a loan calculator to determine what your monthly payment would be. There is no need for it.


Loan Calculator:

Let us say that you are borrowing $20,000 to buy a car and the loan interest rate is 3% and it is a 36 month loan.  You will be making monthly payments on your loan. In order to determine your monthly payment, all you got to do is this:

In an Excel cell, type “=PMT(0.03/12, 12*3, 20000)” = –$581.624.

  • Monthly Interest Rate = 0.03/12
  • Number of Loan Payment periods (nper) = 12 * 3 = 36
  • Present Value (PV) = $20,000 (this is the amount you are borrowing today)

You get a negative number as a result because that is the amount you be paying each month. It is cash outflow out of your pocket.


Remaining Loan Balance:

Here is a trick question. How much will you owe right after you have made the 23 payments on your loan? You would google loan amortization or do a lengthy calculation in Excel to find the answer as shown below.


However, there is a much simpler way. All you need to do is to time travel and calculate the Present Value (PV) of the loan right after 23 payments.

  • There are 13 payments remaining
  • Monthly Interest rate = 0.03/12
  • Monthly Payment = $581.624

In an Excel cell, type “=PV(0.03/12,13,-581.624)” = $7430.43. 

This will match the exactly value shown in the loan amortization table shown above.


Total Interest Paid:

Here is a little bonus – you can calculate the total interest paid for the loan as well. In an Excel cell, type “=CUMIPMT(0.03/12, 36, 20000, 1, 36, 0)” = $938.47.

In  less than a minute you can calculate the monthly loan payment amount, remaining loan balance, and total interest paid in Excel easily.


Tags : , , , ,