iYogi

Home » TechGenie Answers, Technology » How To Calculate a Loan Payment Using Microsoft Excel

How To Calculate a Loan Payment Using Microsoft Excel

The greatest features of Excel are its ability to perform all calculations properly without any error, keeping in mind the fact that one uses the formulas correctly. With Excel users can also try various alternatives to perform financial analysis. It is very helpful to users while presenting financial scenarios. It helps the user to make decisions involving the numbers he/she enters in the spreadsheet. Just try changing a number anywhere in the sheet, you will see the effect. The reason behind why one can use Excel for many different tasks is its speed to perform calculations. It enables you to stimulate different managerial and financial options and their subsequent numerical outcomes.

One of the important features of Excel is that user can set up formulas to suit his/her needs and the most important features are that it can be sent to other users who need the information. Sending Excel spreadsheet to any part of the world is as easy as ending email.

Microsoft ExcelBelow are some simple steps through which user can Calculate a Loan Payment using Microsoft Excel.

Step1:

Firstly gather all the required information like loan principle, the interest rate, the number of months for which the interest is calculated, total number of payments etc. User need to have concepts on principle, interest rate etc.

Step 2:

After all the required information’s are gathered, the second step would be to create an Excel spreadsheet containing the gathered information. Enter the headlines as: “Interest Rate” in cell A3, “Amount Financed” in cell A4, “Term” (No. of months) in cell A5, “Monthly Payments” in cell D3, “1st Payment Due” in cell D4.

Step 3:

Now use the information gathered to key in the proper information in the cells right away to the right of the cells where you entered the headings. E.g.: Interest Rate of your loan in cell A3, put the interest rate 7%, 8% whatever it may be in B3.

Step 4:

Next step will be to enter formula so that your computer finds out your monthly payments. So on formula put: PMT (B3/12, B5, and B4). PMT used in Excel stands for EMI (easy monthly installment).

[NB: Syntax of PMT

PMT (Interest Rate, Number of Payments, PV, FV, Type)

Interest Rate stands for the interest rate of the Loan, Number of Payments stands for the total number of payments for the Loan, PV stands for the present value or principal of the Loan and FV is optional. It’s about the future value or the Loan amount which is left after all the payments were made. If one omits the parameter, the PMT function assumes the FV value to be 0.]

Step 5:

Just entering the formula will not do, you have to understand what the formula means so that you can apply it in other similar situations.

PMT is the command or syntax in Excel through which it indicates the computer to calculate the monthly payment, B3/12 simply divides the interest rate by 12 so as to get the interest for a month and B5 stands for the total number of months of the loan period and B4 stands for the principal amount of the Loan.

Harmeet

About Harmeet


loves to simplify technology for all readers who are interested in the genre but find it complex to comprehend. She is a writer by passion and wants to enrich the life of others by writing about the latest gadgets, apps, and devices in the simplest and the most useful manner. Her blogs are detailed, interesting, and definitely fun to read…


Experience the Best Computer Support

Leave a comment

*