Simon Benninga. Financial Modelling 3-rd edition

908 Chapter 37

37.5.4 The Do . . . Loop Until Statement

The Do . . . Loop Until statement is a member of the bottom-checking

loops family. It makes VBA execute one or more statements one or more

times until a condition becomes true. The following function demon-

strates this behavior:

Function DoUntilDemo(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N < 2 Then

DoUntilDemo = 1

Else

i = 1

j = 1

Do Until i > N

j = j * i

i = i + 1

Loop

DoUntilDemo = j

End If

End Function

1

2

3

4

5

6

7

AB C

N

11

<

--

=DoUntilDemo(A3)

1.4 1

<

--

=DoUntilDemo(A4)

22

<

--

=DoUntilDemo(A5)

7 5040

<

--

=DoUntilDemo(A6)

8

40320

<

--

=DoUntilDemo(A7)

DOUNTILDEMO IN ACTION

909 Types and Loops

37.5.5 The While Statement

The While statement is a leftover from Quick Basic (a forerunner of

VBA).

2

The While statement, a member of the top-checking loops family,

executes a series of statements as long as a given condition is true. The

following function demonstrates its workings:

Function DoLoopUntilDemo(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N < 2 Then

DoLoopUntilDemo = 1

Else

i = 1

j = 1

Do

j = j * i

i = i + 1

Loop Until i > N

DoLoopUntilDemo = j

End If

End Function

1

2

3

4

5

6

7

AB C

N

11

<

--

=DoLoopUntilDemo(A3)

1.4 1

<

--

=DoLoopUntilDemo(A4)

22

<

--

=DoLoopUntilDemo(A5)

7 5040

<

--

=DoLoopUntilDemo(A6)

8

40320

<

--

=DoLoopUntilDemo(A7)

DOLOOPUNTILDEMO IN ACTION

2. A quotation from the Microsoft VBA Help ﬁ le: “Tip: The Do . . . Loop statement pro-

vides a more structured and ﬂ exible way to perform looping.”

910 Chapter 37

Function WhileDemo(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N <= 1 Then

WhileDemo = 1

Else

i = 1

j = 1

While i <= N

j = j * i

i = i + 1

Wend

WhileDemo = j

End If

End Function

1

2

3

4

5

6

7

AB C

N

11

<

--

=WhileDemo(A3)

1.4 1

<

--

=WhileDemo(A4)

22

<

--

=WhileDemo(A5)

75040

<

--

=WhileDemo(A6)

8

40320

<

--

=WhileDemo(A7)

WHILEDEMO IN ACTION

37.5.6 The For Loop

One last (for now) variation on the loopy theme, the For loop, is used

mainly for loops where the number of times the action is repeated is

known in advance. The following functions demonstrate its use and

variations:

911 Types and Loops

The Step part of the statement can be dropped if (as in this case) the

increment is 1. For example,

Function ForDemo1(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N <= 1 Then

ForDemo1 = 1

Else

j = 1

For i = 1 To N Step 1

j = j * i

Next i

ForDemo1 = j

End If

End Function

1

2

3

4

5

6

7

AB C

N

11

<

--

=ForDemo1(A3)

1.4 1

<

--

=ForDemo1(A4)

22

<

--

=ForDemo1(A5)

75040

<

--

=ForDemo1(A6)

8

40320

<

--

=ForDemo1(A7)

FORDEMO1 IN ACTION

For i = 1 To N

j = j * i

Next i

If you want the loop to count down, the Step argument can be nega-

tive, as demonstrated in the next function:

912 Chapter 37

The For loop can be exited early by using the Exit For statement, as

demonstrated in the next function:

Function ForDemo2(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N <= 1 Then

ForDemo2 = 1

Else

j = 1

For i = N To 1 Step -1

j = j * i

Next i

ForDemo2 = j

End If

End Function

1

2

3

4

5

6

7

AB C

N

11

<

--

=ForDemo2(A3)

1.4 1

<

--

=ForDemo2(A4)

22

<

--

=ForDemo2(A5)

75040

<

--

=ForDemo2(A6)

8

40320

<

--

=ForDemo2(A7)

FORDEMO2 IN ACTION

Function ExitForDemo(Parameter1, Parameter2)

Dim i As Integer

Dim Sum As Long

Sum = 0

For i = 1 To Parameter1

Sum = Sum + i

If Sum > Parameter2 Then Exit For

Next i

ExitForDemo = Sum

End Function

913 Types and Loops

37.6 Summary

VBA Macros and functions use variables to store information. Variables

can hold all sorts of information. Declaring and using variables that can

hold only a speciﬁ c type of information (Typed Variables) can make your

program more readable and allow it to run faster and use less computer

memory. Looping structures are a way to make your program perform

a task repeatedly. In this chapter we reviewed the wide variety of looping

structures offered by VBA and the Boolean and comparison operators

used to control the looping and conditional execution structures. You will

ﬁ nd this all very useful in writing homemade functions for your ﬁ nancial

applications.

Exercises

1. Fibonacci numbers introduced in exercise 10 of Chapter 36 are deﬁ ned as follows:

F

FFF

()

() () ()

00

11

2011

3122

423

=

=+=

=+== 3

…

In general, F(n) = F(n − 2) + F(n − 1).

Rewrite the function in exercise 10 of Chapter 36 without using recursion.

1

2

3

4

5

6

ABC D

Parameter1 Parameter2

522

15 <

--

=ExitForDemo(A3,B3)

622

21 <

--

=ExitForDemo(A4,B4)

722

28 <

--

=ExitForDemo(A5,B5)

822

28 <

--

=ExitForDemo(A6,B6)

EXITFORDEMO IN ACTION

914 Chapter 37

1

2

3

4

5

6

7

8

AB C

n Fibonacci

11<

--

=Fibonacci(A3)

21<

--

=Fibonacci(A4)

32<

--

=Fibonacci(A5)

68<

--

=Fibonacci(A6)

12 144 <

--

=Fibonacci(A7)

24 46368 <

--

=Fibonacci(A8)

FIBONACCI IN ACTION

2. Write a version of the function HomeFactorial (see section 36.8) that does not use

recursion. Hint: We can describe the workings of the factorial function by saying,

“To calculate 6! multiply the numbers from 1 to 6.”

1

2

3

4

5

6

7

8

AB C

n HomeFactorial

-1 0 <

--

=HomeFactorial(A3)

00<

--

=HomeFactorial(A4)

11<

--

=HomeFactorial(A5)

22<

--

=HomeFactorial(A6)

36<

--

=HomeFactorial(A7)

8 40320 <

--

=HomeFactorial(A8)

HOMEFACTORIAL IN ACTION

3. Write a function NewPV(CF, r) that calculates the present value of a given cash

ﬂ ow CF at interest rate r for ﬁ ve periods:

NewPV CF r

CF

r

CF

r

CF

r

CF

r

CF

r

(,)

()()()()()

=

+

+11111

12345

1

2

3

4

5

6

AB C D

CF r NewPV

100.0000 10% 379.0787 <

--

=NewPV(A3,B3)

50.0000 10% 189.5393 <

--

=NewPV(A4,B4)

100.0000 1% 485.3431 <

--

=NewPV(A5,B5)

50.0000 1% 242.6716 <

--

=NewPV(A6,B6)

NEWPV IN ACTION

915 Types and Loops

4. Rewrite the function in exercise 3 as BetterNewPV(CF, r, n), so it could deal with

n periods.

1

2

3

4

5

6

ABC D E

CF r n BetterNewPV

100.0000 5% 5 432.9477 <

--

=BetterNewPV(A3,B3,C3)

50.0000 10% 5 189.5393 <

--

=BetterNewPV(A4,B4,C4)

100.0000 1% 10 947.1305 <

--

=BetterNewPV(A5,B5,C5)

50.0000 1% 10 473.5652 <

--

=BetterNewPV(A6,B6,C6)

BETTERNEWPV IN ACTION

5. A bank offers different interest rates on loans. The rate is based on the size of the

periodical repayment (CF) and the following table. Rewrite the function in exercise

4 as BankPV(CF, r, n) so that it reﬂ ects the present value of a loan in the bank.

For Periodical Repayments £

The Interest Rate Is

100.00

r

500.00

r − 0.5%

1,000.00

r − 1.1%

5,000.00

r − 1.7%

1,000,000.00

r − 2.1%

1

2

3

4

5

6

7

8

ABCD E

CF r n BankPV

£100.00 5% 5 £432.95 <

--

=BankPV(A3,B3,C3)

£100.01 5% 5 £439.04 <

--

=BankPV(A4,B4,C4)

£1,000.00 5% 5 £4,464.36 <

--

=BankPV(A5,B5,C5)

£1,000.01 5% 5 £4,540.79 <

--

=BankPV(A6,B6,C6)

£5,000.00 5% 5 £22,703.71 <

--

=BankPV(A7,B7,C7)

£5,000.01 5% 5 £22,964.11 <

--

=BankPV(A8,B8,C8)

BANKPV IN ACTION

916 Chapter 37

6. A bank offers different interest rates on deposit accounts. The rate is based on the

size of the periodical deposit (CF) and the following table. Write a future value

function BankFV(CF, r, n).

For Periodical Deposits The Interest Rate Is

≤100.00 r

≤500.00 r + 0.5%

≤1,000.00 r + 1.1%

≤5,000.00 r + 1.7%

>5,000.00 r + 2.1%

1

2

3

4

5

6

7

8

ABCD E

CF r n BankFV

£100.00 5% 5 £580.19 <

--

=BankFV(A3,B3,C3)

£100.01 5% 5 £588.86 <

--

=BankFV(A4,B4,C4)

£1,000.00 5% 5 £5,992.91 <

--

=BankFV(A5,B5,C5)

£1,000.01 5% 5 £6,099.47 <

--

=BankFV(A6,B6,C6)

£5,000.00 5% 5 £30,497.07 <

--

=BankFV(A7,B7,C7)

£5,000.01 5% 5 £30,856.78 <

--

=BankFV(A8,B8,C8)

BANKFV IN ACTION

7. Another bank offers a 1 percent increase in interest rate to savings accounts with

a balance of more than £10,000.00. Write a future value function Bank1FV(CF, r, n)

that reﬂ ects this policy.

1

2

3

4

5

ABCD E

CF r n BankFV

£100.00 5% 5 £580.19 <

--

=Bank1FV(A3,B3,C3)

£1,000.00 5% 5 £5,801.91 <

--

=Bank1FV(A4,B4,C4)

£5,000.00 5% 5 £29,691.39 <

--

=Bank1FV(A5,B5,C5)

BANK1FV IN ACTION

8. The bank in exercise 7 changed its bonus policy and now offers the interest rate

increase based on the following table. Rewrite Bank1FV(CF, r, n) to reﬂ ect this

change.

917 Types and Loops

Balance Interest Rate

≤1,000.00 r + 0.2%

≤5,000.00 r + 0.5%

≤10,000.00 r + 1.0%

>10,000.00 r + 1.3%

1

2

3

4

5

6

ABCD E

CF r n BankFV

£1,000.00 5% 5 £5,884.33 <

--

=Bank2FV(A3,B3,C3)

£1,000.01 5% 5 £5,884.39 <

--

=Bank2FV(A4,B4,C4)

£5,000.00 5% 5 £30,033.93 <

--

=Bank2FV(A5,B5,C5)

£5,000.01 5% 5 £30,033.99 <

--

=Bank2FV(A6,B6,C6)

BANK2FV IN ACTION

Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.