Excel for Finance Functions Template

This Excel for finance functions template demonstrates sample calculations using the top 10 most important functions and formulas for finance professionals.

#1 XNPV

Determines the Net Present Value (NPV) of a series of cash flows at specific dates.

Formula: =XNPV(discount_rate, cash_flows, dates)

#2 XIRR

Determines the internal rate of return for a series of cash flows, given specific dates.

Formula: =XIRR(cash flows, dates)

#3 MIRR

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)

#4 PMT

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)

#5 IPMT

Calculates the interest portion of a fixed debt payment.

Formula: = IPMT(rate, current period #, total # of periods, present value)

#6 EFFECT

Returns the effective annual interest rate for non-annual compounding.

Formula: =EFFECT(interest rate, # of periods per year)

#7 DB

Calculates the depreciation expense in each period.

Formula: =DB(cost, salvage value, life/# of periods, current period)

#8 RATE

Calculates the yield to maturity for a security.

Formula: =RATE(# of periods, coupon payment per period, price of bond, face value of bond, type)

#9 FV

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)

#10 SLOPE

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