1. *Net Present Value (NPV)*: =NPV(rate, value1, [value2], ...

- *Purpose*: Calculates the present value of a series of future cash flows based on a discount rate.

2. *Internal Rate of Return (IRR)*: =IRR(values, [guess])

- *Purpose*: Determines the discount rate that makes the net present value of cash flows zero.

3. *Future Value (FV)*: =FV(rate, nper, pmt, [pv], [type])

- *Purpose*: Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

4. *Present Value (PV)*: =PV(rate, nper, pmt, [fv], [type])

- *Purpose*: Calculates the present value of an investment based on future payments, a constant interest rate, and a number
of periods.

5. *Payment (PMT)*: =PMT(rate, nper, pv, [fv], [type])

- *Purpose*: Calculates the payment for a loan based on constant payments and a constant interest rate.

6. *Interest Payment (IPMT)*: =IPMT(rate, per, nper, pv, [fv], [type])

- *Purpose*: Calculates the interest payment for a given period of an investment or loan.

7. *Principal Payment (PPMT)*: =PPMT(rate, per, nper, pv, [fv], [type])

- *Purpose*: Calculates the principal payment for a given period of an investment or loan.

8. *Net Present Value with Dates (XNPV)*: =XNPV(rate, values, dates)

- *Purpose*: Calculates the net present value for a series of cash flows that occur at irregular intervals.

9. *Internal Rate of Return with Dates (XIRR)*: =XIRR(values, dates, [guess])

- *Purpose*: Calculates the internal rate of return for a series of cash flows that occur at irregular intervals.

10. *Modified Internal Rate of Return (MIRR)*: =MIRR(values, finance_rate, reinvest_rate)

- *Purpose*: Calculates the internal rate of return considering both the cost of investment and the interest received on
reinvestment of cash.

11. *Number of Periods (NPER)*: =NPER(rate, pmt, pv, [fv], [type])

- *Purpose*: Calculates the number of periods for an investment based on periodic, constant payments and a constant
interest rate.

12. *Rate of Interest (RATE)*: =RATE(nper, pmt, pv, [fv], [type], [guess])

- *Purpose*: Calculates the interest rate per period of an annuity.

13. *Effective Annual Interest Rate (EFFECT)*: =EFFECT(nominal_rate, npery)

- *Purpose*: Calculates the effective annual interest rate given the nominal rate and the number of compounding periods per
year.

14. *Nominal Interest Rate (NOMINAL)*: =NOMINAL(effect_rate, npery)

- *Purpose*: Calculates the nominal annual interest rate given the effective rate and the number of compounding periods per
year.

15. *Straight-Line Depreciation (SLN)*: =SLN(cost, salvage, life)

- *Purpose*: Calculates the depreciation of an asset for one period using the straight-line method.

16. *Declining Balance Depreciation (DB)*: =DB(cost, salvage, life, period, [month])

- *Purpose*: Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.

17. *Double-Declining Balance Depreciation (DDB)*: =DDB(cost, salvage, life, period, [factor])

- *Purpose*: Calculates the depreciation of an asset for a specified period using the double-declining balance method.

18. *Bond Price (PRICE)*: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

- *Purpose*: Calculates the price per \$100 face value of a security that pays periodic interest.
19. *Bond Yield (YIELD)*: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

- *Purpose*: Calculates the yield of a security that pays periodic interest.

20. *Accrued Interest (ACCRINT)*: =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis])

- *Purpose*: Calculates the accrued interest for a security that pays periodic interest.

21. *Accrued Interest at Maturity (ACCRINTM)*: =ACCRINTM(issue, settlement, rate, par, [basis])

- *Purpose*: Calculates the accrued interest for a security that pays interest at maturity.

22. *Cumulative Interest Payment (CUMIPMT)*: =CUMIPMT(rate, nper, pv, start_period, end_period, type)

- *Purpose*: Calculates the cumulative interest paid between two periods.

23. *Cumulative Principal Payment (CUMPRINC)*: =CUMPRINC(rate, nper, pv, start_period, end_period, type)

- *Purpose*: Calculates the cumulative principal paid on a loan between two periods.

24. *Discount Rate (DISC)*: =DISC(settlement, maturity, pr, redemption, [basis])

- *Purpose*: Calculates the discount rate for a security.

25. *Dollar Price (DOLLARDE)*: =DOLLARDE(fractional_dollar, fraction)

- *Purpose*: Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.

26. *Dollar Fraction (DOLLARFR)*: =DOLLARFR(decimal_dollar, fraction)

