Calculating the Equated Monthly Installment (EMI) for a loan is crucial for effective financial planning before borrowing. You can use Google Sheets, as it is a quick and more flexible way to get an estimated payment for your loan, using spreadsheet functions. This article will outline step by step how to use Google Sheets to generate an EMI calculation with a simple DIY formula even if you consider yourself a beginner.
Required Inputs to Calculate EMI
For the EMI calculation, these three variables are needed:
| Principal Amount | The amount you initially borrowed |
| Annual Interest Rate | The percentage that will be charged on your loan |
| Loan Tenure | The period for which the loan is granted, and over which the loan is to be repaid. |
Calculating EMI through Google Sheets’ PMT Function
| PMT Function Syntax = PMT(interest_rate, number_of_periods, present_value, [future_value], [type]) |
where:
- interest_rate : Interest rate per period. For monthly EMIs, it is the annual interest rate divided by 12.
- number_of_periods : Total number of payment periods (months).
- present_value : Loan amount (use a negative value for cash outflow).
- future_value : (Optional) Balance after the last payment (usually zero).
- end_or_beginning : (Optional) 0 if payments are due at the end of the period, 1 if at the beginning (default is 0).
Let’s understand this by considering one example:
- Loan amount: ₹5,00,000
- Annual interest rate: 9%
- Tenure: 5 years (60 months)
Now, Open Google Sheets and perform the following:
Step 1: Insert the values of your loan into different cells:
A1: 500000 (Loan Amount)
A2: 9% (Annual Interest Rate)
A3: 5 (Loan tenure in years)
Apply this to any cell to calculate your EMI: =PMT(A2/12, A3*12, -A1)
- A2/12 converts the annual rate to a monthly rate.
- A3*12 converts years to months.
A1 uses the loan amount as present value (negative for cash outflow). The resulting value will be the fixed monthly payment (EMI) you will be required to make.
Step 2: Interpreting the Output
PMT function returns a negative number which is the monthly cash outflow. If you want to show it as a positive number, you can wrap the formula that way:
=-PMT(A2/12, A3*12, -A1)
Step 3: Calculating Principal and Interest
Google Sheets has other functions, PPMT and IPMT, to break down each EMI into principal and interest parts, for a given period.
- PPMT: Calculates the principal portion of a payment.
- IPMT: Calculates the interest portion of a payment.
For example: If you want to calculate how much of the first month’s payment is principal: =PPMT(A2/12, 1, A3*12, -A1)
To calculate how much of the first month’s payment is interest: =IPMT(A2/12, 1, A3*12, -A1)
From either of these formulas, you can drag down, and see the principal and interest, for each of the months, and also track your outstanding balance over time.
Benefits of using Google Sheets for EMI Calculation
- Flexibility: You could change interest rates, loan amount, length of loan, to see how it instantly changes the EMIs.
- Transparency: You know what’s included in your monthly payment – with amounts for principal and amounts for interest.
- Accessibility: Google Sheets is free and cloud-based so it can be accessed anywhere.
- Visualization: You can create charts and tables in Google Sheets to visualize your loan amortization schedule.
Conclusion
Once you get the fundamentals down, calculating your EMI in Google Sheets is relatively easy. You’ll have a few things to know about your loan and use a simple formula, and you should be able to figure out your monthly payments in a few minutes! This helps you in financial planning, and you can work out the options and experiment with different loans without needing anything fancy. Additionally, Google Sheets keeps the numbers simple for you, so you remain in control, and you can make better financial decisions.
Frequently Asked Questions:
An EMI is the fixed amount the borrower pays to a lender every month until the loan amount is paid off in full. Each EMI contains both a repayment of the principal as well as an interest amount.
You will require the loan amount, annual interest, and tenure (in months).
Typically, you would use the PMT function =PMT(rate, number_of_periods, present_value). Rate would be the monthly interest, number_of_periods would be number of months and your present_value is your loan amount (negative number).
Disclaimer:
The EMI calculation shown in this article is for educational and informational purposes only. Actual loan EMIs may vary depending on the bank’s terms, processing charges, and other conditions. Please consult your lender or financial advisor before making any borrowing decisions.
