1. 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
|
r
|
|
<=500
|
r+0.5%
|
|
<=1000
|
r+1.1%
|
|
<=5000
|
r+1.7%
|
|
>5000
|
r+2.1%
|
2. Write a payment schedule calculator macro. The macro is to ask the user for the sum of the loan, the number of payments, and the interest rate. Assume payment at the end of the period.
Hints:
You may want to use the worksheet function PMT
The following macro and its output might be interest
Sub StringConcat()
Dim S As String
S = "Col1" & Chr(9) & "Col2" ' Chr (9) is the Tab
S = S & Chr(13) & "aaa" & Chr(9) & "bbb"
MsgBox S
End Sub
3. Develop code for a function that behaves in the same manner as Excel's SUM function. The function should sum all numerical values in an array.
4. Develop code for a function that behaves in the same manner as Excel's NPV function that requires two inputs, the first an array of cash flows and the second the discount rate.
E.g Function NetPV( cvec,rate)
5. The spreadsheet shows cash flow over five years for an investment proposal. After an outflow at the start, the cumulated cash flows turn positive during the third year , the time at which this happens being known as the project's payback period (here 2.4 years).
The intermediate calculations determining the payback period are set out in the worksheet. They centre on cumulating cash flows at yearly intervals and testing to determine the time at which the cumulative sum first turns positive. Develop a Payback Function, say PayBack (cvec) that returns the payback period for any array of cash flows.