- *Purpose*: Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a f raction.

27. *Duration (DURATION)*: =DURATION(settlement, maturity, coupon, yld, frequency, [basis])

- *Purpose*: Calculates the annual duration of a security with periodic interest payments.

28. *Macauley Modified Duration (MDURATION)*: =MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

- *Purpose*: Calculates the Macauley modified duration for a security with an assumed par value of \$100.

29. *Interest Rate (INTRATE)*: =INTRATE(settlement, maturity, investment, redemption, [basis])

- *Purpose*: Calculates the interest rate for a fully invested security.

30. *Yield to Maturity (YTM)*: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

- *Purpose*: Calculates the yield to maturity of a bond.

31. *Yield to Call (YTC)*: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

- *Purpose*: Calculates the yield to call of a bond.

32. *Yield to Worst (YTW)*: =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

- *Purpose*: Calculates the yield to worst of a bond.

33. *Coupon Days (COUPDAYS)*: =COUPDAYS(settlement, maturity, frequency, [basis])

- *Purpose*: Calculates the number of days in the coupon period that contains the settlement date.

34. *Coupon Days Before Settlement (COUPDAYBS)*: =COUPDAYBS(settlement, maturity, frequency, [basis])

- *Purpose*: Calculates the number of days from the beginning of the coupon period to the settlement date.

35. *Coupon Days After Settlement (COUPDAYSNC)*: =COUPDAYSNC(settlement, maturity, frequency, [basis])

- *Purpose*: Calculates the number of days from the settlement date to the next coupon date.

36. *Coupon Number (COUPNUM)*: =COUPNUM(settlement, maturity, frequency, [basis])

- *Purpose*: Calculates the number of coupons payable between the settlement date and maturity date.

37. *Coupon Next Date (COUPNCD)*: =COUPNCD(settlement, maturity, frequency, [basis])

38. *Coupon Previous Date (COUPPCD)*: =COUPPCD(settlement, maturity, frequency, [basis])

- *Purpose*: Calculates the previous coupon date before the settlement date.

39. *Price with Odd First Period (ODDFPRICE)*: =ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption,
frequency, [basis])

- *Purpose*: Calculates the price per \$100 face value of a security with an odd first period.

40. *Price with Odd Last Period (ODDLPRICE)*: =ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption,
frequency, [basis])

- *Purpose*: Calculates the price per \$100 face value of a security with

Sure! Here are 30 different scenarios using the VLOOKUP formula in Excel, along with the specific formula and relevant
keyboard shortcuts for each scenario:

1. *Basic Lookup*: =VLOOKUP(A2, B2:D10, 2, FALSE)

- *Purpose*: Find a value in a table based on a single criterion.

2. *Approximate Match*: =VLOOKUP(A2, B2:D10, 2, TRUE)

- *Purpose*: Find the closest match when an exact match is not found.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

3. *Exact Match*: =VLOOKUP(A2, B2:D10, 2, FALSE)

- *Purpose*: Ensure the lookup value exactly matches the data in the table.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

4. *Two-Way Lookup*: =VLOOKUP(A2, B2:D10, MATCH(B1, B1:D1, 0), FALSE)

- *Purpose*: Find a value based on both row and column criteria.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

5. *Using Wildcards*: =VLOOKUP("*"&A2&"*", B2:D10, 2, FALSE)

- *Purpose*: Perform a partial match lookup using wildcards.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

6. *Case-Sensitive Lookup*: =INDEX(D2:D10, MATCH(TRUE, EXACT(A2, B2:B10), 0))

- *Purpose*: Perform a case-sensitive lookup.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

7. *Multiple Criteria Lookup*: =VLOOKUP(A2&B2, CHOOSE({1,2}, B2:B10&C2:C10, D2:D10), 2, FALSE)

- *Purpose*: Lookup based on multiple criteria by combining columns.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

8. *Lookup with Drop-Down List*: =VLOOKUP(DROPDOWN, B2:D10, 2, FALSE)

- *Purpose*: Use a drop-down list to select the lookup value.

- *Shortcut*: Alt + ↓ to open the drop-down list.

9. *Finding Last Value*: =LOOKUP(2, 1/(B2:B10<>""), B2:B10)

- *Purpose*: Find the last non-empty value in a column.

- *Shortcut*: Ctrl + ↓ to navigate to the last cell in a column.

10. *Handling Errors*: =IFERROR(VLOOKUP(A2, B2:D10, 2, FALSE), "Not Found")

- *Purpose*: Handle errors gracefully when the lookup value is not found.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

