Top Excel Formulas6 min read

General Formulas

What are Excel formulas?

Excel formulas are expressions that perform calculations or manipulate data in Microsoft Excel. They are entered into cells and can range from simple arithmetic operations to complex functions. Here are some common types of Excel formulas:

1. Arithmetic Formulas: Perform basic mathematical operations like addition, subtraction, multiplication, and division. For example: =A1+B1.
2. Functions: Excel provides numerous built-in functions for various purposes, such as:

• SUM: Adds up a range of cells.
• AVERAGE: Calculates the average of a range.
• IF: Performs conditional tests.
• VLOOKUP and HLOOKUP: Searches for a value in a table.

1. Cell References: Formulas often reference other cells. Absolute references (e.g., $A$1) remain fixed, while relative references (e.g., A1) adjust when copied to other cells.
2. Concatenation: Combines text from different cells. For example: =A1 & ” ” & B1 combines the contents of cells A1 and B1 with a space in between.
3. Logical Formulas: Use logical operators like IF, AND, OR to perform conditional calculations based on given criteria.
4. Date and Time Functions: Excel has functions for working with dates and times, such as TODAY(), NOW(), and DATEDIF.
5. Math and Trigonometry Functions: Perform advanced mathematical operations like exponentiation, logarithms, and trigonometric functions.
6. Statistical Formulas: Calculate statistical measures like mean, median, standard deviation using functions like AVERAGE, MEDIAN, STDEV.

Formulas enable users to automate calculations, analyze data, and create dynamic and interactive spreadsheets in Excel.

Here are the top excel formulas that will help you productively use the office application

(i) SUM

(ii) AVERAGE

(iii) XLOOKUP

(iv) VLOOKUP

(v) HLOOKUP

(vi) COUNT

(vii) CONCATENATE

(viii) SUBTOTAL

(ix) MODULUS

(x) LEN

(xi) REPLACE

(xii) POWER

(xiii) CEILING

(xiv) FLOOR

Financial Formulas

Here are the top Excel financial formulas that will help you productively use the Office application

XNPV

The XNPV function in Excel calculates the net present value of a series of cash flows at irregular intervals. It requires three arguments: the discount rate, an array of cash flows, and an array of corresponding dates. The formula syntax is:

=XNPV(rate, values, dates)

• rate: The discount rate for the investment.
• values: An array of cash flows that correspond to the dates.
• dates: An array of dates that correspond to the cash flows.

Make sure the dates are in chronological order, and the first cash flow corresponds to the first date. The result is the net present value of the investment.

Example:

Let’s assume you have the following cash flows and dates:

You want to calculate the XNPV with a discount rate of 5%.

Step-by-Step Guide:

1. Enter the Data in Excel
2. Use the XNPV Formula: Assuming the discount rate is 5% (or 0.05), and your data is in cells A2, you can use the following formula: =XNPV(0.05, B2:B5, A2:A5)

Calculation Breakdown:

• Date: 01/01/2023, Cash Flow: -$100,000
  • This is the initial investment, so it is not discounted.
• Date: 01/06/2023, Cash Flow: $30,000
  • This is received after approximately 5 months. The discount factor is calculated based on the number of days from the start date.
• Date: 01/12/2023, Cash Flow: $40,000
  • This is received after approximately 11 months.
• Date: 01/01/2024, Cash Flow: $50,000
  • This is received after exactly one year.

Result:

When you enter the formula =XNPV(0.05, B2:B5, A2:A5) into Excel, it will calculate the present value of these cash flows discounted at a 5% annual rate, considering the exact dates of the cash flows.

This formula gives you a more accurate NPV by considering the exact timing of the cash flows rather than assuming they occur at regular intervals.

XIRR

XIRR: The XIRR function in Excel calculates the internal rate of return for a series of cash flows at irregular intervals. Its formula syntax is:

=XIRR(values, dates, [guess])

• values: An array of cash flows that correspond to the dates.
• dates: An array of dates that correspond to the cash flows.
• [guess]: (Optional) An initial guess for the internal rate of return. If omitted, Excel uses 0.1 (10%) as the default.

Make sure the dates are in chronological order, and the first cash flow corresponds to the first date. The function returns the internal rate of return for the investment.

Example:

Let’s use the same cash flows and dates as in the previous example:

You want to calculate the XIRR for these cash flows.

Step-by-Step Guide:

1. Enter the Data in Excel:
2. Use the XIRR Formula: Assuming your data is in cells A2, you can use the following formula =XIRR(B2:B5, A2:A5)

Calculation Breakdown:

• Date: 01/01/2023, Cash Flow: -$100,000
  • This is the initial investment.
• Date: 01/06/2023, Cash Flow: $30,000
  • This is received after approximately 5 months.
• Date: 01/12/2023, Cash Flow: $40,000
  • This is received after approximately 11 months.
• Date: 01/01/2024, Cash Flow: $50,000
  • This is received after exactly one year.

Result:

When you enter the formula =XIRR(B2:B5, A2:A5) into Excel, it will calculate the internal rate of return for these cash flows, considering the exact dates of the cash flows.

This formula helps you find the annualized rate of return for a schedule of cash flows occurring at irregular intervals, which is particularly useful for more complex financial scenarios where cash flows are not evenly spaced.

MIRR

The MIRR (Modified Internal Rate of Return) function in Excel calculates the internal rate of return for a series of cash flows, considering both the cost of investment and the interest received on reinvestment of cash. The syntax for MIRR is:

MIRR(values, finance_rate, reinvest_rate)

Let’s use the same cash flows as before and assume:

• Finance rate (cost of investment): 5% (0.05)
• Reinvestment rate: 7% (0.07)

Step-by-Step Guide:

Enter the Data in Excel:

Use the MIRR Formula:

Assuming your cash flows are in cells B2, and you want to calculate MIRR with a finance rate of 5% and a reinvestment rate of 7%, you can use the following formula:

Calculation Breakdown:

• Initial Investment (outflow): -$100,000 on 01/01/2023
• Cash Inflows:
  • $30,000 on 01/06/2023
  • $40,000 on 01/12/2023
  • $50,000 on 01/01/2024

Result:

When you enter the formula =MIRR(B2:B5, 0.05, 0.07) into Excel, it will calculate the modified internal rate of return for these cash flows, taking into account the cost of investment and the interest rate for reinvestment.

This formula is useful for providing a more realistic rate of return on investments, considering that interim cash flows can be reinvested at a specific reinvestment rate.

(iv) PMT

(v) EFFECT

(vi) IPMT

(vii) DB

(viii) RATE