xlf Custom functions

Function arguments with percentage and decimal format

Excel formulas accept percentage values in both decimal form and the percent operator. For example, 0.055, and 5.5% both return 0.055 when entered in a cell in Excel.

Consider the following formula for calculating the present value $PV$ from the future value $FV$ on an amount, at a discount rate $r$ over $n$ periods.

$$PV=FV(1+r)^{-n}$$

In code 1. the custom function for this formaula is named PValue.

The syntax is: PValue(DiscRate, FValue, NoPeriods), where DiscRate is the interest rate per period, FValue is the future value, and NoPeriods is the number of periods.

Code 1: A UDF that returns Present Value. DiscRate is in decimal form

Function PValue(DiscRate As Double, FValue As Double, NoPeriods As Double) As Double
	PValue = FValue * (1 + DiscRate) ^ -NoPeriods
End Function

Calling the PValue custom function from Excel

Using PValue in the Excel formula bar is straight forward, because Excel automatically accepts percentage values in decimal form and with with the percent operator.

The same applies to a custom function entered via the Function Wizard on the Formulas tab. In this example, the discount rate DiscRate is entered as 7.5%, and is stored in computer memory as 0.075 as shown in figure 1. DiscRate 7.5% = 0.075.

FunDiag — **Fig 1: Function argument** 7.5% is stored as 0.075

Calling the PValue custom function from VBA

VBA does not automatically convert percentage values such as 7.5% to decimal format. In VBA the % character is a type declaration character for an integer.

The Sub procedure PValue_test in code 2 is used to illustrate the differences under three different scenarios.

Scenario 1, the rate is in decimal format 0.075
Scenario 2, the rate is in percentage format 7.5%, and
Scenario 3, the rate of 7.5% is passed with a variable of the type String.

In each case, the output is sent to the Immediate Window with the Debug.Print statement.

Details of VBA functions:

Replace: Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Val: Returns the numbers contained in a string as a numeric value of appropriate type.

Code 2: A procedure 1. decimal 0.075 - line 5, 2. 7.5% as String argument - line 8, and 3. 7.5% as String variable - line 13

Sub PValue_test()
Dim ans As Double
Dim dRate As String
	'' Scenario 1 
		ans = PValue(0.075, 115.5625, 2)
			Debug.Print "1. PValue(0.075, 115.5625, 2) returns: " & ans & vbNewLine
	'' Scenario 2 
		ans = PValue(Replace("7.5%", "%", "") / 100, 115.5625, 2)
			Debug.Print "2. PValue(Replace(""7.5%"", ""%"", """") / 100, 115.5625, 2) " & _
				"returns: " & ans & vbNewLine
	'' Scenario 3 
		dRate = "7.5%"
		ans = PValue(Replace(dRate, "%", "") / 100, 115.5625, 2)
			Debug.Print "3. PValue(Replace(dRate, ""%"", """") / 100, 115.5625, 2) " & _
			"returns: " & ans & vbNewLine
End Sub

In scenario 1, line 5, the 0.075 is simply passed as a double to PValue and the return value is received by the calling procedure. In Scenario 2, line 8, and scenario 3, line 13, however, the 7.5% must be entered as a String and is enclosed with the "" expression.

Normally, the Val function would be used to extract the leading number sequence from the string, however, it does not work in this situation. The problem is that the % character is treated as the VBA Type Declaration Character for an Integer. This means that if "7.5%" is passed to the Val function, it only returns the integer component 7. It also returns a Type mismatch error. To prevent this, the Replace function is used to strip out (replace) the % character with nothing "". So Replace("7.5%") / 100 returns 0.075.

Another feature of code 2 is the inclusion of double quotes in a text string. In code 2, line 13, the "%" character is entered with double double quotes ""%"".

The Immediate Window with details for each scenario is shown in figure 2.

This example was developed in Excel 2010 32 bit