Thread by @SuryansMisra

10 IMPORTANT EXCEL FUNCTIONS that you should master to perform financial analysis and modeling like a pro. 💯🧵

Save this for future use.

#excelforfinance #financialanalysis #financialmodeling

[1] XNPV:

Calculates the net present value (NPV) for a series of cash flows that may or may not be periodic.

Formula: =XNPV(rate, values, dates)

where rate is the discount rate, values are the cash flows, and dates are the corresponding dates of the cash flows.

[2] IRR:

Calculates the internal rate of return for a series of regular, periodic cash flows.

Formula: =IRR(values, [guess])

where values are the cash flows, and guess is an optional guess for the IRR.

[3] MIRR:

Modification of the IRR calculation and is a more accurate reflection of the true rate of return for a series of cash flows.

Formula: =MIRR(values, finance_rate, reinvest_rate)

where values are the cash flows, finance_rate is the cost of capital, and reinvest_rate is the rate of return for reinvested cash flows.

[4] XIRR:

Calculates the internal rate of return for a series of irregular cash flows. The rate returned by XIRR is the interest rate when XNPV = 0.

Formula: =XIRR(values, dates, [guess])

where values are the cash flows, dates are the corresponding dates of the cash flows, and guess is an optional guess for the IRR.

[5] PMT:

Calculates the payment for a loan based on constant payments and a constant interest rate.

Formula: =PMT(rate, nper, pv, [fv], [type])

where rate is the interest rate, nper is the number of periods, pv is the present value, fv is the future value (optional), and type is the type of payment (optional).

[6] PPMT:

Calculates the payment on the principal for an investment based on periodic, constant payments and a constant interest rate.

Formula: =PPMT(rate, per, nper, pv, [fv], [type])

where rate is the interest rate, per is the period for which the payment is calculated, nper is the total number of periods, pv is the present value, fv is the future value (optional), and type is the type of payment (optional).

[7] SLN:

Calculates the straight-line depreciation of an asset for one period.

Formula: =SLN(cost, salvage, life)

where cost is the initial cost of the asset, salvage is the salvage value of the asset, and life is the useful life of the asset.

[8] RATE:

Calculates the interest rate per period of an annuity.

Formula: =RATE(nper, pmt, pv, [fv], [type], [guess])

where nper is the number of periods, pmt is the payment per period, pv is the present value, fv is the future value (optional), type is the type of payment (optional), and guess is an optional guess for the interest rate.

[9] EFFECT:

Calculates the effective annual interest rate.

Formula: =EFFECT(nominal_rate, npery)

where nominal_rate is the nominal annual interest rate, and npery is the number of compounding periods per year.

[10] FVSCHEDULE:

Calculates the future value of an initial principal after applying a series of compound interest rates.

Formula: =FVSCHEDULE(principal, schedule)

where principal is the initial investment amount, and schedule is an array of interest rates or growth rates.

TL;DR

[1] XNPV [2] IRR
[3] MIRR [4] XIRR
[5] PMT [6] PPMT
[7] SLN [8] RATE
[9] EFFECT [10] FVSCHEDULE

I hope you found this thread useful and learned something new.

If you did, please show some love by:
- Retweeting the first tweet of this thread so more people can benefit from it, and
- Following me @SuryansMisra

Thank you for reading and stay tuned for more! 🙌