In this article we will learn how to calculate the investment value for future or annuity for the present value by using the functions of Microsoft Excel. When we made investment, the amount paid is entered in negative value so … Continue reading →

In this article, we will learn how to find average sales totals by day of the week in Microsoft Excel 2010. To find the average sales total by particular day of the week, i.e. we want to determine which day … Continue reading →

To find a formula that will retrieve the output by rounding numerical substrings to 3 decimal places, we will use a combination of LEFT, FIND, MID & ROUND functions to retrieve the output. Let’s say cell A1 contains 12.34567<>3.4567and the … Continue reading →

Problem: Calculating the credit that remains after each purchase is made. Once the credit limit (stored in cell B1) is exceeded, zero is to be returned for all further purchases. Solution: Use the MAX and SUM functions in the following … Continue reading →

Problem: List1 (column A) contains 10 numbers, two of which (0 & 500) are significantly smaller or greater than the remainder. If included in an average calculation, these exceptional values would disproportionately influence the result. We, therefore, need a way … Continue reading →

Problem: The following PMT formula calculates the monthly payment for a $100,000 mortgage, repaid over a period of 20 years, at 8% annual interest: =PMT(8%/12,12*20,100000,0,0) As Canadian interest rates are calculated semi-annually, rather than annually, the above formula will not … Continue reading →

Problem: The range B2:G6 contains some of the following parameters for a number of different loans (each occupying a separate column): Principal, interest rate, total number of payments, and monthly payment. Based on those details, we want to calculate the … Continue reading →

Problem: Columns A & B contain a log of all the sales from a particular day. Each row consists of the name of an item and the quantity sold. Columns D & E show the initial inventory for all items. … Continue reading →

In this article we will learn about how to calculate the loan amortization schedule in Excel. To calculate loan payment we will use the “RATE”, “NPER”, “PV”, “PMT”, “PPMT” and “IPMT” formulae. All these formulae will help to create the amortization … Continue reading →

In this article we are going to learn about how to calculate loan payments. Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. Below are the Monthly payment … Continue reading →