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 →

Problem: Columns A & B contain dates and their matching sales totals. We want to determine which day of the week corresponds with each date in column A, and then calculate an average sales figure for each day of the … Continue reading →

In this article we will learn about how to create loan amortization schedule in Microsoft Excel 2010. To calculate loan payment we will use the “RATE”, “NPER”, “PV”, “PMT”, “PPMT” and “IPMT” formulae. PMT: Returns the regular monthly payment on … Continue reading →

Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. In the screen shot is a list of functions and necessary syntax for loan calculations. See the formulas in … Continue reading →