ࡱ> H<F  !"#$%&'()*+,-./0123456789:;=>?@ABCDEGLI\JKM[NOPQRSTUVWXYZ]_^`Root Entry FP0q^@EWorkbook_VBA_PROJECT_CUR"Pi9*z*VBAPi9**  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVXYZ\]^_`abcdefghijklmopqrtvwxyz{|}~ @\pA Sn Smithee Ba= ThisWorkbook=-=;&8X@"1Arial1Arial1Arial1Arial"$"#,##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\);_(* "-"??_);_(@_)                + ) , *  `Sheet18 @ A  dMbP?_*+%"??U>@7 Sheet1 rU~|  a I  y 9  , o,o4' :l l lA "q`5 l`P qhlA4T5Module1Of__SRP_2 __SRP_3WThisWorkbook [Xp #   "  0@ X`x   .">( P(X x   ,  F hp 4x B^!(@ HPX @`   - -/0 HP `h p"     N 8  X h x       (0 @P X p   0 8 @P< X    B @Ph pB_ -/   08 @"X h p x  T P LP B  2!( `hR3p 0* Hx   ,   8 X !h" T V Vp V R ! Rx! N! T " Rx" V" R(# T# R# "0$X$`$h$p$x$$ V$ ,$%0%H%(`% %% R% T& 4h&& P& R&t>P' <'(&(( RP(((((( ) B0)x)$) ")$)*<-(* h* ,x*H/** 4+@+X+ `+p++`,xpL APY calculates the IRR (Internal Rate of Return) of an investment. This isQ*J the Annual Percentage Yield a bank account would have to have in order toL arrive at the same balance as the present value of the investment, assumingL you put the same amounts of cash into the bank account as you put into yourL investment (and take out the same amounts) on the same dates. The term APYJ means the amount a deposit in a bank with compound interest will increaseL (in percent) during a one year period (which will be higher than the annual@ interest rate if the compounding period is less than one year). dKvD`@@`B<`D 8`F0`H@. `      ``tmen`b tes `f0`h8Date`j not`l Cur`nN `p to %@l H @P JRTVX\dr@@@ @( -C6?@hHP`Xp8  p8@ pD@0O DG@ L@ LHN@X p0Z@X O XZX]@ b@  @b@ @ @ @ p h@h @ @ @ @p @` @ P @( @ @0 @@ @ @ X @O @  @   0 @@  X  @x  h @ @ @  x @  @   @  ( P @`  x 8 %@ ` %@  '@  ' )@  ( /@ 8  /H  1` 8@%7$*\Rffff*033dca9ed8*\R1*#175*\R0*#13*\R0*#0*\R0*#12*\R1*#5c*\R0*#15M + + + R Ph R R Rh P R Fh L, J+ B$. R 08 Rh  T J8 T T T8 0 V D T` T R Rh , L T@ T T &H Np " T N@ R L N8 0 T R Jh T T h R L P( Tx P N  p &  8X` *h    (0 H !-C6?@@L CashAmounts is a list of cash amounts added to (positive values) or removed* from (negative values) your investment.K TransactionDates is a list of dates which apply to the CashAmounts (and/or CurrentValue amounts).M CurrentValue can be either the present value of the investment, or a list ofC values of the investment as of different dates (specified by theN TransactionDates). If StartDate is not used, only the last CurrentValue isN used and is assumed to be as of the last date in TransactionDates (unless aN date is specified by CurrentDate). When StartDate is used, there must be a* CurrentValue for each TransactionDates.O CurrentDate specifies the date of the current value (CurrentValue), overriding> the usual current date (the last date in TransactionDates).M StartDate, when used, causes the APY to calculated from the StartDate to theN current date. The value of the investment as of StartDate is taken to be aL "cash into" the investment. If there is no TransactionDates/CurrentValueK entry for the specified date, StartDate is assumed to be the date of the& nearest TransactionDates available.F StartValue specifies the investment value as of StartDate, overridingM the usual value (the first CurrentValue with a matching TransactionDates).N Compound specifies whether the interest should be periodically compounded, asN in the standard definition of IRR (when True), or simple (when False) which is much faster to calculate.H MaxPercent is the maximum value which will be returned. The default is 1000, which is 100,000%.M PercentAccuracy is the approximate accuracy desired. The default is 0.0001,H which is plus-or-minus 0.01%. Note that PercentAccuracy is a minimumK desired accuracy, the actual accuracy may be greater. Also note that ifF the calculated current value using the returned APY is within 0.001H (when using dollars, 1/10th of a cent) of the value specified for the* investment, PercentAccuracy is ignored.M Note: CashAmounts/TransactionDates pairs with dates greater than the currentL date are ignored (current date as specified by the calling arguments, notD the current date from the system clock). When StartDate is used,M CashAmounts/TransactionDates pairs with dates less than the start date areM ignored, and the first pair with a date equal to the start date is ignored as well.L Note: the dates in TransactionDates can correlate to data from CashAmounts,E CurrentValue, or both. There must be a CashAmounts value for eachJ TransactionDates value, and (if StartDate is used and StartValue is notM specified) a CurrentValue value for each TransactionDates value. However,J a value of 0 can be used for CashAmounts when there is no cash added orG removed on that date (that is, when TransactionDates applies only to CurrentValue).]]]]](]@]`]]]]]]0]H Check percentages for validity. ( *Percentages must be > 0.'zk# Get cash amounts into cashs array. $J !L'0 0 :x', 6 !N 6!P ,+: , ', 6d $R T '0 0 : ,  ,$ , +: ,d@'0: +:kk Get dates into dates array. $J !L'2 2 <', 6 !N 6!P ,+< , ', 6dH $R T '2 2 < , 2 ,$$R V ,$$X , +<d ,$ , +<k ,dh '2< $R V $X+<d +<k k k 0 2>Number of cash amounts must match number of transaction dates.'z k - Use last CurrentValue cell as current value. $J  !Z!L %Z!L !Z!L %Z%N'>d $R T $'>d '>k k : If StartDate is to be used, get StartDate and StartValue. "$\'B'Fd8 "$J "%Z%N'Bd "$R T$"$R V$"$X'Bd $"'Bk d "$R V "$X'Bd` "'BkP kH k@ B'BG If StartValue isn't given, find it from CurrentValue with nearest date1 (and adjust StartingDate to corresponding date). $$\'@ $J !L'4 4 2G^j', 6 !N ,$< B  @ 6!P'F ,$< B '@ ,$<'Dk , ', 6d $R T '4 4 2G^j , 4 , $< B  @ ,$'F , $< B '@ , $<'Dk ,d'4 4 2^3Number of value amounts must match number of dates.'z`kX 'F$<'Dk8k0 D'Bd  $'Fkk< Use last CurrentDate cell as current date unless specified. $\ 2 $<'.d $J !Z!L %Z!L !Z!L %Z%N'.d0 $R T$ $R V$ $X'.d$ '.kd $R V $X'.d '.kkxkpkh .'.M Elminate cash/date pairs where date is too old or cash is 0, and pairs whereI date is too early if StartDate is used. Note that if there are multipleF entries with the same date as StartDate, only the first such entry is< eliminated. Also, adjust dates to be relative to CurrDate.'0 "$\G'HcG'Hj , 2  "$\ ,$< B H'Hd ,$: ,$< .  "$\ ,$< B  . ,$< 0+< ,$: 0+: 0 '0kk , 0 "$\"At least one cash amount required.'zkx "$\G . B 'Bj &% Do a simple interest version of IRR.] ]8 F'` F B'b , 0 ` ,$: '` b ,$: ,$< 'b , bG dY('Fzjv@ > `  b'zHk@N Do the IRR (Internal Rate of Return) formula to calculate the APY (AnnualizedO Percentage Yield) of an investment. All dates are the number of days from theO actual date to the "current" date (where current date is the date at which theO portfolio is valued). StartValue is the starting value of the portfolio whichL is treated as a cash-in, and StartingDate is the date of the starting valueL (set these to 0 if you don't want to use them). SizeCashs is the number ofH cash transactions. Cashs is an array of cash-ins (positive values) andM cash-outs (negative values), and Dates is an array of the date correspondingL to each cash transaction. CurrValue is the current value of the investmentO (the date of which is 0, by definition). MaxPercent is the upper limit on theL permitted return value. PercentAccuracy specifies the approximate accuracyN desired (that is, 0.0001 indicates + or - 0.01%). The iterative solving loopK also stops if the calculated result is less than 0.001 (a tenth of a cent) off from the current value.]P]h]]]]O Begin by checking out two special cases. The first is if the result using the% MaxPercent limit is still too small. ( m'l F l B'n , 0 n ,$: l ,$< 'n , n > G ('FzjL Second special case is if the cash-ins (minus cash-outs) equals the currentN value, in which case we want to return exactly 0%, not something close to 0%,. so our first Try will be 1 (which equals 0%).'lI But first, calculate the maximum number of iterations possibly required,L assuming Newton's Method is never worse than bisection (which it might be). (Y@Y@  *$r9B.?? 'j 0.693147180559945 is log(2)6 Translate annual percentage into daily percentage +1. ( m'(dd m'fK Iteration loop starts at 0 to make up for the first run-through with Try=1 h j F l B > 'n , 0 n ,$: l ,$< 'n ,< If result is within 1/10th cent, assume that's close enough nMbP? Gyj n G l'(cG l'fj Calculate first derivative F B l B 'p , 0 p ,$: ,$< l ,$<  'p ,& Choose next try using Newton's Method pG ( f 'lcG l n p 'lj l f l (. Newton is out of range, use bisection instead ( f 'lk h lm 'ip Version 2002/2/25D send it through www.paypal.com to email address phil32767@yahoo.comou enjE If you enjoy this program and wish to make a donation to the author,ww.pBAttribute VB_Name = "Module1" Option Ex@plicit@Base 0 'A VersJ2P002/5.APY calculates the IRR (Internal R. of Re turn) an investment. Th`is isf?Annu8Percage Yield a bank accoun t wouha@ve to in order!LarriatKs|balance aUpres1 valueM?M,suming'you pu&amDscashjD S5inr'3 (and t\akA/n`d{erm 'meanjKdepositKNwith comp.d+e@Rill@cre|(@Rp_) d ur@@one year iod (which wAbe high^hwAAaq=rifB3!lesA1CE). If Qenjoy progradm AOwi`@WmAQa donaB"uthor,|>qroug/ww.paypal.$ema@il addGs phil32767@yahoo $ FuncS@`(CAÃ, TrbaDk@, CurrVy, C ,` _ StartNtAs Bool@!@TrueBy Maxk ` Doubl1000#uAcc uracy 0.a 1) G".Descri#CLRR) for`;Sf. Proc$a Invoke_!3Aa \n14`' 3 `Ylistwy@>eqo (g"s@pr r@emovedaH from (negL`{ A y #iapp:l`[ocI/o† jI DdLin re@~re - $#%hHdiff {(specifi(bte=- )y\!"not used, on la`4)g ~as@61.r (unBa s s!When j re |mu Yea@ !@6!}#,n!D( )`&ved`!us(qJ$V,)P|cap =Rd1W ik%%y0g0q0po-"&w"2)%Pno/ YryG1 g a~,[ 'snPro'aveaV2\6Sc-6afir4a matcnhw9 Cesq 09|shB{`/|i!;}rt1$Lwell33!pn_SccoAqgag3]pRr both -Thzi Aa%azC !R(3(0XR#]P+~): R Howe@~Dat2>0 l8Denε there is no cash added or ' remov= vbAHB CUB@@d "DAE1 To : !! +!A6( d.Ei$%)0 c00h NOᕤNCaN MOtsO"OcOO P C,Int( Q%?Q (Q ) Q=G#4';?#$anRPEvbStrhaCc$*"{(E (-Z!a u %Y _i NiIi=G_t|)(l-?=5u` ^<> "I= vbArray Then 5Size+sCUBg !6q<> 1 GoTo ErrorFor ||1 AQ8A8bs(Џ <ÃځDiffx(<~ Nā#= 'WPB9S UW B1 g \z A~By:)APY@V"Number of "v amts must match nd."Exit FunctioPEz10L  !  c 1mRe ' Uspe la@'DV cell as c ( unless specified. `:IsMiss%}})e '|- 1`!' Object !I( A .Areas #.CC). _C !`(?,(1))լJ(o tQ )b Ʀdd D33!GF/-!6  c@?7ap< >1@At lat@/e 8+quir5䁿e${$=w{0qCompid%' Do a sim< intDyvers1IRR5Di m SumB Asub??v/ *j3?9 }w i + >#M3 ; b*-q#)= C !CV(xlPDiv0): ?365># RvBpG ) /5" $ qW0 (eernal]@Return) for]ad@+lcuV_e(AnnualdZPerc[age Yield)!#an$`vestmb\l9fs aciys` fromQcaLctitod"Ġ"S(w"hqohgtqipeportfolio!md0S%>s  whP teda`sh-inSv_]z[(setg0Qvyou don'ɠ ano x1m . & ' cash transactions. Cs is an array of-ins (positive values) Ld -outDnega D,F Datethe d correspondingOto ea churrV}AcentinvestmN(iwhiY0, by definjon).MaxpPerc'upper limit on ( ted repturn> AcKacy sp`ecifi~&approximza'desir&Tatl, 0.0001 ics + or - 1%TTitersolv loop(also stopfZlcuFl%Ysul pless@{1 (a t>hQa A)Aoff` from[. Dim MinMAs DoubleCI(@NIntegerLLVTry Re-LFirst ' BeginwcheckcE twDcaleAQf{iCJGus{ȅ still to@mall#B,= ( + 1) ^ (1 `/ 365@S1= Start* (A^À F@}Lu1 = 0 To SizeC- 1 W+( ) DNext b If q< C<#n0 APY`g: Ex`aFunCSec`wc]+s)Çminusa/h) equ UJ ,`_ cue w@a|@Dnexly 0%, not someth6cloaLhen !7El/P'M7derivoS`_UOP(jTEV s^VXjXCho!Ln!ZtphCb5w1?2 ? 3Q-  6#pI<=~Or2 >@ 'NaNaxRBge, uwNteaA1o b SEnd If b9 ka^wP7=kTa BlhlP5 lhqh Tl^ Ol5Iq`l`Phl5IdL#h@l BlhlP5 hf q`PBlP l *lA "q\5 l\L qhlA4T5'Ta "lhlL5 lhqh Tl^ &l5Iq\l\Lhl\d#.h@l ^q-l5 >h@l  "lhlL/6 (h@l "lhlL5  hf~ q\Ll^,  "lL/5  "lL l`l\ :l l 9^l= A%l>A@%l=Ap%l> BBtD6 p@ l^ r!l5Ii%l BtD5  BtD l  q<t0 l -(%(%l > B"q<6 l ^ y-(%l ^q-l5 Y/(%l  "qA@%l=Ap%l> B"qd6 p@ 4 l^ -(%l ^q-l5 /(%l  "qd/6 (%l "qd5 2 l^! "  "qd/5 0  "qdldqd q` l a p.h p.hl\d! !l lhlLlo,Y@Y@o49 9B.??po,mt,dmtkc o0ol<ϳoDthl`d| olhlPolhlLϳt hfT oMbP?  o ot, oto0lDates1 CurrValue StartDateDiffH StartingDatez DateFound StartingValue= FoundFlag3IsObjectCount0vCellsValueKVarTypepvbArray]9vbString`e DateValue/AreasK IsMissing SizeError'SumCashs' SumCashDates xlErrDiv0 MinPercent 2 Iteration?IterationLimitDTryResulturFirstzLogd ThisWorkbook| Sheet1Workbookkyouenjoy Worksheetluw  6%0* pHd VBAProject4@j = r %7 J< rstdole>stdole f%\*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\SYSTEM\c2.tlb#OLE Automation#^MSForms>!SFEr ms3DD452EE1-E08F-101A-8-02608C4D0BB4 DNT\System32\FM20.DLL#Microsoft 9 \ Ob Library/;C1s00}#:0#j0A69F81FBB-DAAD-4EE9-B199-74EA3F0D57C26DOCUME~1\PHILIPLOCALSTemp\VBE\T.exd <!.E .`M Offi cOfijc 12DF8D04C-5BFAgB-BHDE5JAA41PROGRAM FILES\MICROSOFT OFFICE\MSO97m# 8.0 n""v<odule1*Go@uB1 2HB1ԲKBE,Bt 0. Count! |Number of cash amounts must match number of transaction dates. Areas fNumber of value amounts must match number of dates. DAt least one cash amount required.VBE6.DLL  2  3     P   K (FT5E`7G,-F O8m}'gDFWorkbook @F4hC) F#R CjDF WorksheetrU~~{    CashAmountsTransactionDates CurrentValue CurrentDate StartDate StartValueCompound MaxPercentPercentAccuracy?Calculates the Internal Rate of Return (IRR) for an investment. 14   __SRP_1PROJECTwmVPROJECTSummaryInformation(     0Module1Module1ThisWorkbookThisWorkbookSheet1Sheet1ID="{E4BAAA32-7041-11D3-AF93-00E0291CF87C}" Module=Module1 Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 HelpFile="" Name="VBAProject" HelpContextID="0" VersionCompatible32="393222000" CMG="2C2EFFDC01BD05BD05BD05BD05" DPB="090BDADFDBDFDBDF" GC="E6E4356ACF9AAD9BAD9B52" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] Module1=-1, -1, 867, 716, Z ThisWorkbook=0, 0, 0, 0, C Sheet1=0, 0, 0, 0, C Oh+'0@HTl s Alan SmitheexMicrosoft Excel@Q@Ah*՜.+,0 PDocumentSummaryInformation8CompObjfXd lt| 1 Sheet1  Worksheets FMicrosoft Excel WorksheetBiff8Excel.Sheet.89q