11. *Dynamic Range Lookup*: =VLOOKUP(A2, INDIRECT("B2:D"&COUNTA(B:B)), 2, FALSE)

- *Purpose*: Use a dynamic range for the lookup table.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

12. *Lookup in Multiple Sheets*: =VLOOKUP(A2, Sheet2!B2:D10, 2, FALSE)

- *Purpose*: Perform a lookup across different sheets.

- *Shortcut*: Ctrl + Page Down to navigate to the next sheet.

13. *Lookup with Named Range*: =VLOOKUP(A2, MyRange, 2, FALSE)

- *Purpose*: Use a named range for better readability.

- *Shortcut*: Ctrl + F3 to open the Name Manager.

14. *Lookup with Helper Column*: =VLOOKUP(A2, B2:E10, 4, FALSE)

- *Purpose*: Use a helper column to concatenate multiple criteria.

15. *Date Lookup*: =VLOOKUP(DATE(2024,8,16), B2:D10, 2, FALSE)

- *Purpose*: Perform a lookup based on dates.

- *Shortcut*: Ctrl + ; to insert the current date.

16. *Lookup with Conditional Formatting*: =VLOOKUP(A2, B2:D10, 2, FALSE)

- *Purpose*: Highlight cells based on lookup results.

- *Shortcut*: Alt + H + L to open the Conditional Formatting menu.

17. *Lookup with SUMIF*: =SUMIF(B2:B10, A2, C2:C10)

- *Purpose*: Sum values based on a lookup criterion.

- *Shortcut*: Alt + = to insert the SUM function.

18. *Lookup with INDEX and MATCH*: =INDEX(D2:D10, MATCH(A2, B2:B10, 0))

- *Purpose*: Use INDEX and MATCH for more flexibility.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

19. *Lookup with OFFSET*: =OFFSET(B2, MATCH(A2, B2:B10, 0)-1, 1)

- *Purpose*: Use OFFSET to find a value based on a lookup.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

20. *Lookup with Array Formula*: =VLOOKUP(A2, {B2:D10}, 2, FALSE)

- *Purpose*: Use an array formula for complex lookups.

- *Shortcut*: Ctrl + Shift + Enter to enter an array formula.

21. *Lookup with Data Validation*: =VLOOKUP(A2, B2:D10, 2, FALSE)

- *Purpose*: Ensure data integrity with data validation.

- *Shortcut*: Alt + D + L to open the Data Validation menu.

22. *Lookup with INDIRECT*: =VLOOKUP(A2, INDIRECT("Sheet2!B2:D10"), 2, FALSE)

- *Purpose*: Use INDIRECT to reference different sheets dynamically.

- *Shortcut*: Ctrl + Page Down to navigate to the next sheet.

23. *Lookup with CHOOSE*: =VLOOKUP(A2, CHOOSE({1,2}, B2:B10, D2:D10), 2, FALSE)

- *Purpose*: Use CHOOSE to create a dynamic lookup table.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

24. *Lookup with SUMPRODUCT*: =SUMPRODUCT((B2:B10=A2)*(C2:C10))

- *Purpose*: Sum values based on multiple criteria.

- *Shortcut*: Alt + = to insert the SUM function.

25. *Lookup with HLOOKUP*: =HLOOKUP(A2, B1:D10, 2, FALSE)

- *Purpose*: Perform a horizontal lookup.

- *Shortcut*: Ctrl + Shift + → to select the entire row.

26. *Lookup with TRANSPOSE*: =VLOOKUP(A2, TRANSPOSE(B2:D10), 2, FALSE)

- *Purpose*: Use TRANSPOSE to switch rows and columns for lookup.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.

27. *Lookup with UNIQUE*: =UNIQUE(VLOOKUP(A2, B2:D10, 2, FALSE))

- *Purpose*: Find unique values based on a lookup.

28. *Lookup with FILTER*: =FILTER(D2:D10, B2:B10=A2)

- *Purpose*: Filter values based on a lookup criterion.

- *Shortcut*: Ctrl + Shift + L to apply filters.

29. *Lookup with SORT*: =SORT(VLOOKUP(A2, B2:D10, 2, FALSE))

- *Purpose*: Sort lookup results.

- *Shortcut*: Alt + D + S to open the Sort menu.

30. *Lookup with XLOOKUP*: =XLOOKUP(A2, B2:B10, D2:D10)

- *Purpose*: Use the more advanced XLOOKUP function for flexible lookups.

- *Shortcut*: Ctrl + Shift + ↓ to select the entire column.