Mastering Excel for Finance: Your Ultimate Cheat Sheet from Basics to Pro: Expert Level Excel Finance Cheatsheet

Formulas & Questions

Introduction

Welcome to your ultimate Excel Finance Cheat Sheet! In the world of finance, Excel is an indispensable tool that empowers professionals to analyze data, create forecasts, and make informed decisions efficiently. This blog post is designed to guide you through four levels of Excel formulas: Basic, Intermediate, Expert, and Professional (Stealth Mode). Each section includes essential formulas, practical examples, and challenging questions to help reinforce your understanding.

Whether you are just starting or looking to refine your advanced skills, this cheat sheet will provide you with a structured approach to mastering Excel in the finance domain. Let’s dive in and enhance your Excel proficiency!

What to Expect

In this blog cheat sheet episodes, you’ll find:

• 20 Cheat Sheets for Each Level: A carefully selected collection of key Excel formulas that cater to your current skill level.
• 10 Questions per Level: Thought-provoking questions that test your knowledge and application of the formulas, complete with answers and examples.
• Progressive Learning: The format allows you to build on your skills progressively, ensuring a comprehensive understanding of Excel’s capabilities.
• Real-World Examples: Practical applications of each formula, helping you connect theory with real-life financial tasks.Intermediate Level Excel Finance Cheatsheet

Formula’s : -

1. VLOOKUP:

• Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Example: =VLOOKUP("Product A", A1:C10, 3, FALSE) - Looks for "Product A" in the first column of the range A1 and returns the corresponding value from the 3rd column.

2. HLOOKUP:

• Formula: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Example: =HLOOKUP("Q1", A1:E5, 2, FALSE) - Searches for "Q1" in the first row of A1 and returns the value from the 2nd row.

3. FILTER:

• Formula: =FILTER(array, include, [if_empty])
• Example: =FILTER(A1:B10, A1:A10 > 100, "No Results") - Returns the rows from A1where the value in A1is greater than 100.

4. UNIQUE:

• Formula: =UNIQUE(array, [by_col], [exactly_once])
• Example: =UNIQUE(A1:A10) - Returns a list of unique values from A1.

5. XLOOKUP:

• Formula: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Example: =XLOOKUP("Product B", A1:A10, B1:B10, "Not Found") - Searches for "Product B" in A1and returns the corresponding value from B1.

6. NORM.DIST:

• Formula: =NORM.DIST(x, mean, standard_dev, cumulative)
• Example: =NORM.DIST(85, 70, 10, TRUE) - Returns the cumulative distribution function of the normal distribution for a given x.

7. NORM.INV:

• Formula: =NORM.INV(probability, mean, standard_dev)
• Example: =NORM.INV(0.95, 70, 10) - Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

8. PMT:

• Formula: =PMT(rate, nper, pv, [fv], [type])
• Example: =PMT(5%/12, 60, -10000) - Calculates the monthly payment for a loan of $10,000 at a 5% annual interest rate for 60 months.

9. FV:

• Formula: =FV(rate, nper, pmt, [pv], [type])
• Example: =FV(5%/12, 60, -200, -5000) - Calculates the future value of an investment with periodic payments of $200, starting with $5000.

10. PV:

• Formula: =PV(rate, nper, pmt, [fv], [type])
• Example: =PV(5%/12, 60, -200) - Calculates the present value of an investment with periodic payments of $200.

11. COVARIANCE.P:

• Formula: =COVARIANCE.P(array1, array2)
• Example: =COVARIANCE.P(A1:A10, B1:B10) - Returns the population covariance between two data sets.

12. COVARIANCE.S:

• Formula: =COVARIANCE.S(array1, array2)
• Example: =COVARIANCE.S(A1:A10, B1:B10) - Returns the sample covariance between two data sets.

13. CORREL:

• Formula: =CORREL(array1, array2)
• Example: =CORREL(A1:A10, B1:B10) - Returns the correlation coefficient between two datasets.

14. INDEX MATCH Combination:

• Formula: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• Example: =INDEX(B1:B10, MATCH("Product C", A1:A10, 0)) - Finds the price of "Product C" using the INDEX and MATCH functions.

15. XNPV:

• Formula: =XNPV(rate, values, dates)
• Example: =XNPV(10%, A1:A10, B1:B10) - Calculates the net present value for a series of cash flows.

16. XIRR:

• Formula: =XIRR(values, dates, [guess])
• Example: =XIRR(A1:A10, B1:B10) - Calculates the internal rate of return for a series of cash flows.

17. SLN:

• Formula: =SLN(cost, salvage, life)
• Example: =SLN(10000, 1000, 5) - Calculates the straight-line depreciation of an asset.

18. DB:

• Formula: =DB(cost, salvage, life, period)
• Example: =DB(10000, 1000, 5, 2) - Calculates the depreciation of an asset for a specified period using the declining balance method.

19. XOR:

• Formula: =XOR(logical1, [logical2], ...)
• Example: =XOR(A1 > 10, B1 < 5) - Returns TRUE if an odd number of arguments are TRUE.

20. TRANSPOSE:

• Formula: =TRANSPOSE(array)
• Example: =TRANSPOSE(A1:A10) - Converts a vertical range to a horizontal range or vice versa.

Questions and Answers

1. Question: How can you look up the price of “Product A” using VLOOKUP?

• Answer: Use =VLOOKUP("Product A", A1:C10, 3, FALSE).

2. Question: What formula would you use to find the corresponding value for “Q2” in the second row?

• Answer: Use =HLOOKUP("Q2", A1:E5, 2, FALSE).

3. Question: How do you filter a range to show only values greater than 100?

• Answer: Use =FILTER(A1:B10, A1:A10 > 100, "No Results").

4. Question: How can you get a list of unique product names from a range?

• Answer: Use =UNIQUE(A1:A10).

5. Question: If you want to calculate the monthly payment for a loan using PMT, which formula do you use?

• Answer: Use =PMT(rate, nper, pv).

6. Question: How can you calculate the correlation between two data sets?

• Answer: Use =CORREL(A1:A10, B1:B10).

7. Question: If you want to get the future value of an investment using FV, what formula do you use?

• Answer: Use =FV(rate, nper, pmt, [pv], [type]).

8. Question: How can you find the NPV of a series of cash flows?

• Answer: Use =XNPV(rate, values, dates).

9. Question: What formula would you use to calculate straight-line depreciation?

• Answer: Use =SLN(cost, salvage, life).

10. Question: If you want to transpose a vertical range into a horizontal range, what formula do you use?

• Answer: Use =TRANSPOSE(A1:A10).

I hope now you have better understanding starting with excel formula’s and what question you expect from an interview stand point. Let’s see you in next level :)

Cheers!