subject: How To Create A Loan Calculator With Microsoft Excel [print this page] Welcome to My Excel Tutorial, now we are going to show how to make a loan calculator. This is a basic financial configuration and it allows you to know how much money you have to pay monthly with a specific loan amount and interest rate. At last we are going to make an amortization table that shows the interest rate and balance in each month of the duration of the loan.
The excel functions that we are going to use are: NOMINAL(), PMT()
To understand the entire exercise and practice you can download the file at the end of the post. In the following picture you can see the table and use that picture to understand the exercise.
First, you have to configure an Excel Worksheet, in the file attached you can find a template and the solved file, but you can construct your own excel table. All the calculations that we are going to explain are based in the template file.
The first thing we are going to do is convert the rate. The rate in most of the cases is given in % per year, but you must make the payments monthly, for that reason the rate should be converted to monthly rate. To do that we are going to put in the cell D7 the following excel formula: =NOMINAL(D6,12)/12
This excel formula calculates the Nominal rate taking in count the rate given (Cell D6) and this value is divided by the number of periods in a year (is 12 because a year has 12 months).
The second part is to calculate the monthly payment. To do that we are going to use the following formula in the cell D8:
=PMT(D7,D5,D4)*-1
This excel function calculates the monthly payment taking the loan amount, the loan term and the converted interest rate. As payment is a negative value we make a multiplication by -1.
Now you can know how much you should pay monthly for a specific loan. You can change, the loan amount, the loan term and the interest rate per year and you will know the amount of cash you have to pay each month.
The last part is to create the amortization table. It is used to see the each month how much is for interest, for principal and the balance of the loan. Let's see how we construct it.
You must create an excel table with the following fields: Month, Initial Balance, Payment, Interest Paid, Principal Paid, Final Balance. In the first column the months, it is a list of numbers from 1 to the number of months of the loan term.
The next column is the Initial Balance for the first row, it is the Loan amount (cell D4), for the following rows is the Final Balance of the previous row. For the cell C14 the formula is =G13. You can select the C14 cell and drag to the last row.
The next field is the Payment. It is the payment previously calculated. For the cell D13 the formula is =$D$8 (the $ is used to keep fixed the cell D8 when you drag the cell value D13). You select the D13 cell and drag it to the last row.
Below, the Interest Paid is the amount of interest that contains the payment. For the cell E13 the formula is: =C13*$D$7 It is the amount of the initial balance multiplied by the converted interest rate. After that you can select the E13 cell and drag it to the last row.
The Principal Paid is the amount of principal that contains the payment. For the cell F13 the formula is: =D13-E13 It is the subtraction between the Payment and the Interest Paid. You select the F13 cell and drag it to the last row.
The last column is the Final Balance, it is the loan amount that remains to pay after the present payment. For the cell G13 the formula is: =C13-F13 It is the subtraction between the Initial Balance and the Principal Paid. You select the G13 cell and drag to the last row.
After all you can proof that all the calculation are good if the Final Balance in the last month is cero (0).
You can see that the interest rate is decreasing as Principal paid increases.
This excel table is really helpful to know the loan amount that remains in a specific month. If you want to cancel or finish the payments of a loan, you see how much is the final balance in the month and that is the amount that you have to pay.
