xlf EandA series
Exercises - functions (session 8)
0. Preliminary
The EXERCISE - complete the following tasks
Task 1: formula to function
Assume that all functions are listed in the Insert Function, User Defined (UDF) category, and are called from the Excel WS.
Required
Number set 1.1 to 1.2
1.1 Area Write a function to calculate the area of a rectangle given the length and width as input arguments. The formula is $$\begin{equation} A = L * W \end{equation}$$ where \(A\) is the area, \(L\) is the width, and \(W\) is the width.
1.2 BSPut Write a function to price a put option. The formula for the price of the put option (Black-Scholes) is $$\begin{equation} P=Xe^{-rT}N(-d_2) - S_0 N(-d_1) \end{equation}$$ where$$d_1=\frac {log \left( \frac{S_0}{X} \right) + \left( r+ \frac {\sigma^2} {2} \right )T}{\sigma \sqrt{T}} $$ $$d_2=\frac {log \left( \frac{S_0}{X} \right) + \left( r - \frac {\sigma^2} {2} \right )T}{\sigma \sqrt{T}} = d_1 - \sigma \sqrt{T}$$
In equation 2, \(S_0\) is the stock price at time 0, \(X\) is the exercise price of the option, \(r\) is the risk free interest rate, \(\sigma\) represents the annual volatility (standard deviation) of the return series for the underlying asset, and \(T\) is the time in years, to expiration of the option. Argument names S
, X
, r
, v
, and t
respectively. The probabilities for \(N(\cdot)\) are estimated with the WS NORM.S.DIST
function.
To test the function use the values: S = 42, X = 40, r = 0.1, v = 0.2 and t = 0.5. The put price should be 81 cents.
Task 2: Controlling code execution with loops
Number set 2.3 to 2.5. Only 2.5 is included in this version
2.5 xlfNper2 Write a function (named xlfNper2) to calculate the number of repayments (Nper) required to pay off a LOAN of principal (Pv), with interest rate (Rate), and instalment amount (Pmt). Your code must be based on a For...Next loop.
To test the function use the seed values: Pv:= $400,000, Rate:= 5% p.a., Pmt:= $2,639.82 per month (over 20 years). The return values can be derived from this data, ie. ≅240
1. VBA functions
Solution - task 1: Area
Code 1: Function Area
' Write a function to calculate the area of a rectangle given the length and width as input arguments ' Excel WS functions include a function named AREAS (Lookup and Reference category), ' so the name Area is used in this example '' ============================================== Function Area(Length As Double, Width As Double) As Double Area = Length * Width End Function '' ============================================== Sub TestArea() Dim Ans As Double Ans = Area(2.4, 12) ' Returns 28.8 Stop End Sub
Solution - task 1: BSPut
Code 2: Function BSPut
' Write a function to price a put option '' ============================================== Function BSPut(S As Double, X As Double, r As Double, v As Double, t As Double) As Double ' SYNTAX: BsPut(S,X,r,v,t) ' Arguments: ' S: Compulsory - Stock price ' X: Compulsory - eXercise price of the option ' r: Compulsory - annual rate of interest (risk free) ' v: Compulsory - annual volatility (standard deviation) of the return series for the underlying asset ' t: Compulsory - time in years, to expiration of the option Dim d1 As Double, d2 As Double With Application.WorksheetFunction d1 = (Log(S / X) + (r + v ^ 2 / 2) * t) / (v * t ^ 0.5) d2 = (Log(S / X) + (r - v ^ 2 / 2) * t) / (v * t ^ 0.5) BSPut = X * Exp(-1 * r * t) * .Norm_S_Dist(-1 * d2, True) _ - S * .Norm_S_Dist(-1 * d1, True) End With End Function '' ============================================== Sub TestBSPut() Dim Ans As Double Ans = BSPut(42, 40, 0.1, 0.2, 0.5) ' Returns 0.808599372900092, rounds up to 0.81 Stop End Sub
Solution - task 2: xlfNper2
The seed values: Rate:= 5% p.a., Pmt:= $2,639.82 per month (over 20 years), and Pv:= $400,000, are included in the 1. Loan repayment table of the Repayments worksheet in figure 2. Nper is set to 20 years, and the instalment amount is derived from this data using the WS PMT function (column P and figure 1). PMT(Rate / 12, Nper * 12, PV) returns -$2,639.822957 ≅ -$2,639.82. The loan is repaid using the rounded amount, thus accumulating as a rounding error in the final instalment.

About the question - the question requires use of a For...Next loop
How to do this can be best illustrated by setting up loan repayment schedule in table form, as displayed by the 2. Loan schedule including running balance in figure 2. The For...Next loop reads each record (in computer memory), keeping count of the number of instalments, until the balance (Pv) is zero.
The suggested solution VBA code is in code 3. See comment line for explanations.
Code 3a: Function xlfNper2
' Write a function (named xlfNper2) to calculate the number of repayments (Nper) required to ' pay off a LOAN of principal (Pv), with interest rate (Rate), and instalment amount (Pmt). ' Include a For...Next loop in your code. '' ============================================== Public Function xlfNper2(Rate As Double, _ Pmt As Double, _ Pv As Double) As Long Const MAXLOOPS As Long = 500 ' max number of instalments Const ERROR As Double = 2# Dim AbsPmt As Double Dim interest As Double Dim NetReduction As Double ' reduction in balance Dim i As Long Dim n As Long AbsPmt = Abs(Pmt) If AbsPmt <= Pv * Rate Then ' If instalment amount (Pmt) is not sufficient to cover xlfNper2 = -1 ' current interest then return an error (-1 periods) Else n = 0 ' Version 3: For...Next loop For i = 1 To MAXLOOPS If Pv < 0 Then Exit For Else n = n + 1 interest = Pv * Rate NetReduction = AbsPmt - interest Pv = Pv - NetReduction End If Next i 'Debug.Print "Print time: " & Time & _ ' ", Closing balance: " & _ ' Format(Pv + AbsPmt, "Currency") & vbNewLine If Pv + AbsPmt < ERROR Then xlfNper2 = n - 1 Else xlfNper2 = n End If End If End Function '' ============================================== Sub TestxlfPmt3() Dim Ans As Double Ans = xlfNper2(0.05 / 12, -2639.82, 400000) Stop End Sub
- This example/solution was developed in Excel 2016 ProPlus 64 bit.