# Excel Financial Formulas Tips

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 →

Problem: Rounding the numbers in the string stored in cell A2 (12.34567<>3.4567) to 3 decimal places. Solution: Use the ROUND, LEFT, FIND, and MID functions in the following formula: =ROUND(LEFT(A2,FIND(“<>”,A2)-1),3)&”<>”&ROUND(MID(A2,FIND(“<>”,A2)+2,255),3) Screenshot // Rounding Numerical Substrings

Simple amortization schedule See screen shot 1. Formulas that do not appear in the figure: The formula in cell C17 is =LOAN (LOAN is the name of cell C4). The formula in cell C18 is =G17; copy the formula from … 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 →