Excel Add-in Financial Functions:

Place the file in C:\Program Files (x86)\Microsoft Office\Office14\Library for 64 bit

or C:\Program Files\Microsoft Office\Office12\Library for 32 bit

Add it to the add-in’s tool pak

1. FAME_CAPM(RiskFreeRate, MarketReturn, Beta)a. Calculates the expected return of a security or portfolio using the CAPM (Capital Asset

Pricing Model).

2. FAME_Convexity(Settlement As Date, Maturity As Date, FaceValue, Coupon, Yld, Frequency)a. Calculates the convexity of a bond on a coupon date. It is important to note that this only

works on a coupon date, not between coupon dates. This function was created because, while Excel has functions for duration and modified duration, it does not have one for convexity.

3. FAME_Corr(Values1, Values2, Probabilities)a. Calculates the correlation coefficient between two variables using the probabilities of

occurrence for each value. Excel's Correl() function does not handle the probabilities of occurrence.

4. FAME_Covar(Values1, Values2, Probabilities)a. Calculates the covariance between two variables using the probabilities of occurrence for

each value. Excel's Covar() function does not handle the probabilities of occurrence.

5. FAME_CV(Values, Probabilities)a. Calculates the coefficient of variation using the probabillties of occurrence. Excel does

not have a similar function. This is a measure of risk per unit of return, and is calculated by dividing the standard deviation by the expected value.

6. FAME_EAR(Periodic_Rate, NPERYear)a. Calculates the Effective Annual Rate of a periodic rate. For example, if you know that a

quarterly rate is 2%, this function will tell you that is 8.24% annualized.

7. FAME_ExpValue(Values, Probabilities)a. Calculates the Expected Value of a random variable using the probabilities of each

potential value.

8. FAME_Geomean(DollarValues)a. Calculates the Geometric Mean Rate of Growth from a range of dollar values. Note that

Excel's Geomean() function does not (necessarily) calculate the geometric mean rate of growth (unless you feed is a range of growth rates). Instead it will calculate the geometric mean in the same units as the original data.

9. FAME_GGMCostEquity(Div1, Value, GrowthRate)a. Calculates the Cost of Equity, or Required Return, of a stock using the constant growth

dividend discount model (also known as the Gordon Growth Model).

10. FAME_GGMValue(Div1, ReqRet, GrowthRate)a. Calculates the intrinsic value of a stock using the constant growth dividend discount

model (also known as the Gordon Growth Model).

11. FAME_MACRS(Cost, MacrsClass, Year)a. Calculates the MACRS depreciation for an asset with a MACRS class of 3, 5, 7, 10, 15,

or 20 years. This function returns the depreciation expense for a selected Year. 'Will return #Value! error if MacrsClass or Year are incorrect.

12. FAME_MIRR(CashFlows, ReinvestRate)a. Calculates the Modified Internal Rate of Return for a series of cash flows. This is different

than Excel's built-in function in the way that it handles negative cash flows after period 0. This function calculates the total future value of all cash flows after period 0 (using the specified reinvestment rate). Excel's MIRR function first calculates the present value of all negative cash flows, and then calculates the future value of all positive cash flows. Normally, both functions will return the same answer. However, when there are negative cash flows after period 0, there will be a difference (sometimes significant). There is at least one additional way to calculate the MIRR that I am aware of.

13. FAME_Payback(CashFlows, Rate)a. Calculates the Payback Period of a series of cash flows, assuming that the first cash flow

(period 0) is negative. Note that the discount rate is an optional argument. If you include a discount rate the function will calculate the Discounted Payback Period. For the regular Payback Period, either don't supply a rate or make it equal to 0%.

14. FAME_PortRet(Values, Weights)a. Calculates the expected return for a portfolio of securities given the expected returns of

each security and their weight in the portfolio.

15. FAME_PortVar1(VarCovMat, Weights)a. Calculates the Variance of a portfolio of securities given a variance/covariance matrix and

the weights of the individual securities. Note that you must supply a complete variance/covariance matrix, not just the upper or lower diagonal.

16. FAME_PortVar2(CorrMat, StdDevs, Weights)a. Calculates the Variance of a portfolio of securities given a correlation matrix, and the

standard deviations and weights of the individual securities. Note that you must supply a complete variance/covariance matrix, not just the upper or lower diagonal.

17. FAME_PVGA(Pmt, Nper, GrowthRate, DiscRate, BegEnd)a. Calculates the Present Value of a Graduated (growing) Annuity. Note that BegEnd is an

optional argument that tells the function whether the first cash flow is at the end of the period (0) or the beginning (1) of the period. End of period (0) is the default if no BegEnd argument is supplied.

18. FAME_StdDev(Values, Probabilities)a. Calculates the Standard Deviation of a random variable given the probabilities of each

potential outcome. Note that this function is different from Excel's built-in StDev() function in that it allows the probabilities to differ. Excel's function assumes that all probabilities are equal.

19. FAME_ThreeStageValue(Div1, ReqRate, GrowthRate1, GrowthRate2, G1Periods, TransPeriods)a. Calculates the intrinsic value of a stock using the Three-Stage Dividend Discount Model.

Note that G1Periods is the length of the first period of growth, and TransPeriods is the length of the transition period between fast growth and constant growth.

20. FAME_TwoStageValue(Div1, ReqRate, GrowthRate1, GrowthRate2, G1Periods)a. Calculates the intrinsic value of a stock using the Two-Stage Dividend Discount Model.

Note that G1Periods is the length of the first period of growth.

21. FAME_Var(Values, Probabilities)a. Calculates the Variance of a random variable given the probabilities of each potential

outcome. Note that this function is different from Excel's built-in Var() and VarP() functions in that it allows the probabilities to differ. Excel's functions assume that all probabilities are equal.