ࡱ > # "
! i % & ' h ) ? + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ( @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g j | k l m o p q r s u v w x y z { n ~ R o o t E n t r y F pcr $ W o r k b o o k > _ V B A _ P R O J E C T _ C U R " W+Br Er V B A ` Er }Er \ p Timothy R. Mayes, Ph.D. B a = ThisWorkbook = i xKU28 X@ " 1 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 h 8 C a m b r i a 1 , 8 C a l i b r i 1 8 C a l i b r i 1 8 C a l i b r i 1 C a l i b r i 1 C a l i b r i 1 < C a l i b r i 1 > C a l i b r i 1 ? C a l i b r i 1 4 C a l i b r i 1 4 C a l i b r i 1 C a l i b r i 1
C a l i b r i 1 C a l i b r i 1 C a l i b r i "$"#,##0_);\("$"#,##0\) ! "$"#,##0_);[Red]\("$"#,##0\) " "$"#,##0.00_);\("$"#,##0.00\) ' " "$"#,##0.00_);[Red]\("$"#,##0.00\) 7 * 2 _("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_) . ) ) _(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_) ? , : _("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_) 6 + 1 _(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)
0"-Year"
ff + ) , * P
P ` a >
h @ @ & x @ @ " x @ @ " | @ @ ( @ @ , @ @ Q | | G ǥ}- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }- }
00\);_(* }( } 5
00\);_(*}- } ?
00\);_(* }A } @
00\);_(* ;_(@_) }A } A
00\);_(* ;_(@_) }A } B
00\);_(* ;_(@_) }A } C
00\);_(* ;_(@_) }- } D
00\);_(* }- } E
00\);_(* }( } F
00\);_(*}- } +
00\);_(* }- } ,
00\);_(* }- } -
00\);_(* }- } .
00\);_(* }- } ;
00\);_(* }- } <
00\);_(* }A } 1
00\);_(* ;_(@_) }A } 2
00\);_(* ? ;_(@_) }A } 3
00\);_(* 23 ;_(@_) }- } 4
00\);_(* }A } 0
a 00\);_(* ;_(@_) }A } (
00\);_(* ;_(@_) }A } 8
e 00\);_(* ;_(@_) } } 6
??v00\);_(* ̙;_(@_)
} } :
???00\);_(* ;_(@_) ??? ??? ???
??? } } )
} 00\);_(* ;_(@_)
}A } 7
} 00\);_(* ;_(@_) } } *
00\);_(* ;_(@_) ??? ??? ???
??? }- } >
00\);_(* } } 9
00\);_(* ;_(@_)
}- } /
00\);_(* }U } =
00\);_(* ;_(@_) }A } "
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A }
00\);_(* 23 ;_(@_) }A } #
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A }
00\);_(* 23 ;_(@_) }A } $
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A }
00\);_(* 23 ;_(@_) }A } %
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A }
00\);_(* 23 ;_(@_) }A } &
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A }
00\);_(* 23 ;_(@_) }A } '
00\);_(* ;_(@_) }A }
00\);_(* ef ;_(@_) }A }
00\);_(* L ;_(@_) }A } !
00\);_(* 23 ;_(@_)
20% - Accent1M
2 0 % - A c c e n t 1 ef %
20% - Accent2M "
2 0 % - A c c e n t 2 ef %
20% - Accent3M &
2 0 % - A c c e n t 3 ef %
20% - Accent4M *
2 0 % - A c c e n t 4 ef %
20% - Accent5M .
2 0 % - A c c e n t 5 ef %
20% - Accent6M 2
2 0 % - A c c e n t 6 ef %
40% - Accent1M
4 0 % - A c c e n t 1 L %
40% - Accent2M #
4 0 % - A c c e n t 2 L湸 %
40% - Accent3M '
4 0 % - A c c e n t 3 L %
40% - Accent4M +
4 0 % - A c c e n t 4 L %
40% - Accent5M /
4 0 % - A c c e n t 5 L %
40% - Accent6M 3
4 0 % - A c c e n t 6 Lմ %
60% - Accent1M
6 0 % - A c c e n t 1 23 %
60% - Accent2M $
6 0 % - A c c e n t 2 23ٗ %
60% - Accent3M (
6 0 % - A c c e n t 3 23֚ %
60% - Accent4M ,
6 0 % - A c c e n t 4 23 %
60% - Accent5M 0
6 0 % - A c c e n t 5 23 % !
60% - Accent6M 4
6 0 % - A c c e n t 6 23 % " Accent1A A c c e n t 1 O % # Accent2A ! A c c e n t 2 PM % $ Accent3A % A c c e n t 3 Y % % Accent4A ) A c c e n t 4 d % & Accent5A - A c c e n t 5 K % ' Accent6A 1 A c c e n t 6 F % ( Bad9 B a d % ) Calculation C a l c u l a t i o n } % *
Check Cell
C h e c k C e l l % ??? ??? ??? ??? + C o m m a ,( C o m m a [ 0 ] -& C u r r e n c y .. C u r r e n c y [ 0 ] / Explanatory TextG 5 E x p l a n a t o r y T e x t % 0 Good; G o o d a % 1 Heading 1G H e a d i n g 1 I}% O 2 Heading 2G H e a d i n g 2 I}% ? 3 Heading 3G H e a d i n g 3 I}% 23 4 Heading 49 H e a d i n g 4 I}% 54 H y p e r l i n k
6 Inputu I n p u t ̙ ??v% 7 Linked CellK L i n k e d C e l l } % 8 NeutralA N e u t r a l e % 3 N o r m a l % 9 Noteb
N o t e : Outputw O u t p u t ???% ??? ??? ??? ??? ;$ P e r c e n t
< Title1 T i t l e I}%
= TotalM T o t a l % O O > Warning Text? W a r n i n g T e x t % X T a b l e S t y l e M e d i u m 9 P i v o t S t y l e L i g h t 1 6 ` 1 MACRS Table % MACRS_Table; / Year MACRs Class Total Source:
J1 c c B 4c
=_MA
3 _=
d MbP?_ * + % , & ffffff?' ffffff?( ?) ? " , 4c333333?333333?To& <3 U , ; , , , , , , , ,
, , ,
, , , , , , , , , ; , , @
A A A A A A
B * C @C @C @C $@C .@C 4@ ~
? ? ioT?~
? 4@ ? _LJ? ? $@? @? @ ~
@ ? r?~
? @@ ? +eX? ? 2@? #@ ? B&9 {?~
@ ? D? ? ~jt? ? c? ? ;On? ? S? ? Z?~
@ ? 37? ? H}? ? 8EGr? ? H}? ? ʡE? ? qrCQ?~
@ ? ? H}? ? ec]ܶ? ? |Pk? ? v? ? =$@?~
@ ? ? H}? ? ͪն? ? Y ? ? ? ? W/'?~
@
? ? ? ec]ܶ? ? S㥛İ? ? h|?5? ? aۢ?~
@
? ? ? ͪզ? ? S㥛İ? ? h|?5? ? @j'?~
"@
? ? ?
? ^)˰?
? cZB?
? ~nئ?~
$@ ? ? ? ? S㥛İ? ? h|?5? ? ,&6 צ?~
&@ ? ? ? ? ^)ˠ? ? cZB? ? ~nئ?~
(@
? i|?5?
? ,&6 צ?~
*@ ? cZB? ? ~nئ?~
,@ ? i|?5? ? ,&6 צ?~
.@ ? cZB? ? ~nئ?~
0@ ? i|?5? ? ,&6 צ?~
1@ ? ~nئ?~
2@ ? ,&6 צ?~
3@ ? ~nئ?~
4@ ? ,&6 צ?~
5@ ? ~nؖ?
D # E ?
% M # E ?
% MM # E ?
% MM # E ?
% MM # E ?
% MM # E ?
% MM
F m z / http://www.irs.gov/publications/p946/index.htmlA IRS Publication 946 (Cat. No. 13081F): How to Depreciate PropertyBgD A IRS Publication 946 (Cat. No. 13081F): How to Depreciate Property 6 8
, < \ h z z r r d d T T T 2 2 2 2 2 > @ li ji Sheet1g g D
T h i s W o r k b o o k S h e e t 1
F A M E _ F u n c t i o n s * _ _ S R P _ 2 >
! " # $ & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C E F G H I J K L M N O Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k m o p q r s t u v x y z { } ' D} # x ME ( SL S S < N 0 { 0 0 0 2 0 8 1 9 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } @ ( % H @ 0 OJ
x Attribut e VB_Nam e = "Thi sWorkbook"
Bas0{00020P819- 0C $0046}
|GlobalSpacFalse dCreatabl Predecla Id Tru
BExpose Templ ateDerivBustomizD2 ' DPP # x ME ( SL S S < N 0 { 0 0 0 2 0 8 2 0 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } @ ( % H @ 0 OJ
x Attribut e VB_Nam e = "She@et1"
Bast0{00020820- C $0046}
|Global!SpacFalse dCreatabl Pre decla Id Tru
BExpose Temp lateDeriv$Bustom izD2 ( H P p x
6 * N ( 0
8 H P X ` B h B p 6 B 6 $ X B : * 0 B 8 0 @ p B > > B > h 0 D Z 0 B @ 4
(
0
: @
D
0 0 ` h 0 x @ ( ( o] Dn x ME ( 0* 6
< < & ` @ - - I ` I I i" Calculates depreciation expense for Year using MACRS with the half-year convention. MacrsClass is 3, 5, 7, 10, 15 or 20.Table is optional, 0 for exact or 1 for table lookup. Default is 0.
14`$ `& ( % < ` ` I> @ I@ 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.
14 B@ ` 8 P ID p 6 IF > IH F Calculates the intrinsic value of a stock using the constant growth dividend discount model (also known as the Gordon Growth Model).
14 J ` ID IL IH 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).
14 N ` ( IP IR IT Calculates the required return for a security or portfolio using the CAPM (Capital Asset Pricing Model) using the risk-free rate, the expected market return, and the security's beta.
14 VP ` X 0
)XP $ % Calculates the geometric mean rate of growth for a series of dollar values. The first cash flow in the list must be the most recent cash flow, and the last must be the oldest cash flow.
14`Z `\ `8
` )b % Id Calculates the payback period of a series of cash flows. The first cash flow must be the (negative) initial outlay. The discount rate is an optional argument. If it is provided, this function will return the discounted payback period.
14`f `h @j `l n `
0 x
)p
% )r
% _ Calculates the variance of a random variable given the probabilities of each potential outcome.
14`t `v `l |X
` 8 )~ % ) % )r0 % 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.
14` @ ` `l `
)~
% )
% )r % 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.
14`
` 8
)pX ` % )r % i Calculates the standard deviation of a random variable given the probabilities of each potential outcome.
14` ` X )px % )r % 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.
14` z ` P )p ( % )rH % c Calculates the Expected Value of a random variable using the probabilities of each potential value.
14` `
` 0
)pP X % )x % Calculates the expected return for a portfolio of securities given the expected returns of each security and their weights in the portfolio.
14 ` X )x % ) % 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.
14` @ ` P ` ( )H P % )p x % ) % 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 correlation matrix, not just the upper or lower diagonal.
14 ` @ ` ( ` 0
ID I I I I 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.
14@ ` ( ` H ID h I I I I I 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.
14` ` h h Ib I Calculates the Modified Internal Rate of Return for a series of cash flows. This is different from Excel's built-in function in the way that it handles negative cash flows after period 0.
14` @ ` ` I I 8 IH X I x I Calculates the present value of a graduated annuity. Note that BegEnd is an optional argument that describes whether the cash flows occur at the end (0) or beginning (1) of the period. End of period cash flows are assumed if the BegEnd argument is omitted
14 ! ` ! I I 0 IH P I p I Calculates the FV of a graduated annuity. PMT is the first payment amount, NPer is the number of periods, GrowthRate is the payment growth rate, DiscRate is the required return, and BegEnd if the payments are at the end (0) or beginning (1) of the period.
14 # ` " # ! i " i (" I H" I h" I " I ` Calculates the convexity of a bond. Note that this function only works on a coupon payment date.
14@ @ @ ` @ ` `$ ` @ @ @ @ ` ` $ % $ I $ I Returns a normally distributed random variable with a given mean and standard deviation. Note that this function is volatile, and will return a new result each time the worksheet is recalculated.
148
P ! X
P # `$ ( @ p& % `$ H& % % OJ
* \ R 1 * # 2 2 2 $ * \ R f f f f * 0 k 4 c 1 4 d f 4 5 a H H P B
X h h J L J p D $ H " h ( ( B 0 8 8 p x <
. (
X
h
p
x
B
:
,
0 8 @ X p <
B < 8 , @ p l ( 0 8 8 p x B B 8 @ P ` h p x B H " H P X B ` > h
8 H P X B ` B h B J ( 0 @ ` x 8
F 0 `
B R ( 2 8 p " 4 0 (
X
h x B D 8 6 P B ` B $ B R ( 0 N B ` * x \ D ( * p 0
V 4 P B . x . H B x v @ H B P L ` 2 B ( * P p & L @ H ` ^ p > !
! . 0! `! B h! 2 p! ! ! ! ! ! ! " 0" " P" x" " " B " X " \ #
`# p# # # B All macros here are Copyright 1994-2009 by Timothy R. Mayes, Ph.D.0 ( b Calculates the MACRS depreciation for an asset with a MACRS class of 3, 5, 7, 10, 15, or 20 years. C This function returns the depreciation expense for a selected Year. E The function either uses the IRS table value or an exact calculation, D depending on the value of Table. Default is 0 (use exact result). > Will return #Value! error if MacrsClass or Year are incorrect. ] ] ] A3:G23 % , % * . ( " Do a table lookup .! 0 K T ( 7 2 ' & K T ( 7 2 ' & K T ( 7 2 ' &
K T ( 7 2 ' & K T ( 7 2 ' & K T ( 7 2 ' &t S ` 4Y(' &4 0 n H q @ d 8 4Y(' &iaTypek d - Calculate the exact result using VDB function es
G ' $c G ?' $j .! 0 LHUB ? 7 8 ? 7 : $7 6 ' & q d 4Y(' & k k &' i < Calculates the effective annual rate given a per period rate / See also Excel functions Effect() and Nominal() > @ ' <Hi / Calculates the value of a constant growth stock using the Gordon Growth Model D F H ' B i ` @ 3 Calculates the cost of equity for a constant growth # stock using the Gordon Growth Model ( D L H ' J. i ) Calculates the required return using CAPM P T R P ' NF i h 7 This function is intended only to be used in Chapter 1. 8 It is an example that calculates the geometric mean rate ; of growth in sales. Any other use is strongly discouraged. 7 Note: The first value is assumed to be the most recent. ] ] 8 X! ^' Z < Get the count of sales figures $ X Z$ X ' \ < # Calculate the total growth in sales sCla \ Z ' V < / Take the Nth root and subtract 1 to get geomean is 0. i h P 9 Calculate the payback period or discounted payback period . Note that the first cash flow must be negative ] ] ]
b! ^' j $ ( Upper bound (i.e., number of cash flows) Effec ' h $ 6 Cumulative sum of cash flows, stop when greater than 0 qua ' l $ Counter variableon d G d d ' dj nn h l j b l ' l h l$ b d l ' h h 6 h l$ b d l ' h l h l$ b d l ' f f' `d Payback > Life' ` ( Report errork i 8
2 Calculates the variance with unequal probabilities] 8 ] P ] h r! ^ p! ^ 0 ! If counts not equal, report error Counts not equal' t x k ' t p r$ z ' v or Re l p! ^ oc t l$ p v l$ r ' tmo l known x t' ni 4 Calculates the covariance with unequal probabilities ] ]
(
] @
r! ^ ~! ^ r! ^ ! ^ Counts not equal' x k ~ r$ z ' for a r$ z ' olio u l ~! ^ A l$ ~ l$ l$ r ' arke l and th x ' |i X
5 Calculates the correlation with unequal probabilities ] r! ^ ~! ^ r! ^ ! ^ * Counts not equal' xp k h e Check to see if the ranges are identical. If so, avoid error by returning 1. This avoids returning 0. ~! ! ' x k ~ r$ | ~ r$ r$ ' x ' i x < Calculates the standard deviation with unequal probabilities ] r! ^ p! ^ Counts not equal' x k p r$ n ' x $ ~a $ ' d ' k i B Calculates the coefficient of variation with unequal probabilities] r! ^ p! ^ Counts not equal' x
k
' p r$ p r$ z ' x
' i
8 Calculates the expected value with unequal probabilities ] ] r! ^ p! ^ Counts not equal' x
k
' p! ^ $ p $ r ' x ' zi < Calculates an expected portfolio return with unequal weights p$ z ' i 0 D Calculates the portfolio variance using a variance/covariance matrix ] ] ! ^ ! ^ Counts not equal' x k x ' ! ^ ! ^ $ $ $ ' ! ^ " ) subtract out what we double counted above $ $ $ ' xX
' i H
K Calculates the portfolio variance using the correlation matrix and std devs ] ] ] ! ^ ! ^ ! ^ ! ^ Counts not equal' xp k h ! ^ ! ^ H ! ^ ! ^ $ $ $ + $ $ $ ' x ' i p P > Calculates the value of a stock with two dividend growth rates 2 Note that GrowthRate1 must not be equal to ReqRate / Note that GrowthRate2 must be less than ReqRate ] D ' D ' ' i K Calculate the intrinsic value using the three-stage dividend discount model ] D ' $ Calculate dividend 0 ' i ( Y This function calculates the MIRR by taking all future cash flows forward to the terminal $ period and then solving for the IRR. U Note: Cashflows must include the initial outlay, and initial outlay must be negative. ] p ] ' b! ^' 2 Don't include initial outlay in count of cashflows # Get total Future Value of cashflows $ b ' O Calculate geometric mean rate of return between initial outlay and future value $ b ' i p ( Calculates the PV of a graduated annuity r Pmt is the first annuity payment, NPer is the number of payments, BegEnd is 0 for END of Period or 1 for BEGINNING Cash Flows at END of period H H ' d 8 H H H H ' k i E Calculates the future value of a graduated annuity by using FAME_PVGA H $ ' i ! " Calculates the convexity of a bond $ Only works on a coupon payment date! ] # 0# H# `# ] x# # m $ ' m $ ' ' ' ' ' - ' ` ' < Weighted sum of PV of cashflows d ( ' -yk crsCla ' 0i # , Calculates a random standard normal variable ] # $ $ 0$ H$ _ 0 A@4 $ ' A@4 $ ' ' >
$ $ ' ates t ' nn ' pei ` `$ R Generates a normally distributed random number given a mean and standard deviation U This function will generate new random numbers whenever the worksheet is recalculated .B@ p ' i p h Attribut e VB_Nam e = "FAM E_Functi ons"
'A ll macro s here a Copyrig ht 1994- 2009 by Timothy R. Mayes , Ph.D.
Op Exp licit Ba@se 1
p ~MACRS(Cost, GcrsClas LYear, Kal T able As Boolean False)
P. Desc ri Calc ulates depreciaS expenLfor : using K with the half-yI con8ven |X i s 3, 5, 7, 105 (20.\ oE@al, 0 6ePxact1tplookup. Default+0.rPro cDataxIn0voke \n14|2C6@#T ^et = 1) AndR <=I +TXhen'WeApA~j.As}S"? Ca~wk
3ab(= a * .VLbg,C), 2,0i 5 o 7 4y10 1?$;06<2?
:E?$VErrx(xl }$h9EacVa_/
Ts7 Ifc'ƶiDVDBox"]r<_g>(8d3) Or ;5? 7? pO 1= 20)~)I` 0H1n0SLT 1.\*?I`S?I6 p.VdbD|, .Max(0R- @)AinR- 0.4
% #;#)# *E$p a
X CEAR(Pe riodic_R0, NPER1/` "LEff}iv0nnuG Qqa p br. Fq-m ple, `uyo@u knowP/atjquarterly2%,wipzte@ th8.24% aizezd_ __ e gn@ 0'trSeдls"opcelv;s ()
d Nominal("b 0
@ (1 + :) ^V (/ # GGM#(Di v1, ReqRe Growth % in`nsic Ft8ockJᛀst ant g2 d0ivid scDoumod(-po" s e 0GordP
Ma) _ / / ,
s P / >l;27 / (*[9`эEquityR , Z :j c
uir@"0fP-R di ܞ Go o l cos`f eRaQ(
W :]9- CAPM(RiskFreeq8!rketBeta9a r1a r_ eturn fo r a secu rity hpo rtfolio using th e CAPM ( Capital Asset Pric>Model)Xrisk-f ree rate,expected mark xr, and 's beta ."
Attr ibute FAME_.VB_ProcDa " Invoke_F unc = " \n14 9'CaPlcul |s\requir |uqP
/ "R ^F]R + B F * (M[R- )
EbBtion
\Geo mean(Dol larValues Range tuDescrip+o kg+c / of growth,ri6 d> v?. Tfir@st cas@ l ow inKliI@mu bemorecenH nla old>ePs9!tThpis fyBi ntendtonly to@u0Chapter 1.
'It an exampl4aA1
PRR sales`. An ɀrstrong !discouraged Not e:Y^"ssu>m@*-AW@Dim DVCou@[As I@:ger
SA!GxrSleA
=: I.C 'G? r` figurescd)A (1) /J($) gqBIto?DT*pB] ^ (1`
- 1 )) !'TakTN^root subtract 1Cg@ Va riantCupmSumEmpCFbj, tY;DXCH
q'Up> b|d ( i.e., numb o?Ka\w No ' `@ Ig, sto@p whene r#n 0
FW'evbG"R0>= 6 *n mz=c / 10Do WhikWT <A su<^ =6+< 1s + ]f(AIPC+) D5CLoop 0a[pB-&=Дd- 2L/aG $" K'ElsbQ "E > Life" 'Req errog!QSIf͎3( ,obabilI_SF"L2(nceRr9om@0;) g 0C@LH05eac(h p@ynRcomeJ_doJ C wihun`XcE C FAvgFd"?F0+@
WF<> # 9'}s n r ,\M R,8е!1 GoTo Macro!4&@=_!"Xt@
=#Exp2
"<"-F^{U42Fo
#V+f G4- 3263NextuG
%:26`5[1CoP+1 , 2Z v22ca&Pwe `two=2s0qr2ocy re6132. Excel's q
)do >%~hp9 _87_
onoh 8Gt}/8g1,"rj8(5( 81) Or F;2a;'r _9l_9_9
01+A, _ExpValue(Ps1, P robabili ties)
Avg2 = PFAME
2bF or Counter p1 To .,Covariance () + (H) - 1) *z2 2
qNext
MacroEnd:'oA
E
F unction
CorrU As Range,w2
L Attribute(.VB_Descrip: H"Calcul ates thet c ;e Ie fficient between two blAusingp of occPurreA`fmeach . Excel's@L l() fDA do not handle]."
T9@EcDataAwith unequal 6
Dim.aQ5 gVtAqAIf (e <>˫P) Or\
2) The@A Ai= "²s AN,@7E
Go@ɅC If'Che ck to sePe if\rs( ar@d@ical. @5so, avoid err@uby return1. )i
s0.c
uAddresfs1#2.% 1A#Gp%ji
/ (StdDev9prh'bstnard devi[@yDa Bdom xgiva~}p otF`goutcome0ug@+u˔]v
rvJA5?*;*PortRet3q$ɀWeights_[))f rS a pfolio'sep(W|s*y pir w
i{O,_x ?,?,8,an 6 T
, CT (t?` Kq01(@ MatOO_]p rtfolio of secur ities gi ven a va riance/co( matri x and th e weights indiv idual. Note \at you must supply comple m, not j6d 9er or low diag onal."
$At bu MFA ME_PortV ar1.VB_ProcData Invoke_F unc = " \n14 'Calculat4p using
Dim P ; As Bt
i, j
I nteger
If ( CovMat.C ount <> W^ 2) Then 3 aseq fGoTo MacroEnd X IfD0CFUiD1
J ji D+ 2 * (D(i)@j%?(AI))Nex i* 'subtrac t out whwe double cWed `aboveC
,-^*.*i*&EZ:D)ɡ MAC
`Atio s
E 2(Co rr@ARang e, StdDe|vsHQF $2Descrip{A #"L^@B~za `cee%,&st`ard devia
sb?es?"?9"$?
? #!stb#sc#(`7߇Ň#LADOr (G Redo, o aq{ eg`^%"}ja =+s7i
j r2 !TwoSt ageValue (Div1, ReqR`, Grpowtha2 1Period>sPBC
CinwnsicЂ sC`stock00-Eb
ideDisRX Model ?G is2length4hfirp 1g{B_ = PvaQa 3
wiptwo r rQ
'#Ibe#4 touO"2C lesYanQ5V@ Vj2Q_S0lpl%V1# / ( L - v(10(P +01 +)) ^ $1@ = &3 ^ (' - 1y2p cDbp* P
+}p2
Y
L
uuThree(/3uH3)3, Trans4e{ /4/4/4&GdRO4O4sO4`O4B{
/8!8p?t8betwe90fa91!conqt 3_:_wBMHgt -0g!;dGnmGw 304R=QK15'") xAoY&909t~eн0 5@0/ *++(ҵ
?6- m_MIRR(CashFsdinvestd`9 b--]ifikAr` !UReturn f/U\P`c f .+ differe0fro m Excel' s built->i0( *wayc0it hp(T negPv aft 4,0)N_5ccTh
fMc bP akll fu;wab6er|mi b$
!5n solvq.td: d@include inal 0 lay, u and init ial outl ay must be negat ive.
Di m Future Value As SinglehN, i&Integer
^= 0(N = CashFlo ws.Count 'Don't clude
in c&of c 9f9L'Get tot l )For = 2 To N="!+S(i) * (1 + Rei nvestRat e) ^ (N - i)(Nexb'Calcul geome tric mean r Pre ntweenzef v*F AME_MIRRE(V/ -(1)M1 /O@- 1
EF unction
BPVGA (Pmt, Np er, Growth9, Dis2cCOp[BeNgAu ?At7bute.VB_DescriB +"FDs the `prese@7 Da graduGd annui@ty. No@that D is o!argum dB bes hwhe rBA occur @A eC(0) beginn@ y fC(eriod. :'E Dare ass? D)%+om itted"
IProcDataLInvoke_fL \n14'@OPVTL
' q A8firÀS payJ@x PeCnumb's,H-0 f#END
1BEGINNINGSIf1B ThZsh b}.)pZO V%NkQp- (bq @s`X$sEls a
d{
+
(! s) RIfuF ue,^ u A uFuM.0 PMTKMDL amA6Mk MG+ g珅req@uired c, dVdwY btĿ }}u$ u
uiTv by usaccabF{CdQ+aNKC
omxW(SetxtleU~IP%M a0k~Fac #, |pon, Yld, F1ency-lO&'=bcbonX)'athpNfm o nly workrs aQUO de. Xll/
d0XO!PS, P#+, temp>iS3z= Diff("m", , UDFc\s@%`(oC hp129 qT @Q / + * $#RZ_b^1- _/'@S4š1CQ`mi < trmS`(
tck ãi2@ 'Weigh -|kk7+#)iAwG Qf92F0S >2 c
=E
v 7`rmDev(`(YRa@^ndom st@ard normalpriab%V1, V2H, REaca2sGsIouӒCDoR@2zV1
-Rnd()x21 ^+ Loop Until (R#1) AR > 080 Sqr(-Log(RA RG'= P"2
N V /qQal(M`BtdYsY"RB Dly disԿxA withXg @n devrionyK] ^v2o`il@Rwill a w sult each timer`he!rec:d/`_?`?`GeneqiaІĵa 0cTlrgts`23nevh(ApplicV$F> AME_Rand Normal = (HDev * Std @) + Mean
E nd Functio 4
rU ~|
u rU 8 ` Q ! 9 ///, ` _ _ S R P _ 3 % _ V B A _ P R O J E C T t ' d i r D _ _ S R P _ 0 P //0 ` a y ///0 ` a ///0 ! ` I q ///, I ` I =) 4 q `
=) 8 ` =) =) D ` =) =) =) D ` q =) =) =) 8
` =) =) 8 9 ` =) =) 8 a ` =) =) 8
` =) =) 8 ` =) =) D ` =) =) =) < ` y a 9 Y /////@ ) ` a 9 Y //////, Q ` 1"
" //< y ` $ # # # ////< ` ( # # # ////@ ` * * 1* Q* q* * * ,,////$ ` , 1 ` & + + // n Ͳ 0* pH d FameFncs >4unct ions for Financi al Analy sis with Microso ft Excel, 5 (Ed.@ | u n c t i o s f r F n a i
l A
a l Xy s -w t h M c r =s f S ET x ]e 1, !5 )E d . =x OPi FacD
GasDevGset$ RndR Logd ( FAME_RandNormal Mean@ StdDev$ Volatile Workbookk ` 6 $T ` R( c t L i b r a r y * \ G { 0 0 0 2 0 4 3 0 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } # 2 . 0 # 0 # C : \ W i n d o w s \ s y s t e m 3 2 \ s t d o l e 2 . t l b # O L E A u t o m a t J
< stdole> s+d9l eD
E ^* \G{00020430- C 0046}# 2.0#0#C: \Windows \system32\22.tlb #OLE AutXomaE EOPfficEOfkE E2 DF8D04C- 5BFA-101@B-BDE5"A*A!42"Pr ogram vle s\CommontShared \OFFICE1 2\MSO.DLLL#G& 18 Object Library% "B DBTThisWo rkbookG TUi@}WYr k bo* 2
HB1Bx- B,!}"B+BB|ShePet1GS@#eA@{t 1 H2SNPP F AME_FG AAF A M@_ FN O
2^
'u]!n!"!!b , 5 t h E d . OJ
K* rU ~ ~ ~ ~ ~ ~o DODN~v~
9 p 9 1 I q 9 q ) ) FameFncs ThisWorkbook Sheet1 FAME_Functions F / C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL VBA
Q y 0 F 4 C:\Program Files\Microsoft Office\Office12\EXCEL.EXE Excel
I q @ 0 F C:\Windows\system32\stdole2.tlb stdole
Y P L-[ DR ? C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL Office
a `
FAME_MACRS FAME_EAR
FAME_GGMValue FAME_GGMCostEquity FAME_CAPM FAME_Geomean F F FAME_Payback FAME_Var
FAME_Covar FAME_Corr FAME_StdDev FAME_CV
FAME_ExpValue FAME_PortRet
FAME_PortVar1
FAME_PortVar2 FAME_TwoStageValue FAME_ThreeStageValue FAME_MIRR FAME_PVGA FAME_FVGA FAME_Convexity NormDev FAME_RandNormal
U ounter ( FAME_VThisWorkbook T h i s W o r k b o o k Sheet1 S h e e t 1 FAME_Functions F A M E _ F u n c t i o n s al Analysis with MicrID="{235A77C4-96CC-4691-9FD6-2A36F010C9BA}"
Document=ThisWorkbook/&H00000000
Document=Sheet1/&H00000000
Module=FAME_Functionsa * \ G { 0 0 0 2 0 4 E F - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } # 4 . 0 # 9 # C : \ P R O G R A ~ 1 \ C O M M O N ~ 1 \ M I C R O S ~ 1 \ V B A \ V B A 6 \ V B E 6 . D L L # V i s u a l B a s i c F o r A p p l i c a t i o n s * \ G { 0 0 0 2 0 8 1 3 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } # 1 . 6 # 0 # C : \ P r o g r a m F i l e s \ M i c r o s o f t O f f i c e \ O f f i c e 1 2 \ E X C E L . E X E # M i c r o s o f t E x c e l 1 2 . 0 O b j e c t L i b r a r y * \ G { 0 0 0 2 0 4 3 0 - 0 0 0 0 - 0 0 0 0 - C 0 0 0 - 0 0 0 0 0 0 0 0 0 0 4 6 } # 2 . 0 # 0 # C : \ W i n d o w s \ s y s t e m 3 2 \ s t d o l e 2 . t l b # O L E A u t o m a t i o n (* \ G { 2 D F 8 D 0 4 C - 5 B F A - 1 0 1 B - B D E 5 - 0 0 A A 0 0 4 4 D E 5 2 } # 2 . 4 # 0 # C : \ P r o g r a m F i l e s \ C o m m o n F i l e s \ M i c r o s o f t S h a r e d \ O F F I C E 1 2 \ M S O . D L L # M i c r o s o f t O f f i c e 1 2 . 0 O b j e c t L i b r a r y
| F u n c t i o n s f o r F i n a n c i a l A n a l y s i s w i t h M i c r o s o f t E x c e l , 5 t h E d . OJ
D T h i s W o r k b o o k 0 > 4 c 1 4 d e c 2 T h i s W o r k b o o k } - S h e e t 1 0 ? 4 c 1 4 d e c 2 S h e e t 1 PP - F A M E _ F u n c t i o n s 0 k 4 c 1 4 d f 4 5 F A M E _ F u n c t i o n s n 0 u] P 0 psjjG({R [(`|(Hp]:/ h;fvJ(澩 0 7 Excel+ VBA Win16~ Win32 Mac VBA6# FameFncs stdole` Officeu ThisWorkbook| _Evaluate Sheet1 FAME_Functions
( FAME_MACRSӥ Cost4
MacrsClassW Year6n Table Factor DeprO
MacrsTableG Range Sheets
Application* WorksheetFunction& VLookup
xlErrValue VdbB MaxȲ Min ( FAME_EAR
Periodic_Ratei NPERYear
( FAME_GGMValue Div1f ReqRet+
GrowthRateƵ ( FAME_GGMCostEquityw ValueK ( FAME_CAPM4Y RiskFreeRate MarketReturn[ Beta. ( FAME_Geomean% DollarValues DVCountX SalesGrowtht] Count0v ( FAME_Payback CashFlowsi| Ratek
PayBackd CumSumD CFCount Counter ( FAME_Varj Values
Probabilities Variancen Avgu MacroEndA
( FAME_ExpValueK
( FAME_Covar% Values1u Values2u
Covariance Avg1IX Avg2JX ( FAME_Corra Correlation? Address ( FAME_StdDev StdDeviation IsNumeric$* Sqr( ( FAME_CVX
CoefVariationQ ExpValue3 i` ( FAME_PortRet Weights[
( FAME_PortVar1! VarCovMatA PVar ja
( FAME_PortVar2" CorrMatR StdDevsB CovMat ( FAME_TwoStageValue ReqRateD GrowthRate1 GrowthRate2 G1Periodsց V1t_ V2u_ ( FAME_ThreeStageValueN TransPeriods` Div0f ( FAME_MIRR< ReinvestRate8 FutureValue Ne ( FAME_PVGA Pmt NperA DiscRateT BegEndn ( FAME_FVGA ( FAME_Convexity,
Settlemente Maturity FaceValue z Coupon9B Yld Frequency Priceg Sj Payment tempR PeriodsX DateDiff NormDev R_ _ S R P _ 1 ` P R O J E C T w m l k P R O J E C T
n = S u m m a r y I n f o r m a t i o n ( s. This is different from Excel's built-in function in the way that it handles negative cash flows after period 0. Nper DiscRate BegEnd@ Calculates the present value of a graduated annuity. Note that BegEnd is an optional argument that describes whether the cash flows occur at the end (0) or beginning (1) of the period. End of period cash flows are assumed if the BegEnd argument is omitted1 Returns a normally distributed random variable with a given mean and standard deviation. Note that this function is volatile, and will return a new result each time the worksheet is recalculated. @ Calculates the FV of a graduated annuity. PMT is the first payment amount, NPer is the number of periods, GrowthRate is the payment growth rate, DiscRate is the required return, and BegEnd if the payments are at the end (0) or beginning (1) of the period.
Settlement Maturity FaceValue Coupon Yld Frequency ` Calculates the convexity of a bond } . Note that this function only works on a coupon payment date. Mean StdDev DollarValues / Calculates the geometric mean rate of growth for a series of dollar values. The first cash flow in the list must be the most recent cash flow, and the last must be the oldest cash flow. CashFlows Rate; Calculates the payback period of a series of cash flows. The first cash flow must be the (negative) initial outlay. The discount rate is an optional ar
HelpFile=""
Name="FameFncs"
HelpContextID="0"
Description="Functions for Financial Analysis with Microsoft Excel, 5th Ed."
VersionCompatible32="393222000"
CMG="767496227AEEF4F2F4F2F4F2F4F2"
DPB="ECEE0CB8F42FF52FF52F"
GC="62608236F937F93706"
[Host Extender Info]
&H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000
[Workspace]
ThisWorkbook=0, 0, 0, 0, C
Sheet1=0, 0, 0, 0, C
FAME_Functions=225, 225, 1319, 858, Z
fun ՜. +,0 P D o c u m e n t S u m m a r y I n f o r m a t i o n 8 w 0 C o m p O b j | r Oh +'0 P X
$ Functions for Financial Analysis Timothy R. Mayes, Ph.D. Timothy R. Mayes, Ph.D. Microsoft Excel @ H0t@ ZDr G
# [
' ' ' [ rU ~}
a > Functions for Financial Analysis with Microsoft Excel, 5th Ed. Cost
MacrsClass Year Table/ Calculates depreciation expense for Year using MACRS with the half-year convention. MacrsClass is 3, 5, 7, 10, 15 or 20.Table is optional, 0 for exact or 1 for table lookup. Default is 0.
14
Periodic_Rate NPERYear * 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. Div1 ReqRet
GrowthRate" Calculates the intrinsic value of a stock using the constant growth dividend discount model (also known as the Gordon Growth Model). Value _ Calculates the variance of a random variable given the probabilities of each potential outcome. Values1 Values2 ' 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). RiskFreeRate MarketReturn Beta. Calculates the required return for a security or portfolio using the CAPM (Capital Asset Pricing Model) using the risk-free rate, the expected market return, and the security's beta. a DollarValues / Calculates the geometric mean rate of growth for a series of dollar values. The first cash flow in the list must be the most recent cash flow, and the last must be the oldest cash flow. CashFlows Rate; Calculates the payback period of a series of cash flows. The first cash flow must be the (negative) initial outlay. The discount rate is an optional argument. If it is provided, this function will return the discounted payback period. Values
Probabilities , 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.0 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. i Calculates the standard deviation of a random variable given the probabilities of each potential outcome. VarCovMat Pmt= 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. c Calculates the Expected Value of a random variable using the probabilities of each potential value. Weights$ Calculates the expected return for a portfolio of securities given the expected returns of each security and their weights in the portfolio.< 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. CorrMat StdDevs< > 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 correlation matrix, not just the upper or lower diagonal. ReqRate GrowthRate1 GrowthRate2 G1Periods& 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. TransPeriods ? 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. ReinvestRate/ Calculates the Modified Internal Rate of Return for a series of cash flow ' [ ' [ -
- - @ ! - - @ ! - ( ( - @ ! ( - = = - @ ! = - Q Q - @ ! Q - e e - @ ! e - y y - @ ! y - - @ ! - - @ ! - - @ ! - - @ ! - [ - @ ! A - - @ ! - Z Z - @ ! Z - - @ ! - - @ ! - - @ ! - Z Z - @ ! Z - @ ! - - - @ ! - - @ ! - - - @ ! - - @ ! - - - @ ! - - @ !
- - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - - $
- - - ' ' Calibri /
0a' vw`ywkf - 'A> 2
1 2
*
2 2
?
3 2
S
4 2
g
5 2
{
6 2
7 2
8 2
9 2
6 A 2
w B 2
C 2
D 2
7 E - "System w
Sa' vw`ywkf - ' - 'A> [ -
@ ! * Calibri 0
ga' vw`ywkf - 2
MACRs Class
2
* - Year 2
* g 3-Year 2
* 5-Year 2
* 7-Year 2
* # 10-Year - 2
? Q 1 2
? m 33.33% 2
? 20.00% 2
? 14.29% 2
? - 10.00% 2
S Q 2 2
S m 44.45% 2
S 32.00% 2
S 24.49% 2
S - 18.00% 2
g Q 3 2
g m 14.81% 2
g 19.20% 2
g 17.49% 2
g - 14.40% 2
{ Q 4 2
{ t 7.41%a 2
{ 11.52% 2
{ 12.49% 2
{ - 11.52% 2
Q 5 2
11.52% 2
8.93%a 2
4 9.22%a 2
Q 6 2
5.76%a 2
8.92%a 2
4 7.37%a 2
Q 7 2
8.93%a 2
4 6.55%a - - ' - -
[ -
- - @ ! - Z Z - @ ! Z - - @ ! - - @ ! - - @ ! - Z Z - @ ! Z - > - @ ! > - > Z Z - @ ! > Z - > - @ ! > - > - @ ! > - > - @ ! > - > Z Z - @ ! > Z -
@ ! B - ( Z ( \ - @ ! ( Z @ ! B< -
- Q Q \ - @ ! BQ - e e \ - @ ! Be - y y \ - @ ! By - \ - @ ! B - \ - @ ! B - \ - @ ! B - \ - @ ! B - - ' - [ [ - ' - [ -
- [ - @ ! [ - - @ ! - '# A [ (
X p x
Excel Add-in MACRS Table MACRS_Table Worksheets
Named Ranges
F& Microsoft Office Excel 2003 Worksheet Biff8 Excel.Sheet.8 9q