RS232 and Serial Communications. Serial Communications 101. This technical white paper describes everything youll need to know about RS232, com ports and serial. Rate C6C5, which divides rate of interest by number of months thus giving rate of interest per month. Nper C4C5, which gives total number of months we. Xbcn.jpg' alt='Auto Serial Number On Excel' title='Auto Serial Number On Excel' />How to calculate EMI Download Excel EMI calculator Being associated with bank for short term, I often get this question from friends and relatives. How to calculate EMI When you apply for loan, they will just tell you the EMI, but does not tell you how they are calculated unless you ask them specifically to show how they calculated it. EMI table is also called as amortization table, which shows the balance at each installment. EMI or equated monthly installments is the most popular form of loan payment. It is a fixed amount of repayment made every month towards the loan, which includes payment towards both principal and interest. It is easy to prepare an EMI table in Excel sheet. There is a financial function called PMT to calculate the EMI. PMTrate,nper,pvrate Interest rate for the loan. Total number of payments for the loan. Present valueprincipal. Prepare excel sheet with the following data and enter the PMT function in the cell C7. Rate C6C5, which divides rate of interest by number of months thus giving rate of interest per month. Nper C45, which gives total number of months we need to pay equated installment. Auto Serial Number On Excel' title='Auto Serial Number On Excel' />Do not forget to take the loan amount in minus value, so that our EMI table shows positive values. Now prepare table with Serial Number of Payment, Monthly Installment, Interest Amount, Principal Amount, and Balance. Take the first row with serial number 0 and in the balance amount point the cell to Loan Amount type C3 Next Enter the serial number as 1. Monthly installments will remain same so take absolute cell reference by adding which will not change when you drag the cells to auto increment. In Monthly Installment column, enter C7. In the Interest Amount column, enter F1. C6C5 which means we are dividing the balance amount at the end of the month by monthly interest rate rate of interestpayments per year. As the rate of interest and yearly payments are not going to change, we used absolute reference using sign. In Principal Amount column type C1. D1. 1 which deducts the monthly interest from monthly EMI and whatever the balance available shows as payment towards the principal amount. Auto Serial Number On Excel' title='Auto Serial Number On Excel' />In Balance column enter, F1. E1. 1, which means we are deducting the principal amount in the EMI to ascertain the original balance at the end of the month upon which we are going to calculate interest for the next month. Now select the row and drag it until balance becomes 0. Thats all We made our own EMI calculator in Excel. This Excel EMI calculator is created for a tenure of 2. If you are just calculating for 5 years, i. So you need to take up to 0 in the balance column. Ignore the values in RED. If you want to calculate for more years, then just select the last row and drag it to extend it. Download the Simple Excel EMI Calculator. Download the Excel EMI Calculator with Ad Hoc Payments. Download the Excel EMI Calculator with Multiple Reimbursements. Download the Excel EMI Calculator with All the Three Sheets. Update Oct 1. 6, 2. Mr. Parixit Namdhar, one of my blog readers, suggested that instead of entering the formula C7 in column C entering IFB1. C4C5,0,C7 solves the minus values problem. This formula makes the minus values zero making it easily understandable and looks neat. Calcolo Strutture Acciaio Software here. Thanks a lot Parixit Update April 2. Mr. Ravi Krishna, one of my blog readers, updated the Excel sheet with ad hoc payments and multiple reimbursements, which a lot of readers were asking.