Sometimes percentages can be frustrating because it’s not always easy to remember what we learned about them in school. Let Excel do the work for you – simple formulas can help you find the percentage of a total, for example, or the percentage difference between two numbers.
Find the percentage of a total
- To find out, divide $20,000 by $125,000. Here’s the formula in cell C2: =B2/A2. The result is shown as 0.16 because cell C2 is not formatted as a percentage.
- To format 0.16 as a percentage, (which will also remove the zero) on the Home tab, click the Percentage button.If you are using Excel Online, click Home > Number Format > Percentage.Now we see that $20,000 is 16% of $125,000.Tip: Formatting is the key to getting answers shown in percentages. Find out more in displaying numbers as percentages.
Find the percentage of change between two numbers
- First, click in cell B3 to apply the Percentage format to the cell. On the Home tab, click the Percentage button.If you are using Excel Online, click Home > Number Format > Percentage.
- In cell B3, divide the second year’s sales ($598,634.00) by the first year ($485,000.00), and then subtract 1.
- Here’s the formula in cell C3. =(B2/A2)-1. The percentage of change between the two years is 23%.Notice the parentheses around (B2/A2). Excel calculates what’s in parentheses first, and then subtracts the 1.
See also
Find the percentage of a total
- Click any blank cell.
- Type =42/50, and then press RETURN .The result is 0.84.
- Select the cell that contains the result from step 2.
- On the Home tab, click .The result is 84.00%, which is the percentage of correct answers on the test.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
Find the percentage of change between two numbers
- Click any blank cell.
- Type =(2500-2342)/2342, and then press RETURN .The result is 0.06746.
- Select the cell that contains the result from step 2.
- On the Home tab, click .The result is 6.75%, which is the percentage of increase in earnings.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
- Click any blank cell.
- Type =(2425-2500)/2500, and then press RETURN .The result is -0.03000.
- Select the cell that contains the result from step 2.
- On the Home tab, click .The result is -3.00%, which is the percentage of decrease in earnings.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
Find the total when you know the amount and percentage
- Click any blank cell.
- Type =15/0.75, and then press RETURN .The result is 20.
- Select the cell that contains the result from step 2.
- In newer versions:On the Home tab, click .The result is $20.00, which is the original price of the shirt.In Excel for Mac 2011:On the Home tab, under Number, click CurrencyThe result is $20.00, which is the original price of the shirt.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
Find an amount when you know the total and percentage
- Click any blank cell.
- Type =800*0.089, and then press RETURN.The result is 71.2.
- Select the cell that contains the result from step 2.
- In newer versions:On the Home tab, click .In Excel for Mac 2011:On the Home tab, under Number, click CurrencyThe result is $71.20, which is the sales tax amount for the computer.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
Increase or decrease a number by a percentage
- Click any blank cell.
- Type =113*(1+0.25), and then press RETURN .The result is 141.25.
- Select the cell that contains the result from step 2.
- In newer versions:On the Home tab, click .In Excel for Mac 2011:On the Home tab, under Number, click CurrencyThe result is $141.25, which is a 25% increase in weekly food expenditures.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
- Click any blank cell.
- Type =113*(1-0.25), and then press RETURN .The result is 84.75.
- Select the cell that contains the result from step 2.
- In newer versions:On the Home tab, click .In Excel for Mac 2011:On the Home tab, under Number, click CurrencyThe result is $84.75, which is a 25% reduction in weekly food expenditures.Note: To change the number of decimal places that appear in the result, click Increase Decimal or Decrease Decimal .
See also
This article describes the formula syntax and usage of the RATE function in Microsoft Excel.
Description
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])
The RATE function syntax has the following arguments:
- Nper Required. The total number of payment periods in an annuity.
- Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
- Pv Required. The present value — the total amount that a series of future payments is worth now.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.
- Type Optional. The number 0 or 1 and indicates when payments are due.
Set type equal to | If payments are due |
---|---|
0 or omitted | At the end of the period |
1 | At the beginning of the period |
- Guess Optional. Your guess for what the rate will be.
- If you omit guess, it is assumed to be 10 percent.
- If RATE does not converge, try different values for guess. RATE usually converges if guess is between 0 and 1.
Remarks
Make sure that you are consistent about the units you use for specifying guess and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for guess and 4*12 for nper. If you make annual payments on the same loan, use 12% for guess and 4 for nper.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Data | Description | |
---|---|---|
4 | Years of the loan | |
-200 | Monthly payment | |
8000 | Amount of the loan | |
Formula | Description | Result |
=RATE(A2*12, A3, A4) | Monthly rate of the loan with the terms entered as arguments in A2:A4. | 1% |
=RATE(A2*12, A3, A4)*12 | Annual rate of the loan with the same terms. | 9.24% |