WHAT IS EMI?
• Equated Monthly instalment (EMI) is the fixed amount of monthly instalment which is required to be paid by the borrower to the lender at a specified date of the month. • An EMI includes two components; Principal repayment component and interest component.
Email: Support@skillfinlearning.com
Mobile: 9650010925
WHAT IS EMI? • EMI’s have low portion of principal repayment component and high portion of interest component during initial years of loan tenure. • Principal component of EMI keeps increasing; while the interest component keeps declining as the loan matures. Months Email: Support@skillfinlearning.com
Mobile: 9650010925
Let’s discuss how to calculate EMI in Excel • A is considering taking a home loan of $ 1,000,000 • Interest rate on loan is 5% • Tenure of the loan is 20 years
For PMT function, Go to Select Formula tab in the menu bar, then Click on Financial and Select PMT function.
How much is the EMI? • The PMT function in excel is a financial function which helps calculate the periodic payments (EMI) on a loan
Email: Support@skillfinlearning.com
Mobile: 9650010925
PMT function arguments
Rate is the interest rate for the loan.
Nper is the total no. of instalments PV is the present value of the loan
FV is the future value of the loan after the last payment is made.
Email: Support@skillfinlearning.com
Type is the logical value; enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You may also leave it blank. In that case it will be considered as 0 by excel.
Mobile: 9650010925
PMT function inputs Nper is the total no. of instalments. • In our example, the total tenure of the loan is 20 years. Therefore, total monthly instalments are 20*12 = 240. • Enter 20*12 as the input for Nper.
Rate is the interest rate for the loan. • In our example, the interest rate per annum is 5%. To calculate the monthly instalment, we should divide 5% by 12. So, put 5%/12 as the input for Rate
PV is the present value of the loan • In our example, it is $1,000,000 FV is the future value of the loan after the last payment is made. • In our example, it will be 0 (Zero), as full loan amount will be repaid at the end of the tenure
Email: Support@skillfinlearning.com
Type is the logical value; • we should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. • We can also leave it blank. In that case it will be considered as 0 by excel. Mobile: 9650010925
PMT function inputs Nper is the total no. of instalments. • In our example, the total tenure of the loan is 20 years. Therefore, total monthly instalments are 20*12 = 240. • Enter 20*12 as the input for Nper.
Rate is the interest rate for the loan. • In our example, the interest rate per annum is 5%. To calculate the monthly instalment, you should divide 5% by 12. So, put 5%/12 as the input for Rate EMI is $6,600/-, which means if “A” pays $6,600 per month for 20 years, the full loan amount will be repaid.
PV is the present value of the loan • In our example, it is $1,000,000 FV is the future value of the loan after the last payment is made. • In our example, it will be 0 (Zero), as full loan amount will be repaid at the end of the tenure
Type is the logical value; • we should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. • We can also leave it blank. In that case it will be considered as 0 by excel.
Did you know calculating EMIs was so easy? Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU Visit Skillfinlearning.com
Email: Support@skillfinlearning.com
Mobile: 9650010925