You have taken a loan and you know your EMI capability. So, you want to know how many months will be taken to pay off a loan completely. It is fairly easy job to do it in Excel. You will need to use NPER function for this.
Category: Tips and Tricks
Tips & Tricks 88 – Financial Function – Calculate Principal Part of an EMI
This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. This I talked about in Tips & Tricks 87 –…
Tips & Tricks 87 – Financial Function – Calculate Interest Part of an EMI
This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. Excel defines IPMT as "Returns the interest payment for a…
Tips & Tricks 86 – Quickly Access AutoSum
Autosum is a much widely used feature in Excel. It is available in two places in Excel 2013. One in Home tab and one in Formulas tab. You can quickly access the AutoSum by the shortcut key ALT+
Tips & Tricks 85 – Show Comments Always
You put a comment in Excel and it puts a red triangle in the top right corner to indicate that there is a comment. But sometimes, it is necessary that comment should be visible when Excel is opened. This becomes very desirable feature when dealing with top executives. This is also a good feature to…
Tips & Tricks 84 – Financial Function – Calculate EMI
You want to take a loan and you want to calculate EMI OR you want to build an EMI calculator in Excel. It is a fairly easy job to do – You will need to use PMT function for this. It has following structure – PMT(rate, nper, pv, [fv], [type])
Tips & Tricks 83 – Calculate Geometric Mean by Ignoring 0 and Negative Values
Geometric Mean is a useful mean and is applied only for +ve values. Hence, you will need to ignore <=0 values while calculating Geometric Mean. It is generally used where %ages are involved. For example, population growth for first year is 30%, for second year is 25% and for third year, it is 15%. Then…
Tips & Tricks 82 – Date for Nth Day of the Year
Suppose A1 contains the Year and you are asked to find 69th day of the year which is contained in A2. Then formula for finding Nth day of the year would be =DATE(A1,1,1)+A2-1
Tips & Tricks 81 – First Working Day of the Month if a Date is Given
If A1 contains a date, then formula for First Working Day of the month would be =WORKDAY(EOMONTH(A1,-1),1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110") Where 0000110 is a 7 character string, 1 represents a…
Tips & Tricks 80 – Last Working Day of the Month If a Date is Given
If A1 holds a date, the formula for calculating last Working Day of the month would be =WORKDAY(EOMONTH(A1,0)+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula – =WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110") Where 0000110 is a 7 character string, 1 represents…