This Excel for finance functions template demonstrates sample calculations using the top 10 most important functions and formulas for finance professionals.
Determines the Net Present Value (NPV) of a series of cash flows at specific dates.
Formula: =XNPV(discount_rate, cash_flows, dates)
Determines the internal rate of return for a series of cash flows, given specific dates.
Formula: =XIRR(cash flows, dates)
Determines the internal rate of return when the cash from one investment is invested in a different one.
Formula: =MIRR(cash flows, cost of borrowing, reinvestment rate)
Calculates the number of payments given an interest rate, a number of time periods, and the total value of the loan.
Formula: =PMT(rate, number of periods, present value)
Calculates the interest portion of a fixed debt payment.
Formula: = IPMT(rate, current period #, total # of periods, present value)
Returns the effective annual interest rate for non-annual compounding.
Formula: =EFFECT(interest rate, # of periods per year)
Calculates the depreciation expense in each period.
Formula: =DB(cost, salvage value, life/# of periods, current period)
Calculates the yield to maturity for a security.
Formula: =RATE(# of periods, coupon payment per period, price of bond, face value of bond, type)
Determines how much money you will have in the future, given a starting balance, regular payments, and a compounding interest rate.
Formula: =FV(rate, # of periods, payments, starting value, type)
Calculates Beta of a stock, given the weekly returns for the stock and the index you wish to compare it to.
Formula: =SLOPE(dependent variable, independent variable)
Credits to : Corporate Finance Institute