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

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),

A5∗0.105,A5∗0.12))

However, if sales commission calculations are frequently performed and many more rates

involved, a better approach is to create a user-deﬁned 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 e 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 ﬁgure. 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-deﬁned Functions

The result should be 3000 (since sales at this level qualify for commission at 12%). Or

if the sales ﬁgure 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 Deﬁned.

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 deﬁned’), 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-deﬁned 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-deﬁned 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

 XexprTNd



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

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

and d

are given by:

D[lnS/X C r  q C 

/2T]/[

D[lnS/X C r  q  

/2T]/[

T] D d



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

and d

are evaluated in cells G8 and G11, from which the cumulative normal probabilities

Nd

and Nd

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-deﬁned function with six inputs

S, X, r, q,T, , whose answer is shown in cell G5.

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%

0.6102

N(d

)

0.7291

Time now (0, years)

0.00

Time maturity (T, years)

0.50

0.4688

Option life (T, years)

0.50

N(d

)

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 sufﬁciently complex for us to use

intermediate variables [denoted d DOne and d NDOne for d

and Nd

] 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-deﬁned Functions

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

andNDOne for Nd

, 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 e 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

C X exprTNd



with d

and d

as before. The only difference from the previous call formula is the change

of sign from positive to negative for the d

and d

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

, NDTwo becomes Nd

, and

the signs of the two terms in the BS equation have altered since iopt D 1. Conﬁrm 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

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

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.preﬁx.

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-deﬁned Functions

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 deﬁned 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 ﬂows from an investment are uncertain, having ﬁve possible levels,

the probabilities of each level being as shown in the ﬁrst column of Figure 4.3. Evaluating

the Expected Value of the cash ﬂows requires each cash ﬂow to be multiplied by its

probability, as shown in column D, before the products are summed.

Expected Values

Probs p(i) Cflows Cf(I) Devs

p(I)*Cf(i) p(i) * Dev

0.05

-500

-1250

-25

78125

0.2

100

-650

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 ﬂows 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.

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 ﬂow 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 ﬂows as well), since with functions the element-

by-element calculations are performed ‘off-sheet’. The two functions,WVarianceand

ExpVal,haveasinputsthetwoarrayswhosecontentscombinetoproducetheexpected

value, the cash ﬂows 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 ﬁlters 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-deﬁned Functions

where expx is the expected value of the cashﬂows, 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 ﬂows. 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

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.

Portfolio Risk & Return

Portfolio

Asset Data

Exp Ret StDevRet

weights

ExpRet

33.3%

9.3%

15%

33.3%

StDev

33.3%

2.57%

100%

Correlations

0.5

-0.2

0.5

-0.1

-0.2

-0.1

VCV Matrix V

0.0016

0.0010

-0.0001

Variance

0.0010

0.0025

-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

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 ﬁrst 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

0.0007 D 0.0257 (or 2.57%).

Writing VBA User-deﬁned Functions

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 ﬂexible 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

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

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