c# pdf library mit license : Batch pdf metadata SDK software API .net winforms azure sharepoint Wiley%20Advanced%20Modelling%20in%20Finance%20using%20Excel%20and%20VBA8-part603

74
Advanced Modelling in Finance
If cell A5 contains one value for the total sales, then the commission (in cell B5) can be
evaluated in several ways, for example, with a formula involving nested IF functions:
=IF(AND(A5>=0,A5<10000),A5
0.08,IF(AND(A5
>=10000,A5<20000),
A50.105,A50.12))
However, if sales commission calculations are frequently performed and many more rates
involved, a better approach is to create a user-defined function which can be called
just like any other function in Excel. We start by discussing suitable VBA code for the
function, and in the next section explain how to write the code and test out the results in
aworkbook.
Look at the code listed below for a function named Commission, which requires one
input (or ‘argument’), the monthly sales total denoted Sales. The function is referred to as
Commission(Sales) and its output is the commission due. The code is written in a Module
sheet as with macros:
Option Explicit
’to force variable declaration
Function Commission(Sales)
’returns commission on sales
If Sales>=0 And Sales < 10000 Then Commission= 0.08
Ł
Sales
If Sales>= 10000 And Sales< 20000 Then Commission= 0.105
Ł
Sales
If Sales>= 20000 Then Commission= 0.12
Ł
Sales
EndFunction
Examining the code line-by-line, the function is declared with the Function keyword,
its name Commission, requiring one input, Sales, shown within the brackets. When
the function code is executed, a single-valued result is assigned to the function’s name
Commission. The conditional If... Then statements determine the appropriate commis-
sion rate and apply it to the Sales figure. Notice that only one of these statements will be
executed, so the function’s value is uniquely determined for the particular level of Sales.
The code ends with the required statementEndFunction. Note that statements following
an apostrophe sign are explanatory comments and are not executed.
In line with good programming practice, the Option Explicit statement at the beginning
of the Module sheet ensures that all variables used in the VBA code must be declared.
Here, since the only variables used, Commission and Sales, have been declared implicitly
in the opening Function statement, no further declarations are required. (This point is
elaborated more fully in section 4.3).
4.2 CREATING COMMISSION(SALES) IN THE SPREADSHEET
To create the Commission function in your spreadsheet, simply insert a Module sheet in
the workbook. To do this, choose Tools then Macro then Visual Basic Editor to go to
the VB window. With your workbook highlighted in the Project window, choose Insert
then Module from the menu. Enter the VBA code for the function in the Code window
(ensuring that Option Explicit is declared at the top of the Module sheet).
To test the function out on the spreadsheet, enter a formula such as:
=Commission(25000)
Batch pdf metadata - add, remove, update PDF metadata in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Allow C# Developers to Read, Add, Edit, Update and Delete PDF Metadata
c# read pdf metadata; read pdf metadata online
Batch pdf metadata - VB.NET PDF metadata library: add, remove, update PDF metadata in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Enable VB.NET Users to Read, Write, Edit, Delete and Update PDF Document Metadata
view pdf metadata in explorer; remove metadata from pdf
Writing VBA User-defined Functions
75
The result should be 3000 (since sales at this level qualify for commission at 12%). Or
if the sales figure is in cell A5, use the formula:
DCommission(A5)
As with other Excel functions, the Paste Function button (formerly the function dictionary)
can be employed to help enter functions like Commission. All user-created functions are
grouped in a category with the name User Defined.
If your function does not work, you may see an error message in the cell (such as
#NAME? or #VALUE?) which usually indicates a mismatch between the function and
input or that the function name is wrongly entered. Alternatively a Microsoft Visual
Basic ‘compile error’ message may appear, indicating a mistake in your code. In this
case, note the nature of the error (frequently ‘Variable not defined’), click OK and correct
the highlighted error. Then, most important, click the Reset button (with the black square)
on the VB Standard toolbar, which will remove the yellow bar from the function name
statement. Return to the spreadsheet and re-enter your function (either pressing F2 then
Enter or retyping the entry).
Another way to check the working of a function is to use breakpoints in the code and
to use the Locals window to observe the numerical values being calculated. For example,
make the statement:
If Sales>D0 And Sales<10000
a breakpoint by clicking the statement in the adjacent margin (as described in
Appendix 3A). Next return to the SalesCom sheet and re-enter (or refresh) the
Commission function. As the function evaluates, the cursor should jump back to the
function code, and the sequence of evaluation can be observed by stepping through the
remaining code.
Before leaving the VBE, you may wish to rename your Module sheet, as say Module0,
in the Properties window (as described in Appendix 3A).
In the next section, we develop some slightly more complex functions with several
inputs whose statements involve calls to Excel functions as well as VBA functions. The
functions for the Black–Scholes option value formula illustrate additional aspects of
writing code for user-defined functions.
4.3 TWO FUNCTIONS WITH MULTIPLE INPUTS FOR
VALUING OPTIONS
As yet, Excel does not have a built-in function to calculate the value of an option using
the Black–Scholes formula. This allows us to develop a user-defined function suitable
for valuing a call, named BSCallValue say. The underlying theory that is the background
to the Black–Scholes formula is introduced in Part III of the book. Although at this stage
you will not necessarily understand the option value formula, remember that our purpose
here is merely to turn the formula into workable VBA code.
The Black–Scholes pricing formula for a European call allowing for dividends is:
cD S expqTNd
1
 XexprTNd
2
In the formula, S is the current share price, X the exercise price for the call at time T, r
the continuously compounded risk-free interest rate, hence the expression exprT for
VB.NET Create PDF from Excel Library to convert xlsx, xls to PDF
C#.NET search text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET Professional .NET PDF converter component for batch conversion.
pdf metadata editor online; preview edit pdf metadata
VB.NET PDF Convert to Jpeg SDK: Convert PDF to JPEG images in vb.
PDF pages, C#.NET search text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET NET components to batch convert adobe PDF files to
embed metadata in pdf; remove pdf metadata
76
Advanced Modelling in Finance
the risk-free discount factor over period T. The continuous dividend yield on the share is
denoted q, so that the share price S is replaced by S expqT in the valuation formulation.
The notation Nd is used to denote the cumulative standard normal probability of a value
less than d. Here d
1
and d
2
are given by:
d
1
D[lnS/X C r  q C 
2
/2T]/[
p
T]
d
2
D[lnS/X C r  q  
2
/2T]/[
p
T] D d
1

p
T
where  is the volatility of the share.
The spreadsheet extract in Figure 4.2 shows details of a six-month option on a share
with current value S D 100, X D 95, r D 8.0%, q D 3% and  D 20%. Expressions d
1
and d
2
are evaluated in cells G8 and G11, from which the cumulative normal probabilities
Nd
1
and Nd
2
in cells G9 and G12 are derived, using Excel’s NORMSDIST function.
These are inputs to the call value cell formula (in G4):
DD4
Ł
D16
Ł
G9D5
Ł
D15
Ł
G12
which evaluates as 9.73. (The same inputs produce the related put value for the option
shown in cell H4.)
The call formula can be programmed into a user-defined function with six inputs
S, X, r, q,T, , whose answer is shown in cell G5.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
A
B
C
D
E
F
G
H
Black-Scholes Formula
Call
Put
Share price (S)
100.00
BS value
9.73
2.49
Exercise price (X)
95.00
BSval via fn
9.73
2.49
Int rate-cont (r)
8.00%
Dividend yield (q)
3.00%
d
1
0.6102
N(d
d
1
)
0.7291
Time now (0, years)
0.00
Time maturity (T, years)
0.50
d
2
0.4688
Option life (T, years)
0.50
N(d
d
2
)
0.6804
Volatility (s)
20.00%
Exp (-rT)
0.9608
Exp (-qT)
)
0.9851
Figure 4.2 Details of an option and Black–Scholes values for the call and put in BS sheet
The VBA code for theFunctionBSCallValue(S,X,r,q,tyr,sigma)is set out below.
The time to maturity for the option (in years) is denoted tyr and  is denoted sigma.
The calculations in the Black–Scholes formula are sufficiently complex for us to use
intermediate variables [denoted DOne and NDOne for d
1
and Nd
1
] in coding the
function:
Option Explicit
’to force variable declaration
Function BSCallValue(S, X, r, q, tyr, sigma)
’ returns Black–Scholes call value (allowing for q=div yld)
VB.NET PDF Convert to Tiff SDK: Convert PDF to tiff images in vb.
NET control to batch convert PDF documents to Tiff format in Visual Basic. Qualified Tiff files are exported with high resolution in VB.NET.
remove pdf metadata online; bulk edit pdf metadata
VB.NET PDF Convert to Word SDK: Convert PDF to Word library in vb.
project. Professional .NET library supports batch conversion in VB.NET. .NET control to export Word from multiple PDF files in VB.
edit pdf metadata acrobat; adding metadata to pdf
Writing VBA User-defined Functions
77
Dim ert, eqt
Dim DOne, DTwo, NDOne, NDTwo
ert = exp(-r
Ł
tyr)
‘exp is theVBA function for ‘e’
eqt = exp(-q
Ł
tyr)
‘dividend yieldeffect
DOne = (log(S/X)+(r-q + 0.5
Ł
sigmaO2)
Ł
tyr)/(sigma
Ł
sqr(tyr))
DTwo = (log(S/X)+(r-q –0.5
Ł
sigmaO2)
Ł
tyr)/(sigma
Ł
sqr(tyr))
NDOne = Application.NormSDist(DOne)
NDTwo = Application.NormSDist(Dtwo)
BSCallValue = (S
Ł
eqt
Ł
NDOne - X
Ł
ert
Ł
NDTwo)
End Function
The statementOptionExplicitat the top of the module sheet forces the declaration of all
variables used in calculating the function. To comply, the ‘Dim’ statement declares the
six variables (ert,eqt,DOne,DTwo,NDOne,NDTwo). Descriptive names have been
chosen for the variables, e.g.DOne for d
1
andNDOne for Nd
1
, etc. to link the code
to the conventional notation for the options formula. The variable ert is the discount
factor for converting payoffs at maturity to present values: in algebraic terms, exprT.
The variable eqt is the dividend yield effect on the current share price, algebraically
expqT. Note that the input variables (such as S, X, etc.) are automatically declared in
the Function command itself, so should not be included in Dim statements.
Despite the unwieldy appearance of the code, the calculation is relatively simple. Notice
that the three expressions ‘exp’, ‘log’ and ‘sqr’ are built-in VBA functions for ‘e’, natural
logs (ln) and square-root respectively. Where both VBA and Excel functions exist for the
same expression, the VBA function must be used rather than the corresponding Excel func-
tion. When Excel functions are used in the code, they must be prefaced withApplication.
orWorksheetFunction. Hence:
NDOne = Application.NormSDist(DOne)
uses the Excel function NORMSDIST.
Thus, the formula:
DBSCallValue(100, 95, 8%, 3%, 0.5, 20%)
evaluates to 9.73. (The formula in cell G5 displaying the value 9.73 takes its inputs from
the cells in column B.)
Turning to the corresponding BS valuation formula for a European put, the put–call
parity relationship ensures that the value of a European put is given by:
pD S expqTNd
1
C X exprTNd
2
with d
1
and d
2
as before. The only difference from the previous call formula is the change
of sign from positive to negative for the d
1
and d
2
arguments in the cumulative normal
probability expressions and the reversed signs for the two terms in the Black–Scholes
formula. By adding a further argument,iopt (with value 1 for a call and 1 for a put)
the VBA code for a call can be generalised to cover valuation of either European option,
call or put.
The code for function BSOptValue with seven inputs (including iopt set to 1 or 1)
is given below. If iopt D 1, NDOne becomes Nd
1
, NDTwo becomes Nd
2
, and
the signs of the two terms in the BS equation have altered since iopt D 1. Confirm in
the spreadsheet that for a put, BSOptValue(1, 100, 95, 8%, 3%, 0.5, 20%) equals 2.40
[whereas BSOptValue(1, 100, 95, 8%, 3%, 0.5, 20%) returns 9.73 as before]:
VB.NET PDF File Merge Library: Merge, append PDF files in vb.net
Batch merge PDF documents in Visual Basic .NET class program. Merge two or several separate PDF files together and into one PDF document in VB.NET.
pdf metadata; pdf metadata online
C# PDF Convert to Tiff SDK: Convert PDF to tiff images in C#.net
Studio .NET project. Powerful .NET control to batch convert PDF documents to tiff format in Visual C# .NET program. Free library are
pdf metadata editor; change pdf metadata
78
Advanced Modelling in Finance
Function BSOptValue(iopt, S, X, r, q, tyr, sigma)
’ returns the Black-Scholes value (iopt=1 for call, -1 for put, q = div yld)
’ uses fns BSDOne and BSDTwo
Dim ert, eqt, NDOne, NDTwo
ert = exp(-r
Ł
tyr)
eqt = exp(-q
Ł
tyr)
NDOne= Application.NormSDist(iopt
Ł
BSDOne(S, X, r, tyr, sigma))
NDTwo= Application.NormSDist(ioptŁ BSDTwo(S, X, r, tyr, sigma))
BSOptValue = iopt
Ł
(S
Ł
eqt
Ł
NDOne - X
Ł
ert
Ł
NDTwo)
EndFunction
Function BSDOne(S, X, r, q, tyr, sigma)
’ returns the Black - Scholes d1 value
BSDOne = (log(S/X) + (r-q +0.5ŁsigmaO2)Łtyr)/(sigmaŁsqr(tyr))
EndFunction
Function BSDTwo(S, X, r, q, tyr, sigma)
’ returns the Black-Scholes d2 value
BSDTwo = (log(S/X)+(r-q - 0.5
Ł
sigmaO2)
Ł
tyr)/(sigma
Ł
sqr(tyr))
EndFunction
Note that the intermediate quantities, DOne and DTwo, have been coded as functions
in their own right and are simply called from the ‘master’ function. The object is to
modularise the VBA code making its operation more transparent and allowing errors in
coding to be pinned down more accurately.
In summary, functions are VBA procedures which return values, their code being
enclosed in between the keywords Function and End Function. Following the Function
keyword, the name of the function is declared followed by a list of inputs in brackets.
Aseries of VBA statements follow and when the function has run, the name returns
the output:
Function name (input1, input2, ...)
[statements]
[name = expression]
End Function
In good VBA programming, it is preferable to head each Module sheet with the Option
Explicit statement, which forces formal variable declaration. Note that whereas all vari-
ables created and used in computing the function should be declared with Dim statements,
variables that are function inputs do not need to be declared. If Excel functions are
required in the code, they must be referred to with theApplication. or theWorksheet-
Function.prefix.
4.4 MANIPULATING ARRAYS IN VBA
Unfortunately, there is a step change in the complexity of coding functions with array
input and output. Therefore before developing such functions, it is worth considering how
arrays are handled in Excel. In many instances, familiar Excel functions such as SUM,
AVERAGE, STDEV, SUMPRODUCT and NPV can be employed to take care of the
array handling. In other cases, additional safeguards are required to code array handling
so that it performs as intended. In particular:
ž In the absence of suitable built-in functions, it may be necessary to process individual
array elements, so repeated operations in a loop are required. If so, the number of
C# PDF Convert to Word SDK: Convert PDF to Word library in C#.net
Powerful components for batch converting PDF documents in C#.NET program. Convert PDF to multiple MS Word formats such as .doc and .docx.
read pdf metadata; remove metadata from pdf file
C# PDF File Merge Library: Merge, append PDF files in C#.net, ASP.
NET components for batch combining PDF documents in C#.NET class. Powerful library dlls for mering PDF in both C#.NET WinForms and ASP.NET WebForms.
batch pdf metadata; rename pdf files from metadata
Writing VBA User-defined Functions
79
elements in the array must be counted, and this is easily achieved with code such as
Application.Count(avec)usingtheExcelCOUNTfunction.
ž It is important to be clear about the numbering of arrays. If the Option Base declaration
is omitted, the elements number from 0 (the default). If the Option Base 1 statement
is included at the top of the module, the array elements number from 1 as is usual in
Excel operations.
ž When matrix operations such as matrix multiplication are required, care must be taken
in checking dimensional conformity. (By conformity we mean that the number of
columns in matrix A must equal the number of rows in matrix B for the matrix product
AB to be defined and evaluated.) Coding statements may need to be included to ensure
that input arrays are in the correct format (row vector or column vector) for any matrix
manipulation.
Some simple functions are included in Appendix 4A to explain how arrays are handled
in VBA with an emphasis on manipulating matrices.
Array handling is illustrated in the next two sections, both explicitly in processing
individual items in the arrays as in the variance function in section 4.5 and implicitly by
using Excel’s matrix multiplication and other array functions in section 4.6.
4.5 EXPECTED VALUE AND VARIANCE FUNCTIONS
WITH ARRAY INPUTS
Suppose that the cash flows from an investment are uncertain, having five possible levels,
the probabilities of each level being as shown in the first column of Figure 4.3. Evaluating
the Expected Value of the cash flows requires each cash flow to be multiplied by its
probability, as shown in column D, before the products are summed.
2
3
4
5
6
7
8
9
10
11
12
A
B
C
D
E
Expected Values
Probs p(i) Cflows Cf(I) Devs
p(I)*Cf(i) p(i) * Dev
ev
2
0.05
-500
-1250
-25
78125
0.2
100
-650
20
84500
0.5
700
-50
350
1250
0.2
1300
550
260
60500
0.05
2900
2150
145
231125
Expected Values
750
Variance
455500
Std Dev
674.9
Figure 4.3 Cash flows with their probabilities in the ExpValues sheet of VBFNS workbook
Aconcise formula which encapsulates this method uses the Excel function:
SUMPRODUCT(B5:B9,A5:A9)
In passing note that the two arrays in SUMPRODUCT must both be column vectors (as
here) or both row vectors. Also note that this formula is not appropriate for returning
expected values if the second of the arrays (A5:A9 here) does not sum to one as prob-
abilities should.
80
Advanced Modelling in Finance
The spreadsheet calculations for variance are less compact, requiring each probability
to be multiplied by the squared deviation of the cash flow from its mean, as indicated in
column E. So it is an advantage to have VBA functions for the variance (and for simplicity
for the expected value of the cash flows as well), since with functions the element-
by-element calculations are performed ‘off-sheet’. The two functions,WVarianceand
ExpVal,haveasinputsthetwoarrayswhosecontentscombinetoproducetheexpected
value, the cash flows arrayvvec and the probabilities arraypvec.
The code for theExpVal(vvec,pvec)function centres on using Excel functions SUM,
COUNT and SUMPRODUCT. Since the Excel functions deal with all array handling
for this function, the code is straightforward. The initial If... Then conditional statement
strips out cases when the expected value cannot be sensibly computed, i.e. when the
elements of array pvec do not sum to one or when the two arrays do not have the same
number of elements. In these cases the function returns the value 1:
Function ExpVal(vvec, pvec)
’returns expected value for
2arrays
IfApplication.sum(pvec) <> 1 Or
Application.Count(vvec) <> Application.Count(pvec) Then
ExpVal = -1
Exit Function
ElseIf pvec.Rows.Count <> vvec.Rows.Count Then vvec=Application.Transpose(vvec)
EndIf
ExpVal = Application.SumProduct(vvec, pvec)
EndFunction
For the SUMPRODUCT function to work, both input arrays should be vectors of the same
type, row or column vectors. If the inputs are not of the same form, vvec is transposed.
The VBA code for the WVariance function requires individual elements of the arrays
to be handled. TheOptionBase1declaration at the top of the module sheet ensures that
array elements number from 1 (as opposed to the default 0). As in the ExpVal function,
conditional branching filters out the cases when the elements of pvec do not sum to unity
and when the two arrays have different numbers of elements:
Option Base 1
’makes array numbering startfrom 1
Function WVariance(vvec, pvec)
’calculates variance
Dimexpx
’Expected Value of x
Dimi As Integer
IfApplication.sum(pvec) <> 1 Or
Application.Count(vvec) <> Application.Count(pvec) Then
WVariance = -1
Exit Function
EndIf
expx = ExpVal(vvec, pvec)
’using the ExpVal function
WVariance = 0
For i = 1 To Application.Count(pvec)
WVariance = WVariance + pvec(i)
Ł
(vvec(i) - expx)O2
Next i
WVariance= Application.Sum(WVariance)
EndFunction
Once again, the If... Then conditional statement strips out cases when the variance cannot
be sensibly computed. The variance is built up incrementally using aFor...Nextloop as
shown above. Notice that squared deviations are expressed in the form:(vvec(i)-expx)O2 ,
Writing VBA User-defined Functions
81
where expx is the expected value of the cashflows, vvec(i). This quantity is evaluated
using the VBA function ExpVal, that we have just created, hence in VBA code:
expx = ExpVal(vvec, pvec)
As the loop counter i increases on each loop, the variance is incremented by adding one
pvec(i)
Ł
(vvec(i)–expx)O2term.
As an exercise, develop the code for a function to evaluate the Standard Deviation of
the cash flows. Remember to use the VBA built-in function, Sqr, rather than the Excel
function. It would be sensible to include a test that the WVariance function is giving
a meaningful numeric output (i.e. that it is non-negative) before taking its square root.
(VBA code for the standard deviation is given as a solution to Exercise 4 at the end of
the chapter.)
If your function does not work, you may need to add some well-positioned MsgBox
functions to indicate intermediate values. For example, if the probability in cell A5 of
Figure 4.3 is changed from 0.05 to 0.5 say, the sequence of processing can be checked
by inserting a suitably worded message box(MsgBox“Gothere”) immediately before
the statementExitFunction.
Another approach is to test the procedure by calling it from a subroutine. Run-time
errors display as usual, and you can also use the debugger to track down the problem. For
example, the macro CheckFn sets up the arrays vvec and pvec, then calls the Function
WVariance. The debugger will pop up error boxes if there are errors in the code:
Sub CheckFn()
’calls WVariance function
’needs named ranges vvec, pvec in spreadsheet
Dim vvec As Variant, pvec As Variant
Set vvec = Range(“vvec”)
Set pvec = Range(“pvec”)
Range(“B22”).Value = WVariance(vvec, pvec)
End Sub
It is worth adding a brief description of what the function does, so that it appears when you
access the function in the function dictionary. To do this, start in the VB environment with
the relevant Module sheet active. Then access the Object Browser which allows you to
look at the methods and properties of Excel’s various objects. With the current workbook
name displayed in the Libraries part, choose the Module sheet where your function code
resides, and select your function in the Members window. On the right mouse button,
one option is Properties, whose dialog box allows you to add a brief description of your
function. Finally, click OK to close the dialog box. Check that your description appears
when you access the function via the Paste Function button.
4.6 PORTFOLIO VARIANCE FUNCTION WITH ARRAY INPUTS
Functions to calculate the mean and variance of returns for portfolios are easy to
develop. To illustrate the process, a simple spreadsheet formulation using Excel functions
is explained, then the cell formulas are coded in VBA to produce the corresponding
functions.
The spreadsheet in Figure 4.4 contains risk and return information for three assets, A,
B and C, and shows the results of constructing a portfolio with equal amounts of each
82
Advanced Modelling in Finance
of the three assets. The asset returns are in cells C5:C7, a column vector referred to with
range name e in the spreadsheet formula and in the VBA code referred to asretsvec.
The portfolio is made up of the three assets in the proportions set out in cells E5:E7, a
second column vector with range name w in the spreadsheet formulas and referred to as
wtsvecintheVBAcode.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
B
C
D
E
F
G
Portfolio Risk & Return
e
w
Portfolio
Asset Data
Exp Ret StDevRet
t
weights
s
ExpRet
t
A
8%
4%
33.3%
9.3%
B
15%
5%
33.3%
StDev
C
5%
1%
33.3%
2.57%
100%
Correlations
1
0.5
-0.2
0.5
1
-0.1
-0.2
-0.1
1
VCV Matrix  V
A
B
C
A
0.0016
0.0010
-0.0001
Variance
B
0.0010
0.0025
-0.0001
=w
T
Vw
C
-0.0001
-0.0001
0.0001
0.07%
Figure 4.4 Risk and return for portfolio of three assets in PFolio sheet
The expected portfolio return (in cell G5) is the weighted sum of the expected returns on
each of A, B and C (in fact, 0.093 or 9.3%). The formula in G5 using the named ranges
wand e is the Excel function SUMPRODUCT(e,w), which gives the weighted return,
provided the number of elements in each of the column vectors is the same.
The variance–covariance matrix for the three assets can be calculated cell-by-cell from
the correlations (C10:E12) and the individual asset return standard deviations (in cells
D5:D7). The resulting matrix of cells (C15:E17) contains the individual variances of
returns for assets A, B and C on the diagonal and also the covariances for all pairs of
assets in the off-diagonal cells. Suppose this variance–covariance matrix is given the range
name V. Together with the weights array, it forms the basis for calculating the portfolio
variance, essentially a matrix multiplication of form wT
Vw where w
T
is the transpose
of the weights vector w. The formula in cell G17 for variance combines the Excel func-
tions TRANSPOSE for transposing an array and MMULT for matrix multiplication. It is
written as:
MMULT(TRANSPOSE(w), MMULT(V,w))
(Since this is an array function, in entering the formula into cell G17, remember to press
the keystroke combination CtrlCShiftCEnter.)
For Excel’s MMULT function to work, the dimensions of any pair of adjacent matrices
being multiplied must conform, i.e. the number of columns in the first matrix must equal
the number of rows in the second matrix. Here TRANSPOSE(w) is a 1 by 3 row vector,
and MULT(V,w) is a 3 by 1 column vector, so matrix multiplication is possible, giving
the single cell value of 0.0007 (or 0.07%). So for this portfolio, the risk in return is
p
0.0007 D 0.0257 (or 2.57%).
Writing VBA User-defined Functions
83
It is simple to write the code for the portfolio return function, calledPFReturn(wtsvec,
retsvec):
OptionBase 1
Function PFReturn(wtsvec, retsvec)
’returns weighted return for PortFolio
If Application.Count(wtsvec) <> Application.Count(retsvec) Then
PFReturn = “Counts not equal”
Exit Function
ElseIf retsvec.Rows.Count <> wtsvec.Rows.Count Then
wtsvec = Application.Transpose(wtsvec)
End If
PFReturn = Application.SumProduct(retsvec, wtsvec)
End Function
The second function,PFVariance(wtsvec,vcvmat), has a more complex code:
Function PFVariance(wtsvec, vcvmat)
’calculates PF variance
Dim nc As Integer, nr As Integer
Dim v1 As Variant
nc = wtsvec.Columns.Count
nr = wtsvec.Rows.Count
If nc > nr Then wtsvec = Application.Transpose(wtsvec)
v1 = Application.MMult(vcvmat,wtsvec)
PFVariance = Application.SumProduct(v1, wtsvec)
End Function
The function has vector and matrix inputs and requires VBA statements that satisfy the
conformity requirements for matrix multiplication. We would prefer the function to work
when the weights array is a row vector as well as when it is a column vector. To achieve
this, the dimensions of the array are determined. Variablesnr andnc (representing the
number of rows and columns) for the weights arraywtsvecare declared as integers. For
the matrix multiplication to work, the weights vector must be a column vector, hence the
statement:
If nc > nr Then wtsvec = Application.Transpose(wtsvec)
Atemporary vectorv1 is used in the matrix multiplication, so it is declared up front as a
variant. As mentioned in Section 3.4.1, this is a flexible data type that can be of any form,
scalar or array or matrix. Herev1 stores the 3 by 1 column vector Vw. One convenient
way of evaluating w
T
Vw in VBA is to use the SUMPRODUCT function to multiply the
elements of the two 3 by 1 column vectors w and Vw. Hence the penultimate statement
line of code:
PFVariance = Application.SumProduct(v1, wtsvec)
which returns a scalar value to PFVariance.
The code for the PFVariance function centres on ensuring that the Excel MMULT
function evaluates properly. Because the user enters the function arguments, the code
needs to adjust them where necessary to enforce matrix conformity. However, since
the MMULT and SUMPRODUCT functions handle all the array computations, it is not
necessary to declare dimensions for arrayv1 in the code or to process individual elements.
This keeps the code somewhat simpler.
Although the spreadsheet illustrates results for a three-asset portfolio, the two functions
have the advantage of working with arrays of any size.
Documents you may be interested
Documents you may be interested