99+ List of Formulae of Excel for Finance

Creating a comprehensive list of 100 Excel formulas tailored for finance is an excellent resource for professionals, students, and enthusiasts looking to enhance their financial analysis skills. This list can form the backbone of a valuable eBook, providing practical tools for a wide range of financial tasks, from basic calculations to complex financial modeling. Here’s a curated list of 100 Excel formulas and functions essential for finance, categorized for ease of understanding and application.

Basic Arithmetic and Statistical Functions

1. SUM(range) – Adds up a range of cells.
2. AVERAGE(range) – Calculates the average of a range of cells.
3. MEDIAN(range) – Finds the median value in a range.
4. MIN(range) – Identifies the minimum value in a range.
5. MAX(range) – Identifies the maximum value in a range.
6. COUNT(range) – Counts the number of cells with numerical data in a range.
7. COUNTA(range) – Counts the number of non-empty cells in a range.

Financial Functions

8. PV(rate, nper, pmt) – Calculates the present value of an investment.
9. FV(rate, nper, pmt) – Calculates the future value of an investment.
10. NPV(rate, value1, [value2], ...) – Calculates the net present value of an investment based on a discount rate and a series of future payments and income.
11. IRR(values) – Returns the internal rate of return for a series of cash flows.
12. XIRR(values, dates) – Returns the internal rate of return for a schedule of cash flows that are not necessarily periodic.
13. PMT(rate, nper, pv) – Calculates the payment for a loan based on constant payments and a constant interest rate.
14. PPMT(rate, per, nper, pv) – Calculates the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
15. IPMT(rate, per, nper, pv) – Calculates the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
16. RATE(nper, pmt, pv, [fv], [type], [guess]) – Calculates the interest rate per period of an annuity.
17. NPER(rate, pmt, pv, [fv], [type]) – Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
18. SLN(cost, salvage, life) – Calculates the straight-line depreciation of an asset for one period.
19. DB(cost, salvage, life, period, [month]) – Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
20. DDB(cost, salvage, life, period, [factor]) – Calculates the depreciation of an asset for a specified period using the double-declining balance method.

Lookup & Reference Functions

21. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – Searches for a value in the first column of a table and returns a value in the same row from a specified column.
22. HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) – Searches for a value in the first row of a table and returns a value in the same column from a specified row.
23. INDEX(array, row_num, [column_num]) – Returns the value of a specified cell or array of cells in a table.
24. MATCH(lookup_value, lookup_array, [match_type]) – Searches for a specified item in a range of cells, and then returns the relative position of that item.
25. XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) – Searches a range or an array, and returns an item corresponding to the first match it finds.

Date and Time Functions

26. TODAY() – Returns the current date.
27. NOW() – Returns the current date and time.
28. DATE(year, month, day) – Returns the specified date.
29. DATEDIF(start_date, end_date, "unit") – Calculates the difference between two dates.
30. EDATE(start_date, months) – Returns the date that is a specified number of months before or after the start date.
31. EOMONTH(start_date, months) – Returns the last day of the month that is a specified number of months before or after the start date.
32. YEARFRAC(start_date, end_date, [basis]) – Returns the year fraction representing the number of whole days between start_date and end_date.

Text Functions

33. CONCATENATE(text1, [text2], ...) – Joins two or more text strings into one string.
34. TEXT(value, format_text) – Formats a number and converts it to text.
35. LEFT(text, [num_chars]) – Returns the leftmost characters from a text value.
36. RIGHT(text, [num_chars]) – Returns the rightmost characters from a text value.
37. MID(text, start_num, num_chars) – Returns a specific number of characters from a text string starting at the position you specify.
38. LOWER(text) – Converts all letters in a text string to lowercase.
39. UPPER(text) – Converts all letters in a text string to uppercase.
40. PROPER(text) – Capitalizes the first letter in each word of a text string.
41. TRIM(text) – Removes extra spaces from a text string.

Logical Functions

42. IF(logical_test, [value_if_true], [value_if_false]) – Performs a logical test and returns one value for a TRUE result, and another for a FALSE result.
43. AND(logical1, [logical2], ...) – Returns TRUE if all of its arguments are TRUE; otherwise, returns FALSE.
44. OR(logical1, [logical2], ...) – Returns TRUE if any argument is TRUE; otherwise, returns FALSE.
45. NOT(logical) – Reverses the logic of its argument.
46. IFERROR(value, value_if_error) – Returns a specified value if the formula evaluates to an error; otherwise, returns the result of the formula.
47. IFS(conditions) – Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

Data Analysis Functions

