In my previous post I shared EMI Calculator with you. In this post I am sharing how to make EMI Calculator. This has been done using PMT Function in excel to calculate EMI amount and Spin Buttons ActiveX Controls to fine tune the amounts.
PMT Function Calculates the payment for a loan based on constant payments and a constant interest rate. For calculating EMI you need 3 inputs.
Loan Amount , Annual Interest Rate, Loan Period in Years. Using these figures EMI can be calculated with PMT Function in Excel
Syntax : PMT(RATE,NPER,PV,FV,TYPE)
RATE : is the interest rate for the loan. In case you are making monthly payments devide the annual rate by 12 to get monthly rate.
NPER : is the total number of payments for the loan. When payments are made on monthly basis you need to multiply number of years with 12 to arrive at total number of payments.
PV : is the present value, or the total amount that a series of future payments is worth now; also known as the principal. We take this figure as (-) Negative in our formula since it is receipt of funds.
FV : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. In our formula we omit this figure as we need to reach zero balance at the end of term.
Type : is the number 0 (zero) or 1 and indicates when payments are due. If payments are due at the end of the period use 0 or omit this figure. use 1 if payments are made at the beginning of the period. In our formula we have omitted this.
We have calculated our figures with following formulas
EMI : =PMT(Interst/12,Years*12,-LoanAmt)
Total Amount with Interest - This is total outgo during the loan term. This can be calculated using : = EMI*Years*12
Flat Interest Rate : = (Total Amount with Interest - LoanAmt)/LoanAmt/Years : Many financiers talk about Flat interest rate while landing money. They calculate simple interest on entire loan amount for entire period of the loan. Many people go for the loan without understanding the actual interest rate. Flat interest rate looks very attractive as it is much lower than the actual interest rate which is calculated on reducing balance. You can see the difference in the figures above. So be careful while borrowing on flat interest rate.
In next post I will cover Adding Spin Buttons ActiveX Controls to Worksheet.
Using PMT Function in Excel
PMT Function Calculates the payment for a loan based on constant payments and a constant interest rate. For calculating EMI you need 3 inputs.
Loan Amount , Annual Interest Rate, Loan Period in Years. Using these figures EMI can be calculated with PMT Function in Excel
Syntax : PMT(RATE,NPER,PV,FV,TYPE)
RATE : is the interest rate for the loan. In case you are making monthly payments devide the annual rate by 12 to get monthly rate.
NPER : is the total number of payments for the loan. When payments are made on monthly basis you need to multiply number of years with 12 to arrive at total number of payments.
PV : is the present value, or the total amount that a series of future payments is worth now; also known as the principal. We take this figure as (-) Negative in our formula since it is receipt of funds.
FV : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. In our formula we omit this figure as we need to reach zero balance at the end of term.
Type : is the number 0 (zero) or 1 and indicates when payments are due. If payments are due at the end of the period use 0 or omit this figure. use 1 if payments are made at the beginning of the period. In our formula we have omitted this.
We have calculated our figures with following formulas
EMI : =PMT(Interst/12,Years*12,-LoanAmt)
Total Amount with Interest - This is total outgo during the loan term. This can be calculated using : = EMI*Years*12
Flat Interest Rate : = (Total Amount with Interest - LoanAmt)/LoanAmt/Years : Many financiers talk about Flat interest rate while landing money. They calculate simple interest on entire loan amount for entire period of the loan. Many people go for the loan without understanding the actual interest rate. Flat interest rate looks very attractive as it is much lower than the actual interest rate which is calculated on reducing balance. You can see the difference in the figures above. So be careful while borrowing on flat interest rate.
In next post I will cover Adding Spin Buttons ActiveX Controls to Worksheet.
How to create Home loan EMI calculator with features like staggered disbursement, variable interst rates amd moratorium period
ReplyDeleteI am a banker. We charge interest rate on the outstanding balance every month end. This is known as interest with monthly rests. Could you calculate and send me a blank table wherein if I put the details, the program should calculate emi with monthly rests (ie interest will be charged on outstanding every month) what will be the emi? Could you send the same to me on my email id vinodhemdev@gmail.com This will be very helpful for our customers who wish to know how much emi will be there per lac on monthly rest basis
ReplyDeleteuse pmt formula
ReplyDelete=pmt(5%/12,60,-10000)
Dear Yogesh, Thank you for explaining in very simple way the usage. It is indeed very helpful to all.
ReplyDeleteParesh
Hi I used the same figure at same cells with 14 % , instead of 14.25 as decimals were creating problem and exact same formula but i am getting value 233333.34 . WHy ? do i need to enable something in excel . i checked again the formula and values but i dont get EMI Value that you have got in the example . where i am making mistake ?
ReplyDeleteI am looking for a formula to calculate the PPF. Each month separately and whole year accumulated interest.
ReplyDeleteThanks
Excellent! Thanks
ReplyDeleteWhat is formula for calculating EMI of a sim0le interest loan.
ReplyDeleteWhat is formula for calculating EMI of a sim0le interest loan.
ReplyDeletewhy do you have taken principal as negative(-) .
ReplyDeleteValuable Information!!
ReplyDeleteShalom, Om swastiastu, Namo buddhaya, Salam kebajikan, pada artikel kali ini kami akan memberikan kepada kamu Panduan Cara Bet 2D Togel Klik4D Online yang tersedia di S128cash. Berhubung permainan togel adalah game legendaris di Indonesia, tahukah kamu kalau game togel sekarang bisa dimainkan secara online ? (Baca Selengkapnya Disini...)
ReplyDeletegolden goose
ReplyDeletekyrie 4
kd shoes
golden goose outlet
kawhi leonard shoes
hermes handbags
curry 6
supreme hoodie
moncler
supreme new york
In my past post I imparted EMI Calculator to you. In this post I am sharing how to make EMI Calculator. This has been finished utilizing PMT Function in cheap essay service dominate to compute EMI sum and Spin Buttons ActiveX Controls to calibrate the sums.
ReplyDeleteSENSOTOP will concentrate on this supplying innovative systems targeted on liquid leaks networks installation of systems in all environmental conditions, industrial plant settings, refineries, oil in water
ReplyDelete, pipeline oil depots and tank farms.
Smart Kitchens by Superior Living is one of the UAE’s most dominant renovation and project management companies. We are a group of Selected contractors who are here to help you give your kitchen a complete makeover the smart way by our kitchen cabinets suppliers in dubai
ReplyDeleteservices. We’re here for you every step of the way.