48. SUMIF(range, criteria, [sum_range]) – Adds the cells specified by a given condition or criteria.
49. SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) – Adds the cells in a range that meet multiple criteria.
50. COUNTIF(range, criteria) – Counts the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
51. COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) – Counts the number of cells that meet multiple criteria.
52. AVERAGEIF(range, criteria, [average_range]) – Calculates the average of all the cells in a range that meet a given criterion.
53. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) – Calculates the average of all cells that meet multiple criteria.
54. MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) – Returns the maximum value among cells specified by a given set of conditions or criteria.
55. MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) – Returns the minimum value among cells specified by a given set of conditions or criteria.

Advanced Financial Functions

56. XNPV(rate, values, dates) – Returns the net present value for a schedule of cash flows that is not necessarily periodic.
57. XIRR(values, dates) – Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
58. AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis]) – Returns the prorated linear depreciation of an asset for each accounting period.
59. AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) – Returns the depreciation for each accounting period by using a depreciation coefficient.
60. CUMIPMT(rate, nper, pv, start_period, end_period, type) – Returns the cumulative interest paid between two periods.
61. CUMPRINC(rate, nper, pv, start_period, end_period, type) – Returns the cumulative principal paid on a loan between two periods.
62. DB(cost, salvage, life, period, [month]) – Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
63. DDB(cost, salvage, life, period, [factor]) – Calculates the depreciation of an asset for a specified period using the double-declining balance method.
64. SYD(cost, salvage, life, per) – Returns the sum-of-years’ digits depreciation of an asset for a specified period.
65. FVSCHEDULE(principal, schedule) – Returns the future value of an initial principal after applying a series of compound interest rates.
66. INTRATE(settlement, maturity, investment, redemption, [basis]) – Returns the interest rate for a fully invested security.
67. MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) – Returns the Macauley modified duration for a security with an assumed par value of $100.
68. ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis]) – Returns the price per $100 face value of a security with an odd first period.
69. ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis]) – Returns the yield of a security with an odd first period.
70. ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) – Returns the price per $100 face value of a security with an odd last period.
71. ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) – Returns the yield of a security with an odd last period.
72. PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) – Returns the price per $100 face value of a security that pays periodic interest.
73. PRICEDISC(settlement, maturity, discount, redemption, [basis]) – Returns the price per $100 face value of a discounted security.
74. PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) – Returns the price per $100 face value of a security that pays interest at maturity.
75. RECEIVED(settlement, maturity, investment, discount, [basis]) – Returns the amount received at maturity for a fully invested security.
76. TBILLEQ(settlement, maturity, discount) – Returns the bond-equivalent yield for a Treasury bill.
77. TBILLPRICE(settlement, maturity, discount) – Returns the price per $100 face value for a Treasury bill.
78. TBILLYIELD(settlement, maturity, pr) – Returns the yield for a Treasury bill.
79. YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) – Returns the yield on a security that pays periodic interest.
80. YIELDDISC(settlement, maturity, pr, redemption, [basis]) – Returns the annual yield for a discounted security; for example, a Treasury bill.
81. YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) – Returns the annual yield of a security that pays interest at maturity.

Conditional and Logical Analysis

82. SUMPRODUCT(array1, [array2], ...) – Returns the sum of the products of corresponding ranges or arrays.
83. SUMX2MY2(array_x, array_y) – Returns the sum of the differences of squares of corresponding values in two arrays.
84. SUMX2PY2(array_x, array_y) – Returns the sum of the sum of squares of corresponding values in two arrays.
85. SUMXMY2(array_x, array_y) – Returns the sum of squares of differences of corresponding values in two arrays.
86. TRANSPOSE(array) – Returns the transpose of an array.
87. FREQUENCY(data_array, bins_array) – Returns a frequency distribution as a vertical array.
88. GROWTH(known_y's, [known_x's], [new_x's], [const]) – Calculates predicted exponential growth by using existing data.
89. LINEST(known_y's, [known_x's], [const], [stats]) – Returns the parameters of a linear trend.
90. LOGEST(known_y's, [known_x's], [const], [stats]) – Returns the parameters of an exponential trend.
91. TREND(known_y's, [known_x's], [new_x's], [const]) – Returns values along a linear trend.
92. FORECAST.LINEAR(x, known_y's, known_x's) – Returns a value along a linear trend.

Matrix Functions

93. MMULT(array1, array2) – Returns the matrix product of two arrays.
94. MDETERM(array) – Returns the matrix determinant of an array.
95. MINVERSE(array) – Returns the matrix inverse of an array.

Error Handling and Data Validation

96. ERROR.TYPE(error_val) – Returns a number corresponding to an error type.
97. ISERROR(value) – Returns TRUE if the value is any error value.
98. ISERR(value) – Returns TRUE if the value is any error value except #N/A.
99. ISNA(value) – Returns TRUE if the value is the #N/A error value.
100. IFNA(value, value_if_na) – Returns the value you specify if the expression resolves to